Compare a range for a match and then put it in a cell

**Zoomer36** · 07-23-2012, 11:42 AM

Hello,

I need some help with this problem. I have a spreadsheet with 10K to 15K invoices. For each invoice, I have the number of cartons and packs sold. I also have all the invoices and the stamps sold. I pull this info with a SQL script from out tables. If I was better at SQL, maybe I could get it all together but that isn't working either.

I need to compare the 2 ranges and if there are stamps on a matching invoice, I need to put that number in the stamps column on the first sheet.

Doc Type Doc NumberDate Cartons Packs Stamps Customer # State Doc Type Doc Number Date Stamps
Invoice 1001 1/31/2008 30 300 13952 MS Invoice 1001 1/31/2008 30
Invoice 1002 1/31/2008 164 1640 13471 MS Invoice 1002 1/31/2008 164
Invoice 1003 1/31/2008 44 440 15493 KY Invoice 1003 1/31/2008 44
Invoice 1004 1/31/2008 80 800 15493 KY Invoice 1004 1/31/2008 80
Invoice 1005 1/31/2008 176 1760 10147 MS Invoice 1005 1/31/2008 176
Invoice 1006 1/31/2008 40 400 10147 MS Invoice 1006 1/31/2008 40
Invoice 1007 1/31/2008 31 310 10321 MS Invoice 1007 1/31/2008 31
Invoice 1008 1/31/2008 30 300 13952 MS Invoice 1008 1/31/2008 30
Invoice 1009 1/31/2008 180 1800 10408 MS Invoice 1009 1/31/2008 180
Invoice 1010 1/31/2008 71 710 15048 MS Invoice 1010 1/31/2008 71
Invoice 1011 1/31/2008 120 1200 13464 MS Invoice 1011 1/31/2008 120
Invoice 1012 1/31/2008 21 170 14557 MS Invoice 1012 1/31/2008 21
Invoice 1013 1/31/2008 30 300 10926 MS Invoice 1013 1/31/2008 30
Invoice 1014 1/31/2008 29 290 13117 KY Invoice 1014 1/31/2008 29
Invoice 1015 1/31/2008 8 80 12904 NE Invoice 1015 1/31/2008 8
Invoice 1016 1/31/2008 17 170 11068 NE Invoice 1016 1/31/2008 17
Invoice 1017 1/31/2008 8 80 12065 NE Invoice 1017 1/31/2008 8
Invoice 1018 1/31/2008 29 290 14591 NE Invoice 1018 1/31/2008 29
Invoice 1019 1/31/2008 35 350 10371 KY Invoice 1019 1/31/2008 35
Invoice 1020 1/31/2008 22 130 14864 NE Invoice 1020 1/31/2008 22

I know it looks like it should be a simple =M2 since the first 20 entries have both cartons and stamps but there are <7000 rows in the stamps and over 12000 rows in the cartons and pack. I have attached the spreadsheet here for you to see.

Thanks for any and all help!
2008.xlsx

**NBVC** · 07-23-2012, 11:59 AM

Try perhaps:

=SUMIF(K:K,B2,M:M)

copied down.

This sums the Stamps for matching invoices... so if multiples of an invoice number exist in the 2nd table, the Stamps column will be summed up...

Does that work for you?

**Zoomer36** · 07-23-2012, 12:24 PM

THANKS!!!!! Works like a champ. The data looked a little odd as that are actually some invoices that have stamps but don't have cartons. Not sure why but the numbers fill in very nicely.

**Cutter** · 07-23-2012, 02:17 PM

@ Zoomer36

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

**Zoomer36** · 07-23-2012, 04:24 PM

Hello and thank you for marking it solved. I was actually working with the data to make sure it all worked correctly. I found that when I had a return and an invoice with the same number, it gathered both stamps numbers in both of them. That wasn't correct. I used NBVC's recommendation and expanded it to SUMIFS since I am using Excel 2007. This is the final formula that I used and copied down. =(SUMIFS(M:M,K:K,B2,J:J,A2)) That summed the M column after comparing all values in the K column to cell B2 and all the values in the J column to cell A2. Great piece of advice and this really helped me. I am working with 4 years of data and it is wearing me out!