smashclash
Board Regular
- Joined
- Nov 24, 2003
- Messages
- 126
- Office Version
- 365
- Platform
- Windows
I have a set of data like below that I need to separate out. Rather than the data be broken out for each single instance of a name it's grouped together at times and summed the amt. So for example in the below picture I'd like Chip Dale and Vicky Micky to be in separate rows and each have an amount of $500. VBA could identify the need to break this out because of the comma in column A. Frank Tank in A3 would be no action since there are no commas. Chip Dale, Vicky Micky, Mike Ike in A5 would need two rows added and the 800 divided by 3 (266.67) since there are three names for the Amt.
For all the rows with commas the names should only appear one time after VBA runs. So it could work as follows. Search column A for a comma. FInd comma and insert the number of rows equivalent to the number of commas. Then adjust the Names so they only appear once. Example, search column A for comma, find one in Column A2. Clone row 2. Change name in row A2 to Chip Dale and amount to be 500. The new row added would be Vicky Micky and Amt is 500. Then repeat for any other commas in column A.
For all the rows with commas the names should only appear one time after VBA runs. So it could work as follows. Search column A for a comma. FInd comma and insert the number of rows equivalent to the number of commas. Then adjust the Names so they only appear once. Example, search column A for comma, find one in Column A2. Clone row 2. Change name in row A2 to Chip Dale and amount to be 500. The new row added would be Vicky Micky and Amt is 500. Then repeat for any other commas in column A.