VBA Macro to copy and insert rows if following condition is met

[greykitten]

Hello Forum,
I just discovered VBA and I'm trying to write a conditional macro where VBA will add (x) number of rows depending on how many separate numbers appear in a specific cell. However, I can't find any postings that specifically address this scenario. I've attached a generic and fictional example below of how the data appears and how it needs to look like.

Goal: If a cell in column M has multiple cust_numbers, then that row needs to be copied and populated underneath with the next number in that series which would then repeat until each cust_number has its' own row. I am using Excel 2010.

Important Notes: Ideally, all of the customer data that appears in columns A - L and N - onward should all replicate in the rows that are created beneath the first row. The only difference would be the value in column M. Also, all of the account number values will be delimited by a comma that separates them and some of them will vary slightly in length. Last, the total number of rows will vary each month so the ending row would need to be dynamic.

CURRENT FORM:

Column A ......................... Column M
Customer ......................... Cust_Number
Customer_1 ......................... 1111111,2222222,333333333,4444444,555555,666666

DESIRED FORM

Column A ......................... Column M
Customer ......................... Cust_Number
Customer_1 ......................... 1111111
Customer_1 ......................... 2222222
Customer_1 ......................... 333333333
Customer_1 ......................... 4444444
Customer_1 ......................... 555555
Customer_1 ......................... 666666

I have no idea if this can be done but I would be eternally grateful if so

Thank you in advance for your help!
~Eric

 

[r2c2]

@greykitten Interesting problem.

Here is a VBA solution to expand customer data. This assumes you have 13 columns of data in A:M, starting with headers in row 1 and your data is continuous in column M (ie no blanks). Feel free to change the code as per your data shape & size. Keep a backup of original data before running the macro.

Use Expand button to expand the test data. Use Reset to erase the expanded data and replace it with rawdata range.

 

[greykitten]

Hello r2c2,
Wow, this is amazing work! It works perfectly except for two things.

1) I also have data in columns N - AS. I can't figure out what to modify in the macro to copy data found in columns N - AS in the same way that columns A - L are copied. Would that be done by changing the range to "AS1", instead of "M1"?

2) Unfortunately, there are a few instances of cells in column M that do not have values. When I tried testing this by adding a row without a value in column M in between other rows with multiple values, the rows below that with multiple number values did not expand. Do you have any idea on how to overcome this?

Also, I receive this data monthly so for this to function properly, would I need to paste the data first in the "rawdata" tab, then a second time in the "expand" tab? Would that be the easiest way? I really like the expand and reset buttons you added.

Thank you again for all of your time and effort. This is truly awesome!

~ greykitten