VBA - Excel Macro to separate and sort data from one row into many when necessary.
Budget: $10 – $30 USD
We need to take the data from "Raw" tab in the attached file and make it look like the "Guests" tab (see the different tabs).
There is a third Tab named Key to show which col from Raw (Key - Col A) goes with which col in Guests (Key - Col B, C, Etc.).
Please use Col/Row letters or numbers in VBA rather than the text since the col titles may change. We need to be able to run this on many rows as our dataset grows.
Here are the basics of the program:
Use Col D in "Raw" to create the needed rows in the table in "Guests".
If Raw - Col D is 1 there is only 1 guest in the raw data. If it is 2 there are 2 guests, 3 = 3 guests, etc. There may be up to 10 guests per entry. Each guest needs to be on its own line in the "Guests" table.
Raw - Col C always needs to be added to every row in Guests - Col A depending on how many entries there are.
Raw - Col F always needs to be added to every row in Guests - Col J depending on how many entries there are.
These two columns will be the same for each row for Guest 1, Guest 2, Guest 3, etc. until it hits the next dataset.
The number in Raw - Col D should only appear in the first row in Guests - Col B and not in the next rows (which should have Col B empty until the next data set from Raw).
Let me know if you have any questions.
If Raw - Col D is 1 just add Col C, F, G-M from Raw into their corresponding columns in Guests.
If Raw - Col D is 2 add the data from above in the first available row and then add Col O,P,Q,S,T,U,V in Raw into their corresponding columns in Guests in the next row.
If Raw - Col D is 3 add the data from above in the first available rows and then add Col X-AD in Raw into their corresponding columns in Guests in the next row.
If Raw - Col D is 4 add the data from above in the first available rows and then add Col AF-AL in Raw into their corresponding columns in Guests in the next row.
If Raw - Col D is 5 add the data from above in the first available rows and then add Col AN-AT in Raw into their corresponding columns in Guests in the next row.
Continue this pattern for a possible 10 guests (current data set only has 5 but we will expand to 10. Ignore Col AV in RAW, we will most likely remove that or it will be at the end.
One other thing, if the email/address/city/state/zip for any of Guests 2- 10 is empty please add the email/address/city/state/zip from Guest 1 into their corresponding cols.
Let me know if you have any questions.
There is a third Tab named Key to show which col from Raw (Key - Col A) goes with which col in Guests (Key - Col B, C, Etc.).
Please use Col/Row letters or numbers in VBA rather than the text since the col titles may change. We need to be able to run this on many rows as our dataset grows.
Here are the basics of the program:
Use Col D in "Raw" to create the needed rows in the table in "Guests".
If Raw - Col D is 1 there is only 1 guest in the raw data. If it is 2 there are 2 guests, 3 = 3 guests, etc. There may be up to 10 guests per entry. Each guest needs to be on its own line in the "Guests" table.
Raw - Col C always needs to be added to every row in Guests - Col A depending on how many entries there are.
Raw - Col F always needs to be added to every row in Guests - Col J depending on how many entries there are.
These two columns will be the same for each row for Guest 1, Guest 2, Guest 3, etc. until it hits the next dataset.
The number in Raw - Col D should only appear in the first row in Guests - Col B and not in the next rows (which should have Col B empty until the next data set from Raw).
Let me know if you have any questions.
If Raw - Col D is 1 just add Col C, F, G-M from Raw into their corresponding columns in Guests.
If Raw - Col D is 2 add the data from above in the first available row and then add Col O,P,Q,S,T,U,V in Raw into their corresponding columns in Guests in the next row.
If Raw - Col D is 3 add the data from above in the first available rows and then add Col X-AD in Raw into their corresponding columns in Guests in the next row.
If Raw - Col D is 4 add the data from above in the first available rows and then add Col AF-AL in Raw into their corresponding columns in Guests in the next row.
If Raw - Col D is 5 add the data from above in the first available rows and then add Col AN-AT in Raw into their corresponding columns in Guests in the next row.
Continue this pattern for a possible 10 guests (current data set only has 5 but we will expand to 10. Ignore Col AV in RAW, we will most likely remove that or it will be at the end.
One other thing, if the email/address/city/state/zip for any of Guests 2- 10 is empty please add the email/address/city/state/zip from Guest 1 into their corresponding cols.
Let me know if you have any questions.