Comparing 2 blocks of text and data

[John Fesen]

I have 2 blocks of text and data.

Block 1 stretches from A7 to AF 300 with text and data in all columns and
rows.
In column AA there are UPC numbers.

Block 2 stretches from AH7 to AW 200 with text and data in all columns and
rows.
In column AW there are UPC numbers.

What I want to do is to match Block 2 with Block 1 via the UPC numbers.
When done, the text and data in the two blocks of data are now in the same
row – all based upon the UPC numbers with a total of 200 rows. The extra 100
rows in Block 1 are either deleted or appear at the bottom of Block 1 because
of no matches in Block 2.

Can this be done in Excel?
How?

 

[Joel]

The easy wasy without a macro is to copy block 2 to a new sheet keeping the
same columns. Then put formulas on the new sheet in columns A7 to Af300 to
get the number from the old sheet.

If the Old sheet is Sheet1 and the new Sheet is Sheet2

Then in Sheet 2 cell A& put the following

=Index(Sheet1!A7:A300,Match($AW7,Sheet1!$AA7:$AA300,0),1)

then copy formula to A7:Af300

 

[John Fesen]

THANK YOU very much - it works!
John

The easy wasy without a macro is to copy block 2 to a new sheet keeping the
same columns. Then put formulas on the new sheet in columns A7 to Af300 to
get the number from the old sheet.

If the Old sheet is Sheet1 and the new Sheet is Sheet2

Then in Sheet 2 cell A& put the following

=Index(Sheet1!A7:A300,Match($AW7,Sheet1!$AA7:$AA300,0),1)

then copy formula to A7:Af300