Help comparing data in 2 Excel worksheets please

  Newuser3443 18:41 10 Oct 05
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 05

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 05

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 05

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 05

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 05

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

  Newuser3443 20:43 10 Oct 05

Do you the function to return the current ROW please?

  VoG II 21:10 10 Oct 05

=ROW()

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

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…