Comma delimited list and rows into columns

jstar88 · Jun 1st 2011

Currently this is what I have:
Part No. | Description | Place | Manufacturer Name | Manufacturer Part No.
123-456 | hi | | California | 300-123
123-456 | hi | | China | 300-125
456-789 | hello | A3, E5| Arizona | 400-126
123-789 | hey | 15, C4, E2| Mexico | A32-123
123-789 | hey | 15, C4, E2 | Germany | E21-654
123-789 | hey | 15, C4, E2 | Italy | F11-354

and i need it to be formatted as:
Part No. | Description | Place | Manufacturer Name | Manufacturer Part No.
123-456 | hi | | California | 300-123 |China | 300-125
123-456 | hi | | California | 300-123 |China | 300-125
456-789 | hello | A3| Arizona | 400-126
456-789 | hello | E5| Arizona | 400-126
123-789 | hey | 15| Mexico | A32-123 | Germany| E21-654 | Italy| F11-354
123-789 | hey | C4| Mexico | A32-123 | Germany| E21-654 | Italy| F11-354
123-789 | hey | E2| Mexico | A32-123 | Germany| E21-654 | Italy| F11-354

There's a lot of things going on at once, but basically i need to separate the comma list into new rows and then for items with multiple manufacturers, list them next to each other rather than separating it as a new row.

Thanks!

Stanley D. Grom · Jun 2nd 2011

Re: Comma delimited list and rows into columns

jstar88,

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

Have a great day,
Stan

**PCI** · Jun 2nd 2011

Re: Comma delimited list and rows into columns

Try and comment.
There is 2 steps and therefore 2 macros.

Code

Option Explicit
Sub Prepare1()
Dim LastRow As Long
Dim NbPlace As Integer
Dim I As Long, J As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For I = LastRow To 3 Step -1
        NbPlace = Len(Cells(I, "C")) - Len(Replace(Cells(I, "C"), ",", "")) + 1
        If (NbPlace > 1) Then
            Rows(I + 1 & ":" & I + NbPlace - 1).Insert Shift:=xlDown
            For J = 1 To NbPlace - 1
                Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
                Cells(I + J, "C") = Split(Cells(I, "C"), ", ")(J)
            Next J
            Cells(I, "C") = Split(Cells(I, "C"), ",")(0)
        End If
    Next I
End Sub


Sub Prepare2()
Dim LastRow As Long, LastCol As Long
Dim I As Long, J As Long
Dim MyRg As Range
Dim F
Dim MyValue
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For I = LastRow To 2 Step -1
        Set MyRg = Range("C2:C" & I - 1)
        MyValue = Cells(I, "C")
        If (MyValue <> Empty) Then
            With MyRg
                Set F = .Find(What:=MyValue, After:=.Cells(1, 1), LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
                If (Not F Is Nothing) Then
                    LastCol = Cells(F.Row, Columns.Count).End(xlToLeft).Column
                    Range("D" & I).Resize(1, 2).Copy Destination:=Cells(F.Row, LastCol + 1)
                    Rows(I & ":" & I).Delete Shift:=xlUp
                End If
            End With
        End If
    Next I
End Sub

Display More

jstar88 · Jun 2nd 2011

Thanks PCI. Will try it out.

jstar88 · Jun 2nd 2011

Re: Comma delimited list and rows into columns

I came across another issue which is:
I have a cell that says for instance R260-R263.

Can't think of a macro of how to separate it in new lines so that it becomes:
R260
R261
R262
R263

Thanks! similar to commas but now with hyphen. is the code similar as well?

**PCI** · Jun 2nd 2011

Re: Comma delimited list and rows into columns

New revision for PREPARE1

Code

Option Explicit
Sub Prepare1()
Dim LastRow As Long
Dim NbPlace1 As Integer, NbPlace2 As Integer
Dim FirstNb As Integer, LastNb As Integer
Dim I As Long, J As Long
Dim Place
Dim TEMP
Dim PlaceRef As String
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For I = LastRow To 3 Step -1
        Place = Cells(I, "C")
        NbPlace1 = Len(Place) - Len(Replace(Place, ",", "")) + 1   '  +1  FOR  THE SPACE AFTER COMMA
        NbPlace2 = Len(Place) - Len(Replace(Place, "-", ""))
        If (NbPlace1 > 1) Then
            Rows(I + 1 & ":" & I + NbPlace1 - 1).Insert Shift:=xlDown
            For J = 1 To NbPlace1 - 1
                Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
                Cells(I + J, "C") = Split(Place, ", ")(J)
            Next J
            Cells(I, "C") = Split(Place, ",")(0)
        End If
        If (NbPlace2 > 0) Then
            NbPlace2 = InStr(Place, "-")
            TEMP = Left(Place, NbPlace2 - 1)
            PlaceRef = Left(TEMP, 1)                    '  RECORD  THE R AT THE BEGINNING
            TEMP = Right(TEMP, Len(TEMP) - 1)
            FirstNb = TEMP * 1
 '---------
            TEMP = Right(Place, Len(Place) - NbPlace2)
            TEMP = Right(TEMP, Len(TEMP) - 1)           '  REMOVE  THE R AT THE BEGINNING
            LastNb = TEMP * 1
 '---------
            NbPlace2 = LastNb - FirstNb
            Rows(I + 1 & ":" & I + NbPlace2 - 1).Insert Shift:=xlDown
            For J = 1 To NbPlace2
                Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
                Cells(I + J, "C") = PlaceRef & FirstNb + J
            Next J
            Cells(I, "C") = PlaceRef & FirstNb
        End If
    Next I
End Sub

Display More

jstar88 · Jun 2nd 2011

Re: Comma delimited list and rows into columns

Thanks PCI but when I run it, I get a Subscript out of range error. Does this code include possible blanks in Column C?

Currently, this is the code I am using for the separation of the commas:

Code

Option Explicit
Sub Comma()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim mySplit As Variant
Dim HowMany As Long
Set wks = ActiveSheet
With wks
    FirstRow = 2 'no headers?
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For iRow = LastRow To FirstRow Step -1
    If InStr(1, .Cells(iRow, "C").Value, ",", vbTextCompare) > 0 Then
    mySplit = Split(.Cells(iRow, "C").Value, ",")
    HowMany = UBound(mySplit) - LBound(mySplit) + 1
    .Rows(iRow + 1).Resize(HowMany - 1).Insert
    .Cells(iRow, "C").Resize(HowMany, 1).Value _
    = Application.Transpose(mySplit)
    End If
    Next iRow
End With
End Sub

Display More

After separating the commas, there are still hyphens. Now I need to run some macro that identifies the hyphens and fills in the consecutive numbers in between those hyphens on separate rows?
For example,
[INDENT]i need to try and get from "R260-R263" (with the hyphen and only the numbers 260 and 263) in one cell to become:

R260
R261
R262
R263
[/INDENT]

**PCI** · Jun 2nd 2011

Re: Comma delimited list and rows into columns

See here the file used.
Here again send a sample of your data to be sure we are working on the data.

jstar88 · Jun 2nd 2011

Re: Comma delimited list and rows into columns

I attached a little change to the file and i get an error of type mismatch:

Do you mind explaning to me what is the reason for the error after you take a look?
I think it has to do something with: first separating the commas and then after that, going through column C and finding the hyphens.
along with recording the R at the beginning, sometimes it could be two or more letters in the front as well such as RA

Thanks for all the help! I really appreciate it!

**PCI** · Jun 3rd 2011

Re: Comma delimited list and rows into columns

The issue we are running into is how to extract the place number:
Is there 1 or 2 characters at the beginning?
Is there only 3 digits at the end?
There could be what ever we want just tell it

jstar88 · Jun 3rd 2011

Re: Comma delimited list and rows into columns

the problem is that it varies...it could go up to three characters in the beginning and up to 4 digits at the end.

**PCI** · Jun 3rd 2011

Re: Comma delimited list and rows into columns

Of course the last digits make a number greater on after the hyphen versus the number before the hyphen.

jstar88 · Jun 3rd 2011

Re: Comma delimited list and rows into columns

Quote from PCI;558421

Of course the last digits make a number greater on after the hyphen versus the number before the hyphen.

Can you help me adapt your macro to accomodate varying combinations of the "C" column?

For instance, I can have:
DS1-DS10 in a cell
A1234
1,2,A3,C3-C5

jstar88 · Jun 3rd 2011

Re: Comma delimited list and rows into columns

bumpp...please

**PCI** · Jun 4th 2011

Re: Comma delimited list and rows into columns

As the format of the place is becoming more sophisticated (mixe of comma and hyphen) it will take more time (for me) to prepare.

**PCI** · Jun 4th 2011

Re: Comma delimited list and rows into columns

Perhaps it is not so long to prepare.
Here 3 macros to be launched sequentially:
PREPARE1a, Prepare1b, PREPARE2
Of courese you can merge them after checking

Code

Option Explicit
Sub Prepare1a()
Dim LastRow As Long
Dim NbPlace1 As Integer, NbPlace2 As Integer
Dim FirstNb As Integer, LastNb As Integer
Dim I As Long, J As Long
Dim Place
Dim TEMP
Dim PlaceRef As String
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For I = LastRow To 3 Step -1
        Place = Cells(I, "C")
        NbPlace1 = Len(Place) - Len(Replace(Place, ",", "")) + 1   '  +1  FOR  THE SPACE AFTER COMMA
        If (NbPlace1 > 1) Then
            Rows(I + 1 & ":" & I + NbPlace1 - 1).Insert Shift:=xlDown
            For J = 1 To NbPlace1 - 1
                Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
                Cells(I + J, "C") = Split(Place, ",")(J)
            Next J
            Cells(I, "C") = Split(Place, ",")(0)
        End If
    Next I
    Application.ScreenUpdating = True
End Sub
Sub Prepare1b()
Dim LastRow As Long
Dim NbPlace1 As Integer, NbPlace2 As Integer
Dim FirstNb As Integer, LastNb As Integer
Dim I As Long, J As Long
Dim Place
Dim TEMP
Dim PlaceRef As String
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For I = LastRow To 3 Step -1
        Place = Cells(I, "C")
        NbPlace2 = Len(Place) - Len(Replace(Place, "-", ""))
        If (NbPlace2 > 0) Then
            NbPlace2 = InStr(Place, "-")
 '---------
            TEMP = Left(Place, NbPlace2 - 1)
            PlaceRef = Left(Place, NbPlace2 - 1)
            For J = 0 To 9
                PlaceRef = Replace(PlaceRef, J, "")     '  REMOVE  DIGITS
            Next J
            FirstNb = Replace(TEMP, PlaceRef, "") * 1
 '---------
            TEMP = Right(Place, Len(Place) - NbPlace2)
            LastNb = Replace(TEMP, PlaceRef, "") * 1
 '---------
            NbPlace2 = LastNb - FirstNb
            Rows(I + 1 & ":" & I + NbPlace2 - 1).Insert Shift:=xlDown
            For J = 1 To NbPlace2
                Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
                Cells(I + J, "C") = PlaceRef & FirstNb + J
            Next J
            Cells(I, "C") = PlaceRef & FirstNb
        End If
    Next I
    Application.ScreenUpdating = True
End Sub
Sub Prepare2()
Dim LastRow As Long, LastCol As Long
Dim I As Long, J As Long
Dim MyRg As Range
Dim F
Dim MyValue
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For I = LastRow To 2 Step -1
        Set MyRg = Range("C2:C" & I - 1)
        MyValue = Cells(I, "C")
        If (MyValue <> Empty) Then
            With MyRg
                Set F = .Find(What:=MyValue, After:=.Cells(1, 1), LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
                If (Not F Is Nothing) Then
                    LastCol = Cells(F.Row, Columns.Count).End(xlToLeft).Column
                    Range("D" & I).Resize(1, 2).Copy Destination:=Cells(F.Row, LastCol + 1)
                    Rows(I & ":" & I).Delete Shift:=xlUp
                End If
            End With
        End If
    Next I
    Application.ScreenUpdating = True
End Sub

Display More

jstar88 · Jun 6th 2011

Re: Comma delimited list and rows into columns

thanks PCI! works like a charm. thanks for all the help. can't express how much i appreciate it.

jstar88 · Jun 6th 2011

Re: Comma delimited list and rows into columns

by the way, how do you save your macro to a file so that it works on other computers, like if i want to use this on another computer

**PCI** · Jun 6th 2011

Re: Comma delimited list and rows into columns

As soon as you load the files with the macros, the macros are available for all files on the computer.
The macros prepared are working for the active sheet of the active file.
So just copy your file (withour data) in an other PC.

jstar88 · Jun 27th 2011

Re: Comma delimited list and rows into columns

hi PCI!
sry to bother you after so long but after running the awesome macro, i came across a problem:

For the place column within your attached Oz file: I get an error when I have something like U12_4-U12_7 as the place. Do you know I can adjust my macro so that it works out to:
U12_4
U12_5
U12_6
U12_7

thanks!

Comma delimited list and rows into columns

Participate now!

Share