VBA To create unique value from Cell with 2 words - Split Value - Excel 2010

[Johnny Thunder]

Hello,

I am working on a VBA project to break-out a cell that has two names listed into unique individual lines. I will attach a screenshot to elaborate on this.

Excel 2012
	A	B	C	D	E
1	Unique Value	State	Column A	Column B	Column C
2	ABC3	California	Test1	Happy	Left
3	ABC4	Texas	Test1, Test2, Test3	Sad	Right
4	ABC5	Ohio	Test2	Tired	Right
5	ABC6	North Carolina	Test3, Test4	Happy	Right
6	ABC7	Georgia	Test1	Tired	Left
7	ABC8	Washington	Test1	Sad	Straight
Sheet1

What I am trying to accomplish is to have a new row entry for each cell in Column A that has a "," followed by a new word.

So using the attached data for example Column A, Row 3 should result in 2 additional rows with all data duplicated and only include 1 of the 3 names from Cell C3 and remove the ",".

Hopefully I am explaining this well.

 

[hiker95]

Johnny Thunder,

If I understand you correctly.

Sample raw data:

Excel 2007
	A	B	C	D	E
1	Unique Value	State	Column A	Column B	Column C
2	ABC3	California	Test1	Happy	Left
3	ABC4	Texas	Test1, Test2, Test3	Sad	Right
4	ABC5	Ohio	Test2	Tired	Right
5	ABC6	North Carolina	Test3, Test4	Happy	Right
6	ABC7	Georgia	Test1	Tired	Left
7	ABC8	Washington	Test1	Sad	Straight
8
9
10
11
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E
1	Unique Value	State	Column A	Column B	Column C
2	ABC3	California	Test1	Happy	Left
3	ABC4	Texas	Test1	Sad	Right
4	ABC4	Texas	Test2	Sad	Right
5	ABC4	Texas	Test3	Sad	Right
6	ABC5	Ohio	Test2	Tired	Right
7	ABC6	North Carolina	Test3	Happy	Right
8	ABC6	North Carolina	Test4	Happy	Right
9	ABC7	Georgia	Test1	Tired	Left
10	ABC8	Washington	Test1	Sad	Straight
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 07/22/2014, ME793392
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 3), ", ") > 0 Then
    s = Split(Cells(r, 3), ", ")
    Rows(r + 1).Resize(UBound(s)).Insert
    Range("A" & r & ":E" & r).Resize(UBound(s) + 1).Value = Range("A" & r & ":E" & r).Value
    Cells(r, 3).Resize(UBound(s) + 1).Value = Application.Transpose(s)
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[hiker95]

Johnny Thunder,

If my resulting screenshot is not correct, then please attach a screenshot of the results (manually formatted by you) that you are looking for.

 

[Johnny Thunder]

Hiker95,

You Sir, are amazing! It worked flawlessly and your interpretation of what I posted was spot on!

I only have one question so I can learn from your code.

So the data I posted was just Test data, my actual report starts in column A and ends in column H with column H being the column that has the multiple values.

Would I simply change this piece of code

"Range("A" & r & ":E" & r)" to "Range("A" & r & ":H" & r) ?

 

[hiker95]

Johnny Thunder

Thanks for the feedback.

You are very welcome. Glad I could help.

So the data I posted was just Test data

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

Would I simply change this piece of code

"Range("A" & r & ":E" & r)" to "Range("A" & r & ":H" & r) ?

Without seeing your actual raw data, it is difficult to say.

I would suggest that you try the change your have provided.

If the change does not work, then post a screenshot of your actual raw data, and, a screenshot (manually formatted by you) of the results you are looking for.

 

[Johnny Thunder]

I was able to get what I wanted with the proposed changes.

thank you again!

 

[hiker95]

Johnny Thunder,

Thanks for the feedback.

You are very welcome, again. Glad your changes worked correctly.

And, come back anytime.