Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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? –  agentmike 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 –  deathApril Oct 21 '12 at 15:44
add comment

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.