I ship a lot of items to a lot of stores and I have two Excel sheets. I have one sheet showing how many items I have shipped to each store and another sheet showing how many I have sold in each store. The problem is I need to link them together (should have been done in the beginning but oh well...). I have about 547 stores I have shipped to and and about 450 stores I have sold items in. So not every store shipped to has items sold to but the store ID is unique and doesnt duplicate itself, except in each sheet.

So I have

Sheet 1: Lists of stores with quantity of items shipped

StoreID | qty-item1-shipped | qty-item2-shipped | qty-item3-shipped
   4564 |                45 |                56 |                34

Sheet 2: Lists of stores with quantity of items sold

StoreID | qty-item1-sold | qty-item2-sold | qty-item3-sold
   4564 |             35 |             46 |             24

I really should know this it's quite basic but its driving me nuts... the end results should be

       |       | Qty Shipped | Qty Sold
Store1 | item1 |          45 |       35
Store1 | item2 |          46 |       56
Store2 | item1 |          65 |        4
Store2 | item2 |          25 |       14

Now I should be using an access database for this kind of thing but conceptually I'm missing a rather large basic piece can you help an idiot find his way?

  • You did not mention how you want to merge the tables (using some programming language?). Is it a one time job or will you have to do this regularly? – Linus Caldwell May 11 '13 at 6:18
  • Can you group the "duplicates" on one same sheet together ? Because then it's a simple matter of using SUMIF. If not, that's another story because there is no identifier to separate two entries for the same store on one sheet. – ApplePie May 11 '13 at 12:03
  • hey, thanks for that looks a lot better. I was thinking of using access, but either that or excel. I dont think I can group the duplicates though I'm not sure as "store ID 23" might not be in the same cell.. unless you can do it without them needing to be in matching cell numbers... – Adam Sinnott May 11 '13 at 12:24

like Alexandre said you should use sumif

col A: store
col B: items (of store X)
col C: sum of item Y shipped (Y an item from store x )
col D: sum of item Y sold

before you can do that you should use pivot table to rearrange your tables
store1 item1...value
store1 item2...value
store2 item1
store2 item3

Its not hard to do it with excel but with so many stores and a data so valuable I think its time to think of real database... find a smart kid to build an sqlserver express database(free)/ open source DB for you .... even if it will cost a bit... you will sleep better at night. Good luck

| improve this answer | |

On copies (just in case!) strip out qty-, -shipped and -sold. Create PivotTables for each sheet using multiple consolidation ranges. Drill down to their data by double clicking the intersection of the Totals. Shift the right hand column of one table to the right. Copy Paste Special Values each table into a single new sheet, one above the other. Move up the label for the right hand column of the lower labels to the other row with labels and delete the lower labels. You should end up with a 'flat file' suitable for pivotting again to show your desired output, or for entry into a database if the latter is really what you want.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy