# Help comparing data in 2 Excel worksheets please

Newuser3443 18:41 10 Oct 2005
Locked

I have 2 worksheets in the same Excel workbook.

Sheet1 contains ITEM CODE and DESCRIPTION.

Sheet2 contains SUPPLIER NAME, ITEM CODE, ORDER VALUE

I want to be able (via a macro) to add a third column to Sheet1 which contains the result of looking for all matching item codes in Sheet2 to compute a TOTAL VALUE OF ORDERS for each ITEM CODE.

This is beyond my know how, so any assistance would be greatfully appreciated (I have spent most of the afternoon scouring the internet).

Many thanks

Rob

VoG II 18:56 10 Oct 2005

Start off by creating a table containing the sums for each Item Code using the SUMIF function, e.g.

=SUMIF(A1:A7,"a",B1:B7)

(please note the ranges and value that I've used probablyy bear no resemblence to yours).

This lookup table can be on the same or a different sheet.

Then on Sheet1, in Column C enter a VLOOKUP formula:

=VLOOKUP(A9,A9:B13,2,FALSE)

(again no relation to your worksheet).

=VLOOKUP(ITEM CODE, range of lookup table created earlier, column number of lookup table containing the sums, FALSE to return an exact match or an error if the ITEM CODE is not found in the lookup table).

Newuser3443 19:19 10 Oct 2005

Thanks VoG.

If I follow the example correctly, the "a" in the SUMIF function is the ITEM CODE?

In my macro I need to create a new worksheet (easy enough) and COPY the worksheet containing the item codes in to it.

Without a long explanation, the worksheet containing the item codes needs to be a seperate speadsheet, so how to I open it (if it needs to be opened?), copy it, paste it into my currently active worksheet and then close it?)

Sorry for more questions..........

Rob :0(

VoG II 19:26 10 Oct 2005

Sorry, yes the "a" is an ITEM CODE.

I don't think that you need a macro to do this - just worksheet functions as outlined above.

To create a copy, open the worksheet containing the ITEM CODEs and Edit|Move or Copy Sheet, select the To book: to copy to, and be sure to tick Create a copy.

Newuser3443 19:42 10 Oct 2005

Cheers mate, will give it a go. Hope you don't mind me asking any more questions if I get stuck.

:0)

VoG II 20:08 10 Oct 2005

Ask away! My answers above were not terribly explicit but hopefully will get you started.

Newuser3443 20:43 10 Oct 2005

Do you the function to return the current ROW please?

VoG II 21:10 10 Oct 2005

=ROW()

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?