Reclassification based on previous cells

[JoaoFernandes]

Hello again, another noob question :)

I have a table of only two columns that looks like this:

Item	Classification
a	1
a,b	2
a,b	4
a,b	4
a,b	4
a,b	3
b	1

I would like to update it to something like this, with no commas:

a	1
a	2
b	4
b	4
b	4
b	3
b	1

So far I'm using a Multi Row Formula with the following arguments:

• If Classification = 2 then [Item] is always equal to the previous one
• If Classification = 3 then [Item] is always equal to the next one

The problem is with classification 4. It should be that a classification of 4 is always equal to the item not included in the classification of 1. So, if Classification 1 is an A item (like in the example), all the subsquent 4s should be B. Likewise, if Classification 1 is a B item, all the subsquent 4s should be A.

Does anyone have a clue on how to create such a formula? Thank you very much!

[MarqueeCrew · Accepted solution]

@JoaoFernandes

Use a Text to Columns and after you split the first column with a comma, you can "Split to Rows"

You'll get the results that you are looking for.

Cheers,

Mark