Forum Discussion
Solved
Help with Excel
Hi, I have a problem which hopefully you can help with, I have a column with customer names and their sales for 2016 and a column with customer names and their sales for 2017. Some names appear in one column and not the other (like below)
| Customer | 2016 | Customer | 2017 | |
| a | 700 | b | 900 | |
| b | 650 | h | 400 | |
| c | 625 | e | 375 | |
| d | 500 | a | 100 | |
| e | 400 | i | 50 | |
| f | 375 | j | 25 | |
| g | 100 | |||
| h | 25 |
How do I merge the two columns of customer names together (like below) but keep their respective 2016 and 2017 sales in separate columns?
| Customer | 2016 | 2017 |
| a | 700 | 100 |
| b | 650 | 900 |
| c | 625 | |
| d | 500 | |
| e | 400 | 375 |
| f | 375 | |
| g | 100 | |
| h | 25 | 400 |
| i | 50 | |
| j | 25 |
Thanks for any help you can give.
Click on "Show Queries" on the Data tab in the ribbon. That will open the "Queries & Connections" pane.
This workbook has three queries.
Double-click on one of the queries to get to the query editor.
Open the navigation pane on left side to see all the queries.
"tbl_2016" and "tbl_2017" just load both tables into PQ. They are loaded as connection only.
The last query is build via the UI. Both tables are appended. The columns "Year" and "Sales" are unpivoted. Then the table is pivoted with column "Year".
The query "Append1" is loaded as a table to the worksheet.
Hi
I would suggest a Power Query solution. See attached file.
Thanks Detlef,
I'm new to excel, please can you advise how to use the power query solution to produce the combined result from the original two sets of data?
Thanks.
Click on "Show Queries" on the Data tab in the ribbon. That will open the "Queries & Connections" pane.
This workbook has three queries.
Double-click on one of the queries to get to the query editor.
Open the navigation pane on left side to see all the queries.
"tbl_2016" and "tbl_2017" just load both tables into PQ. They are loaded as connection only.
The last query is build via the UI. Both tables are appended. The columns "Year" and "Sales" are unpivoted. Then the table is pivoted with column "Year".
The query "Append1" is loaded as a table to the worksheet.
