Adding rows due to comparing

[English_Bloke82]

Hi there,

First post, and I am afraid it is pretty complicated. We have two work sheets (lets call them Work1 and Work2), in column A in both is a time, in B is another time and in C a unit value.

Work2 will have the same rows as Work1, but Work1 will have additional columns. to make it a bit easier
Work1 Work2
9:00 10:00 1 9:00 10:00 1
10:00 11:00 1 10:00 11:00 1
11:00 12:00 2 12:00 15:00 3
12:00 15:00 3 9:00 10:00 4
9:00 10:00 4

I want to compare the three cells in both worksheets and in Work2 add an extra row where they do not match to get them in line: e.g.

Work1 Work2
9:00 10:00 1 9:00 10:00 1
10:00 11:00 1 10:00 11:00 1
11:00 12:00 2
12:00 15:00 3 12:00 15:00 3
9:00 10:00 4 9:00 10:00 4

Sometimes there will be multiple spaces that will be needed, Sorry this is a hard one!

Thank you!

[darkyam]

Will it work both ways? In other words, might it be that Work2 has a time that Work1 doesn't, so that the spaces are in the first three columns? Also, will the second times and the third numbers on both sheets match if the first times do? If not, how do you want the sheet to handle them?

[English_Bloke82]

Hi,

It will only work the one way, and if the first matches then there is a 99.9% chance the rest will match too.

Basically they are the timings of a visit so the rows will be identical in the two sheets, and will be in the same order, but with additional visits in the Work1 sheet.

Hope this helps

:o)

[darkyam]

OK, then it should be easy.
In Work1, you could have =IF(ISERROR(LOOKUP($A1,Work2!$A$1:$A$1000),"",A1) in D1. You can drag that across and down as needed.