Split Cells with Alt-Enter to Rows

[lrgalvez2]

Hi! Can anyone help me please? I have data where the last 3 Columns have multiple data in each cell using Alt+Enter. I need to split the multiple data into one row for each data and for each row the first column is copied. Please see below:

​

I have used the Code submitted by Apo here in this thread and it works great for the Column "Types" but I need to perform it in two other columns.

I have thousands of records and any help will be appreciated.

Thanks

 

[hiker95]

lrgalvez2,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider that is based on the graphic/picture you have displayed.

You can changed the raw data worksheet name in the macro.

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D
1	Test	Types	Scores	Subscore
2	Math Test	1 2 3	70 20 36	23 4 66
3	English Test	4 5	10 15	12 99
4	Reading Test	6 7	55 88	12 76
5
6
7
8
9
Sheet1

After the macro:

Excel 2007
	A	B	C	D
1	Test	Types	Scores	Subscore
2	Math Test	1	70	23
3	Math Test	2	20	4
4	Math Test	3	36	66
5	English Test	4	10	12
6	English Test	5	15	99
7	Reading Test	6	55	12
8	Reading Test	7	88	76
9
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 ReorgSplitData()
' hiker95, 07/07/2015, ME866439
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 2 Step -1
    If InStr(.Cells(r, 2), vbLf) Then
      s = Split(.Cells(r, 2), vbLf)
      .Rows(r + 1).Resize(UBound(s)).Insert
      .Cells(r + 1, 1).Resize(UBound(s)) = .Cells(r, 1)
      .Cells(r, 2).Resize(UBound(s) + 1) = Application.Transpose(s)
      s = Split(.Cells(r, 3), vbLf)
      .Cells(r, 3).Resize(UBound(s) + 1) = Application.Transpose(s)
      s = Split(.Cells(r, 4), vbLf)
      .Cells(r, 4).Resize(UBound(s) + 1) = Application.Transpose(s)
    End If
  Next r
End With
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgSplitData macro.

 

[lrgalvez2]

Hello Hiker!

This is the solution I was looking for. Thanks so much for the assistance.

 

[hiker95]

lrgalvez2,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[lrgalvez2]

Hello Hiker it's me again. The Code you gave works if the cells that need to be split are found in Column B - D. I have cases where the cells in columns A - F are single line cells (Like Column A in my example) then columns G - I are multi-line cells (Like Columns B-D of example) and then again Columns J - K are single line cells. Below is sample:

Can you please provide me with a code that works with the above example? I tried to do it myself but it's not as easy as it seems. I'm a complete beginner when it comes to VB.

Thanks in advance.

 

[hiker95]

lrgalvez2,

You are posting a much larger picture/graphic again. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.

In order to continue you will have to supply actual screenshots that I can use for testing:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[lrgalvez2]

Here's a sample of the actual data:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	From This:
2	ID	First	Last	Email	Date	Library	Test(s)	Score	Percentile	Comp	Industry	% Range	Competency	As of
3	1091	Juan	Cruz	juandelacruz@gmail.com	2/2/10		Math	24.00	80	ABC	Food	71-100	Multiple	7/9/15
4	1092	John	Doe	john.doe@yahoo.com	12/9/11	Battery 1	English Verbal Comprehension	90.0 2.88 Multi	73.0 53.0 Multi	XYZ	Car	FALSE		7/9/15
5
6
7	To This:
8	ID	First	Last	Email	Date	Library	Test(s)	Score	Percentile	Comp	Industry	% Range	Competency	As of
9	1091	Juan	Cruz	juandelacruz@gmail.com	2/2/10		Math	24.00	80	ABC	Food	71-100	Multiple	7/9/15
10	1092	John	Doe	john.doe@yahoo.com	12/9/11	Battery 1	English	90	73	XYZ	Car	71-100	Single	7/9/15
11	1092	John	Doe	john.doe@yahoo.com	12/9/11	Battery 1	Verbal	2.88	53	XYZ	Car	31-70	Single	7/9/15
12	1092	John	Doe	john.doe@yahoo.com	12/9/11	Battery 1	Comprehension	Multi	Multi	XYZ	Car	FALSE	Multiple	7/9/15

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula L3 =IF(I3<=30,"0-30",IF((AND(I3>30,I3<=70)),"31-70",IF((AND(I3>70,I3<=100)),"71-100"))) L9 =IF(I9<=30,"0-30",IF((AND(I9>30,I9<=70)),"31-70",IF((AND(I9>70,I9<=100)),"71-100"))) <tbody> </tbody>

<tbody>
</tbody>

I used MrExcelHtml. Great tool! Thanks for pointing it out to me.

 

[hiker95]

lrgalvez2,

I used MrExcelHtml. Great tool! Thanks for pointing it out to me.

You are very welcome.

Excel 2007
	A	B	C	G	H	I	M	N
1	ID	First	Last	Test(s)	Score	Percentile	Competency	As of
2	1091	Juan	Cruz	Math	24	80	Multiple	7/9/2015
3	1092	John	Doe	English Comprehension Verbal	90 2.88 Multi	73 53 Multi		7/9/2015
4
5
6
7	ID	First	Last	Test(s)	Score	Percentile	Competency	As of
8	1091	Juan	Cruz	Math	24	80	Multiple	7/9/2015
9	1092	John	Doe	English	90	73	Single	7/9/2015
10	1092	John	Doe	Verbal	2.88	53	Single	7/9/2015
11	1092	John	Doe	Comprehension	Multi	Multi	Multiple	7/9/2015
12
Sheet1

Is the text shown in range M9:M11, supposed to be in cell M3?

 

[hiker95]

lrgalvez2,

Here is a macro solution for you to consider based on your posted screenshots.

Sample raw data in two screenshots to fit the MrExcel display area:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	ID	First	Last	Email	Date	Library	Test(s)	Score	Percentile
2	1091	Juan	Cruz	juandelacruz@gmail.com	2/2/2010		Math	24	80
3	1092	John	Doe	john.doe@yahoo.com	12/9/2011	Battery 1	English Comprehension Verbal	90 2.88 Multi	73 53 Multi
4
5
6
Sheet1

Excel 2007
	J	K	L	M	N
1	Comp	Industry	% Range	Competency	As of
2	ABC	Food	71-100	Multiple	7/9/2015
3	XYZ	Car	FALSE	Single Single Multiple	7/9/2015
4
5
6
Sheet1

Cell Formulas
Range	Formula
L2	=IF(I2<=30,"0-30",IF((AND(I2>30,I2<=70)),"31-70",IF((AND(I2>70,I2<=100)),"71-100")))
L3	=IF(I3<=30,"0-30",IF((AND(I3>30,I3<=70)),"31-70",IF((AND(I3>70,I3<=100)),"71-100")))

After the macro in two screenshots to fit the MrExcel display area:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	ID	First	Last	Email	Date	Library	Test(s)	Score	Percentile	Comp
2	1091	Juan	Cruz	juandelacruz@gmail.com	2/2/2010		Math	24	80	ABC
3	1092	John	Doe	john.doe@yahoo.com	12/9/2011	Battery 1	English	90	73	XYZ
4	1092	John	Doe	john.doe@yahoo.com	12/9/2011	Battery 1	Comprehension	2.88	53	XYZ
5	1092	John	Doe	john.doe@yahoo.com	12/9/2011	Battery 1	Verbal	Multi	Multi	XYZ
6
Sheet1

Excel 2007
	K	L	M	N
1	Industry	% Range	Competency	As of
2	Food	71-100	Multiple	7/9/2015
3	Car	71-100	Single	7/9/2015
4	Car	31-70	Single	7/9/2015
5	Car	FALSE	Multiple	7/9/2015
6
Sheet1

Cell Formulas
Range	Formula
L2	=IF(I2<=30,"0-30",IF((AND(I2>30,I2<=70)),"31-70",IF((AND(I2>70,I2<=100)),"71-100")))
L3	=IF(I3<=30,"0-30",IF((AND(I3>30,I3<=70)),"31-70",IF((AND(I3>70,I3<=100)),"71-100")))
L4	=IF(I4<=30,"0-30",IF((AND(I4>30,I4<=70)),"31-70",IF((AND(I4>70,I4<=100)),"71-100")))
L5	=IF(I5<=30,"0-30",IF((AND(I5>30,I5<=70)),"31-70",IF((AND(I5>70,I5<=100)),"71-100")))

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 ReorgSplitDataV2()
' hiker95, 07/09/2015, ME866439
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 2 Step -1
    If InStr(.Cells(r, 7), vbLf) Then
      s = Split(.Cells(r, 7), vbLf)
      .Rows(r + 1).Resize(UBound(s)).Insert
      .Cells(r + 1, 1).Resize(UBound(s), 6).Value = .Cells(r, 1).Resize(, 6).Value
      .Cells(r, 7).Resize(UBound(s) + 1) = Application.Transpose(s)
      s = Split(.Cells(r, 8), vbLf)
      .Cells(r, 8).Resize(UBound(s) + 1) = Application.Transpose(s)
      s = Split(.Cells(r, 9), vbLf)
      .Cells(r, 9).Resize(UBound(s) + 1) = Application.Transpose(s)
      .Cells(r + 1, 10).Resize(UBound(s), 2).Value = .Cells(r, 10).Resize(, 2).Value
      .Cells(r, 12).Copy .Cells(r + 1, 12).Resize(UBound(s))
      s = Split(.Cells(r, 13), vbLf)
      .Cells(r, 13).Resize(UBound(s) + 1) = Application.Transpose(s)
      .Cells(r + 1, 14).Resize(UBound(s)) = .Cells(r, 14)
    End If
  Next r
End With
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgSplitDataV2 macro.

 

[lrgalvez2]

Hiker your Code worked perfectly. No way could I have done this without assistance from you. I have over 10,000 rows to work with. Thank you so much.

Thank you to MrExcel for providing the venue for leeting this happen.

Regards to everyone