Comparing 2 blocks of text and data

J

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?
 
J

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
 
J

John Fesen

THANK YOU very much - it works!
John


Joel said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top