Adding rows based on comma value

[smashclash]

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.

 

[sandy666]

there is no add-in for 365
ask IT about your version contain Power Query or not

 

[smashclash]

I asked and this is what he wrote back:

Important: Data Catalog Deprecation Notice
Data Catalog capabilities in Excel and Power Query stopped working on March 4th, 2019.



You should be able to download it like you showed me in the last screen-shot. It should work with 365.

 

[sandy666]

try this first, refresh green table
download, don't open in Excel online

s666-PQ-split2rows by number of comma and divide next column by number of comma.xlsm

Microsoft Excel Workbook

1drv.ms

 

[smashclash]

OK, I clicked on the file. Opened up Excel Online and I downloaded a copy of the file. I then opened it up in Excel on my PC. Right clicked within the green table and hit refresh. It appears to have processed but none of the data changed (don't think it should have).

 

[sandy666]

in short:
if you don't have Power Query you will get error
if you don't see any error - you have Power Query

I suggest to read
more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

btw. you didn't update your profile about Excel version

 

[smashclash]

Thank you again! I just updated my profile. I will read up on PQ later today.

 

[sandy666]

Glad you are Happy
Thanks for the feedback