Comparing and populating data between 2 sheets

[Kurt Herman-Roloff]

Hi all -

I am having problems automating the following. I have a Excel
document with 2 sheets

Sheet A (1,000 records)
Col1,Col2,Col3,Col4,Col5,Col6,Col7
text,text,text,text,text,text,<blank>

Sheet B (600 records)
Col1,Col2,Col3,Col4,Col5,Col6,Col7
text,text,text,text,text,text,integer

I want to compare Col1-6 in Sheet B to Sheet A and if a row (Col1-
Col6) in Sheet B matches a row (Col1-Col6) in Sheet A populate the
value of Col7 in Sheet B in Col7 in Sheet A

Thanks for your help,
Kurt

 

[Pete_UK]

I would suggest that you concatenate those text values together in a
helper column, so in H1 of both sheets you can use this formula:

=A1 & B1 & C1 & D1 & E1 & F1

and copy it down as far as required in both sheets.

Then in G1 of Sheet A you can put this formula:

=IF(ISNA(MATCH(H1,'Sheet B'!H:H,0)),"",INDEX('Sheet B'!
G:G,MATCH(H1,'Sheet B'!H:H,0)))

and again copy it down as required.

Hope this helps.

Pete