Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This is essentially Excel, merge two sheets with one column as key but instead of one key column I have 3 so I cannot simply use VLOOKUP.

What I want is very simple: dynamically generate a master table with all the data from a set of tables combined. It should be very easy to include new rows that are added to the source tables. Eventually I will do a pivot table on the master table to get the final form as shown.

Source table structure:
Date | Product | Variant | Price

Desired master structure:
Source table name* | Date | Product | Variant | Price

Final form
Date | Product | Variant | Source1 | Source2 | SourceN

*The source table name can be added to the source table.

share|improve this question
    
maybe this superuser.com/questions/487201/… will give you a lead/idea? – Michael Oct 20 '12 at 21:23
1  
@agentmike Unfortunately I cannot use VLOOKUP because it only supports one key column and I have three. However, the INDIRECT function from that question seems to be quite useful. I guess I may have to use SQL instead of EXCEL in the end... – billc.cn Oct 20 '12 at 21:30
    
can you create a new column with the whole key concatenated from the 3 columns in each sheet? if yes, use INDEX+MATCH on this column – Aprillion Oct 21 '12 at 15:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.