Forum Discussion
Merging Customer Databases and Updating Rows
Hi Team,
New to Excel. Using Office 365. I am using Excel to format and manage my customer database. We are switching CRMS, (VINsolutions to Hubspot). When we did the data export out of the previous CRM, it unfortunately made 6 seperate sheets, each with a different section of data. One sheet is customer data, other is future task set, other is deal data (we are a car dealership) etc. My problem is when I upload to Hubspot, I need each customer to be one row, not 6 rows of data across 6 sheets, as they won't merge on Hubspot via 6 different sheets.
My question is this, how do I merge sheets and have them update a row based on a number in the column? I do have a unique "CustomerID" that is attached to each row and is consistent across each sheet.
Thanks in advance and I am sorry if I didn't explain this well.
Ryan
Power Query can "join" the 6 different sheets using the unique "CustomerID".
Basic process:1) Click a data cell in first worksheet then use menu path: Data > From Table/Range. This creates a Query. Click Close and Load (down arrow) > Close and Load to > Only Create Connection
2) Repeat step one for all other worksheets. On last worksheet, do not click close and load.
3) Click first query then Merge Queries (down arrow) > Merge Queries as New
4) Add second Query and select Customer ID in both queries, Select Join Kind Inner Join. Click OK
5) Select Merge1 query and expand new column unchecking CustomerID.
6) Select Merge1 query then Merge Queries (not as new).
7) Repeat 4, 5, & 6 until all 6 queries are merged then click Close and Load to > Only Create Connection
7) Right click Merge1 in Queries & Connections panel and select Load to > Table > New worksheet
If you need help, send copy of workbook and I'll take care of it.
3 Replies
If you have unique ID you may use XLOOKUP to combine the data into one table. Perhaps Power Query will be better, but it require bit more effort on initial period if you never used it.
Power Query can "join" the 6 different sheets using the unique "CustomerID".
Basic process:1) Click a data cell in first worksheet then use menu path: Data > From Table/Range. This creates a Query. Click Close and Load (down arrow) > Close and Load to > Only Create Connection
2) Repeat step one for all other worksheets. On last worksheet, do not click close and load.
3) Click first query then Merge Queries (down arrow) > Merge Queries as New
4) Add second Query and select Customer ID in both queries, Select Join Kind Inner Join. Click OK
5) Select Merge1 query and expand new column unchecking CustomerID.
6) Select Merge1 query then Merge Queries (not as new).
7) Repeat 4, 5, & 6 until all 6 queries are merged then click Close and Load to > Only Create Connection
7) Right click Merge1 in Queries & Connections panel and select Load to > Table > New worksheet
If you need help, send copy of workbook and I'll take care of it.Thanks Craig. I am going to give it a try, feels like something I should know how to do. I'll give you a shout if I need any more help. Much appreciated!
