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 vlookup
s 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.