I have two spreadsheets, one with data listed against a product identified by the style (sku), the second spreadsheet has the different product colour and size options for each sku. I want to merge the two spreadsheets together so that I have all of the colour and size variations for each sku as well as all of the details that are matched to the sku i.e. long description, product name, catalogue etc... I am trying to do this in excel

  • Are you trying to do this for a report, or create a brand new 'standalone' workbook with both tables merged? – Dave Feb 12 '14 at 8:37
  • Please edit your question to include relevant details on your existing setup as well as what methods you've already tried to achieve your desired results – Alex M Mar 16 at 22:05

You need to Consolidate Data. Excel can merge data from different spreadsheets (worksheets) or different files. Another thing you can do is use a Pivot Table.

For Consolidation, read this support article from Microsoft.

For Pivot Tables, you can read this question in Stack Overflow which has a good example and also has another alternative (by using SQL) to accomplish what you need.

| improve this answer | |
  • 2
    Please don't answer with "go read this link". If that link ever changes or stop working your answer will become useless, especially for links beyond control of StackExchange network. Please post relevant portions from that link directly in your answer. – Mxx Feb 12 '14 at 7:42

You can use the vlookup() function in your second worksheet to merge its data with the data from your first sheet. You can google for more information about the syntax of the vlookup function or find more details here: http://www.ablebits.com/office-addins-blog/2011/12/14/lookup-excel-functions-vlookup/

If it is a one-time task and you don't have time to learn vlookup(), install the Merge Tables Wizard add-in (it is commercial, but a fully-functional 15-day trial version is available), you can download it here: http://www.ablebits.com/excel-lookup-tables/index.php

If you opt for the Merge Tables wizard, be sure to choose your SECOND worksheet with colour and size variations as the Main table. Merging 2 tables will take you about 5 min with the add-in installation.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.