Here is my situation. I need to take a list of 38k customer accounts and sort the information to a specific template that I can import to our new website. However the way the data is presented makes that difficult…
Every address saved for 1 customer is a separate line. So if you have a different billing or shipping address it would be 2 rows. Sometimes 3 rows. I need to move those rows into a column (Address 1, Address 2, etc.) under 1 row. So if CustomerA has 3 saved address, I need to take those 3 rows and move them into 3 columns of 1 row. I hope that makes sense.
I have no idea if a formula, pivot table, or VBA(I know as much as Jon Snow in this area) would be the best route to take. I tried pivot tables, but it's not really working out. I was thinking Index/Match might be the best solution – I'd just have to duplicate the formula for each Address column, but it'd get the job done. However I don't know how I can accommodate for the multiple rows.
Below is an example of the data I received. Line 20 is the template I need to move the rows into. Column g_user id is unique per customer, but with multiple rows of addresses for a single customer that value will be duplicated in the spreadsheet. Same with customer_no.
http://www.filedropper.com/excelhelp
Please let me know if you need more information and I will try to explain further.
EDIT – From the spreadsheet I posted, I need to take rows 2 and 3 and move the address information into new columns in row 1. So since address is 2 columns, city is 1, state is 1, zip code is 1, and country is 1 this makes 12 total columns of data from those 2 rows.
Best Answer