Predictive Cell Filling Workflow

[Samowens30]

Hi There,

Thanks in advance for taking the time to read this / help me solve the problem.

I have 8 columns, with around 100,000 rows of different combinations. Each row will have some empty cells in certain columns. I want to design a workflow that looks at the possible combinations and autofills the empty cells when it is certain there is no other combination.

Example: Let's say the filled out data has 4 columns like this:

Phone - Apple - iPhone8 - 128gb

Phone - Apple - iPhone8 - 256gb

But the data I am working with has this:

(Blank) - (Blank) - iPhone8 - 128gb

Phone - Apple - iPhone 8 - (Blank)

(Blank) - Apple - (Blank) - (Blank)

For the first line, we can see if it an iPhone 8 128gb, so we know for a fact that is an Apple product and is a phone, so it should autofill in Phone & Apple into the first 2 columns

For the second line we can see that we only have the memory size missing, however there are two possible combinations for this so it isn't certain, so the workflow should leave this blank

For the 3rd one, we only have "apple", we don't know if this is a tablet, or the type of apple product, or the size, so it leaves it all blank,

Hope this makes sense, please let me know what works best. I'll check back tonight and of course mark solution / like responses.

[morr-co · Accepted solution]

Hello @Samowens30 - attached is one approach to this. The logic could be modified but from your examples, it appears to be mostly driven by Model field. Let me know if you have questions.

[Samowens30]

You are a genius!!! Thank you!!! 🙂

[Samowens30]

Hi There,

I've been playing with this for an hour or so and it works quite well, but has two slight issues i'm struggling to address.

1) It consolidates multiple identical lines into one line, which makes it really hard to link back to the source data for analysis. I think a solution is to somehow keep the original description so that I can vlookup the source cell to get the extra columns info. Do you know how best I can ensure the source cell is carried through the process? Is it possible?

2) It only matches perfect records, is there a way I can configure it to match columns which are empty so that it still autofills? For example, If I have iPhone 8, I know it's Apple, but it won't autofill unless each column is full, so if the memory size is empty it doesn't fill Apple in.

If able to solve these 2 problems then I will have a perfect workflow for this task

Thanks in advance if you are able to help with this, you are a lifesaver!!

[morr-co]

@Samowens30 a revised workflow is attached. Below is a summary of the changes based on your questions.

1. There are few changes to make this clearer. The final tool, the purple Union tool, has the records side by side.
   1. The Type, Brand, Model, and Size fields are the original values from the record.
   2. The fields prefixed match_ are the values from the record it was matched to. 
   3. The fields prefixed new_ are the new values determined in Formula tool (i.e. what the value should be). You could update the logic to adjust this.
   4. I added a RecordId field to make this clearer. For example, RecordId 3 matched to RecordId 2 (match_RecordId).
      
      
2. All of the records get compared now, even if they are incomplete. However, a record will not be compared to itself. If there is no match, the match_ and new_ fields will be empty.

Hope this helps!