I have several very large Excel spreadsheets containing customer information, each record consisting of multiple rows (variable). I need to coerce the existing data into a standardized format with a fixed number of rows, concatenating some cells into one and breaking others into separate rows. While I don't think the VBA required to perform any one of these tasks may not be all that difficult, when I tried doing this myself I quickly hit a wall and gave up. Perhaps someone here can assist...
The existing spreadsheet looks like this:
Notice how the Address
field can be comprised of either 3 or 4 lines (depending on whether or not there is a "Suite #) and the Business Classification(s)
and Countries where work is performed
fields can span multiple rows. I need to coerce the existing spreadsheet such that:
the Address field is a concatenation of street address and optional suite # separated by a comma
the City, ST Zip information is separated into individually labeled rows (City, ST and Postal Code)
the Country info is separated into its own labeled row (Country)
the Business Classification(s) info is concatenated into one cell separated by commas
the Countries where work is performed info is concatenated into one cell separated by commas
I would like to get the resultant spreadsheet post-manipulation to have one customer record per row with each field (Name, Address, City, ST, Postal Code, Country, etc) in its own column.
The Excel spreadsheets I currently have comprise several hundreds of thousands of records spread across multiple spreadsheets. Any assistance with the VBA necessary to massage the data into the desired format is greatly appreciated. Thank you.