Separating multiple paragraphs of data from one cell in excel

[eagiordano]

Hello All.

I am looking to separate the following data into a certain format in excel. Currently the data is in one cell separated in paragraphs with titles. See below

---------

Case International IH
580, 1594, 1694, MXM120, MXM130, MXM140, MXM155, MXM175, MXM190

County
654, 754, 764, 774, 944, 964, 974, 1164, 1164TW, 1184TW

David Brown
1394, 1410, 1412, 1494, 1594, 1690, 1694

Fiat
M100, M115, M135, M160

Fordson
Major, Power Major, Super Major

Ford New Holland
230A, 231, 233, 234, 250C, 260C, 333, 334, 335, 345, 420, 445, 450, 455, 515, 530A, 531, 532, 535, 545, 550, 555, 575D, 650, 655, 675D, 750, 755, 2000, 2100, 2110, 2120, 2150, 2300, 2310, 2600, 2610, 2810, 2910, 3000, 3055, 3100, 3110, 3120, 3150, 3230, 3300, 3310, 3330, 3400, 3430, 3500, 3550, 3600, 3610, 3900, 4000, 4100, 4110, 4140, 4200, 4330, 4340, 4400, 4410, 4500, 4600, 4630, 4830, 5000, 5030, 5100, 5110, 5200, 5340, 5500, 5550, 5600, 5610, 5610S, 5640, 5700, 5900, 6410, 6500, 6600, 6610, 6610S, 6640, 6700, 6710, 6810, 6810S, 7000, 7100, 7200, 7410, 7500, 7600, 7610, 7610S, 7630, 7700, 7710, 7740, 7810, 7810S, 7840, 7910, 8000, 8030, 8160, 8200, 8210, 8240, 8260, 8340, 8360, 8530, 8560, 8600, 8630, 8700, 8730, 8830, 9000, 9200, 9600, 9700, TB100, TB120, TM115, TM120, TM125, TM130, TM135, TM140, TM150, TM155, TM165, TM165 Brazil, TM175, TM190, TS80, TS90, TS100, TS110, TS115, TW5, TW10, TW15, TW20, TW25, TW30, TW35

---------

The data listed above obviously changes from cell to cell but the format of having the title (Make) and data (Model) below is the same. All the models are separated by commas. I need to convert it into the following format in two columns. Each Model on individual rows with the Make listed beside it.

Thank you in advance for your help.

Regards,
eagiordano

 

[JLGWhiz]

Assuming that all data is listed in column A and that groups are separated by a blank row:

Sub t()
Dim a As Range, spl As Variant, i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
    With sh1
        For Each a In .Columns(1).SpecialCells(xlCellTypeConstants).Areas
            sh2.Cells(Rows.Count, 1).End(xlUp)(2) = a.Cells(1, 1).Value
            spl = Split(a.Cells(2, 1), ", ")
            r = sh2.Cells(Rows.Count, 1).End(xlUp).Row
            For i = LBound(spl) To UBound(spl)
                sh2.Cells(r, 2) = spl(i)
                r = r + 1
            Next
            r = 0
            sh2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(spl)) = a.Cells(1, 1).Value
        Next
    End With
End Sub

This will put the results in sheet 2.

 

[eagiordano]

Assuming that all data is listed in column A and that groups are separated by a blank row:

Sub t()
Dim a As Range, spl As Variant, i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
    With sh1
        For Each a In .Columns(1).SpecialCells(xlCellTypeConstants).Areas
            sh2.Cells(Rows.Count, 1).End(xlUp)(2) = a.Cells(1, 1).Value
            spl = Split(a.Cells(2, 1), ", ")
            r = sh2.Cells(Rows.Count, 1).End(xlUp).Row
            For i = LBound(spl) To UBound(spl)
                sh2.Cells(r, 2) = spl(i)
                r = r + 1
            Next
            r = 0
            sh2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(spl)) = a.Cells(1, 1).Value
        Next
    End With
End Sub

This will put the results in sheet 2.

Hello @JLGWhiz. Thank you for your reply, really appreciate it.

Sorry I don't think I explained it very well initially. The data for separation is in one cell and separated by paragraph breaks not by rows. Also I forgot to mention, in column A I have a reference number which could do with being replicated in the output data. See screenshot below of the data.

Input data:

Output data:

I have noticed that your first code threw up an error when the data only had one model. Like the Ferguson FE35 data on ref 11547. Furthermore on some data like JCB we have multiple lines of data separated by line breaks which also cause an issue.

I look forward to hearing from you, and once again thank you for your help.

Regards,
eagiordano

 

[eagiordano]

Hello @JLGWhiz. Thank you for your reply, really appreciate it.

Sorry I don't think I explained it very well initially. The data for separation is in one cell and separated by paragraph breaks not by rows. Also I forgot to mention, in column A I have a reference number which could do with being replicated in the output data. See screenshot below of the data.

Input data:

View attachment 27498

Output data:

View attachment 27500

I have noticed that your first code threw up an error when the data only had one model. Like the Ferguson FE35 data on ref 11547. Furthermore on some data like JCB we have multiple lines of data separated by line breaks which also cause an issue.

I look forward to hearing from you, and once again thank you for your help.

Regards,
eagiordano

@JLGWhiz please find the data below in text format.

Ref: 11547

Case International IH
238, 248, 258, 268, 278, 288, 385, 395, 454, 474, 475, 484, 485, 485XL, 495, 495XL, Hydro 84

Ferguson
FE35

JCB
805, 805B, 806, 806B, 806C, 807, 807B, 807C, 808, 808B
Backhoe loader
3C Mk111, 3D Mk111
Earthmoving Equipment
410, 415, 420, 425, 430
Loading shovel
140, 406, 412, 415, 420, 430, 435

Massey Ferguson
35, 35X, 65, 133, 135, 148, 152, 158, 165, 168, 175, 178, 185, 188, 230, 230 US Built, 231, 235, 240, 243, 245, 250
Industrial
20, 20D, 20F, 30E, 30H, 40E, 50, 50B, 50E, 50EX, 50F, 50H, 50HX, 60H

-------------------------------------

Ref: 11569

Case International IH
580, 1594, 1694, MXM120, MXM130, MXM140, MXM155, MXM175, MXM190

County
654, 754, 764, 774, 944, 964, 974, 1164, 1164TW, 1184TW

David Brown
1394, 1410, 1412, 1494, 1594, 1690, 1694

Fiat
M100, M115, M135, M160

Fordson
Major, Power Major, Super Major

--------------------------------------

Thanks
eagiordano

 

[JLGWhiz]

Sorry @eagiordano - afraid I won''t be able to help with the solid block of text. Maybe someone smarter than me will jump in and offer a solution.
regards, JLG

 

[eagiordano]

Sorry @eagiordano - afraid I won''t be able to help with the solid block of text. Maybe someone smarter than me will jump in and offer a solution.
regards, JLG

@JLGWhiz no problem. Thank you for your assistance.
Regards,
eagiordano

 

[JLGWhiz]

You might try the text to columns feature under the Data tab on the ribbon and see what that does. It might not give you exactly what you want but it could possibly make the data more conducive to using vba.

 

[eagiordano]

You might try the text to columns feature under the Data tab on the ribbon and see what that does. It might not give you exactly what you want but it could possibly make the data more conducive to using vba.

Yes that might be a good idea. I could potentially put it into the following format using text to columns if you think that would work better for VBA?

Let me know what you think.

 

[JLGWhiz]

It needs some housekeeping, but that should not be all that difficult. The data in column A without any apparent owner needs to be rconciled. Then if th others can be arraange with the owner in a single column and their models in the adjacent column of the corresponding row, then I can write the code to make a continuous list of each owner in one column and their models in and adjacent column. I could almost do it from the way it is displayed in the screen shot but it would be better if you can rearrange it by copy and paste to eleminate the oddball stuff and make it more uniform in structure. The less guesswork I have to do the better the results for you.

I see a difference in pattern at the far right and cannot see all of it to tell exactly what that implies.

 

[eagiordano]

It needs some housekeeping, but that should not be all that difficult. The data in column A without any apparent owner needs to be rconciled. Then if th others can be arraange with the owner in a single column and their models in the adjacent column of the corresponding row, then I can write the code to make a continuous list of each owner in one column and their models in and adjacent column. I could almost do it from the way it is displayed in the screen shot but it would be better if you can rearrange it by copy and paste to eleminate the oddball stuff and make it more uniform in structure. The less guesswork I have to do the better the results for you.

I see a difference in pattern at the far right and cannot see all of it to tell exactly what that implies.

Ahh ok. Are you able to show me an example of the format you are explaining? Only issue is that if it requires quite a lot of copy and pasting then it might not be viable for the amount of data I have.

The difference in pattern is caused by the JCB data which is as follows:

JCB
805, 805B, 806, 806B, 806C, 807, 807B, 807C, 808, 808B
Backhoe loader
3C Mk111, 3D Mk111
Earthmoving Equipment
410, 415, 420, 425, 430
Loading shovel
140, 406, 412, 415, 420, 430, 435

The Models are split into series which I admit creates more confusion. But I do not mind if the script treats the series as additional models because they can be removed at the end easily.