create new column based on two cells from different with same value

[Anonymous]

Hey there, 

I have following situation which I couldn't resolve yet:

I want to compare each row with the previous row.

If Colour and Previous Colour are both the same as in the row before I want to skip to the next row automatically and try it again.

If they are not the same, in an additional column it should add the value "1"

ID	Colour	Previous Colour	Index
126443	Red	Green	1
123456	Blue	Red	2
175332	Blue	Red	3
765432	Yellow	Blue	4
733511	Green	Yellow	5
531534	Blue	Green	6

Something like: 

if ((PrevColour(index-1) = PrevColour(index) && Colour(index-1) = Colour(index))
then jump to index+1
else type "1" in new column

I'd appreciate any help. Kind regards 🙂

Jama1234

[artemus · Accepted solution]

1. Add a new column: Index2 as an index column starting at 2 (one more than column Index)

2. Do a self join (Merge Tables) with Index and Index2 as the key joining columns. Kind of join is left outer

3. Expand the newly generated column to new rows.

4. Now you have duplicate columns in your table with values equal to the previous row.

5. Add a column that compares the current values with the previous values that are stored in the same row.

6. Remove unneeded columns

[Anonymous]

It worked really well.

Thank you!!