1

I've looked at the question How do I merge two tables in Excel that have identical columns? (and the unanswered followup Merging tables in Excel).

I have a case that is somewhat similar, but with enough differences that I can't see how to apply that answer to my case. I have multiple one-column tables, and I want each table to be a column in the new table, merging or creating new row headers depending on whether there is a match.

For example turning something like this:

Week 1          Week 2          Week 3
-----------     -----------     -----------   
Apples   10     Apricots 11     Bananas  12
Pears     8     Apples    5     Pears     7
Bananas   5     Bananas   4     Mangos    4

...into something like this:

        Week 1  Week 2  Week 3
--------------------------------   
Apples      10      5     
Pears        8              7
Bananas      5      4      12
Apricots           11
Mangos                      4

Right now, I'm doing these manually, and it's time consuming and has lots of potential for human error. Is there any way to automate this? I've tried vlookups but couldn't find anything that worked.

I've got a feeling it might be possible using pivot tables, but I can't figure it out.

Community
  • 1
user56reinstatemonica8
  • 5,494

2 Answers2

1

Assuming your data as shown is in A1:F5

  • Click in G10 (or wherevah)
  • Use Ribbon>Data>Data Tools>Consolidate
  • Function>Sum
  • Reference: Select A1:B5
  • Use labels in: Check Top row and Left column.
  • Click Add
  • Select C1:D5
  • Click Add
  • Select E1:F5
  • Click Add
  • Click OK

You will get

    1   2   3
-----------         
Apricots        11  
Apples  10  5   
Pears   8       7
Bananas 5   4   12
Mangos          4

Regards Brian

Jakuje
  • 10,827
Brian Canes
  • 11
0

This is the best I've found so far - copy and paste row headers, remove duplicates, then put in columns of vlookups. It seems to work but there might be a more efficient way.

  • Copy and paste all your row headers where you want your new table to start

For example:

Apples
Pears
Bananas
Apricots
Apples
Bananas
Bananas
Pears
Mangos
  • Select them all, then under Data hit the Remove duplicates button

It'll now remove duplicates in the order they appear:

Apples
Pears
Bananas
Apricots
Mangos
  • Put down a column with a formula like this, where the first cell reference ("A15") points to the adjacent row header, and the second range ("A5:B11") covers the first table to merge: =IFERROR(VLOOKUP($A15,A$5:B$11,2,FALSE),"")

It should start getting the data:

Apples      10 
Pears        8 
Bananas      5 
Apricots       
Mangos
  • Copy and paste new columns of this formula, with the range adapted to point to the next table to merge. Repeat as necessary.

This should give you all the data. In theory, the only room for human error should be if you entered the formula or cell ranges incorrectly (check and double-check anyway!):

Apples      10      5     
Pears        8              7
Bananas      5      4      12
Apricots           11
Mangos                      4
user56reinstatemonica8
  • 5,494