Parse delimited cells, use results in lookup, concatenate

porear · Oct 7, 2011

Sorry this is a compound problem but I am not sure how to solve each step with a method producing an output compatible with input types for the next piece.

I need to parse comma delimited numbers (stored as text) in cells like those in col C below. I then need to look up each of the parsed numbers against col A, retrieve the corresponding data from col B, and return all results in column E (separated by carriage returns). All data is text format.

I know I can read in the delimited data with Split, but not sure how to proceed with the lookup from there, as I don't know how (or if it is possible) to then pass that array to a VLOOKUP function. Maybe that is the wrong approach anyway...

Any help is appreciated, thanks in advance.

Excel 2010
<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>ID</TD><TD>Desc</TD><TD>Cross Ref</TD><TD style="TEXT-ALIGN: right"></TD><TD>Result</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>42</TD><TD>Data1</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>54</TD><TD>Data2</TD><TD>1,42</TD><TD style="TEXT-ALIGN: right"></TD><TD>Data4
Data1

</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>777</TD><TD>Data3</TD><TD>42,54,83</TD><TD style="TEXT-ALIGN: right"></TD><TD>Data1
Data2
Data5

</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>1</TD><TD>Data4</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>83</TD><TD>Data5</TD><TD>54</TD><TD style="TEXT-ALIGN: right"></TD><TD>Data2</TD></TR></TBODY></TABLE>

Sheet1

GTO · Oct 7, 2011

You mention Split, so we are okay for vba, right?

Peter_SSs · Oct 7, 2011

Welcome to the MrExcel board!

I've assumed the answer to GTO's question is "Yes" so this is my effort.
Test in a copy of your workbook.

 Sub GetResult() Dim bits Dim lr As Long, j As Long Dim s As String, t As String Dim c As Range, found As Range lr = Range("A" & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False For Each c In Range("C2:C" & lr) s = c.Value If Len(s) > 0 Then t = "" bits = Split(s, ",") For j = 0 To UBound(bits) Set found = Range("A1:A" & lr).Find(What:=bits(j), _ LookIn:=xlValues, LookAt:=xlWhole, SearchFormat:=False) If found Is Nothing Then t = t & Chr(10) & "N/A" Else t = t & Chr(10) & found.Offset(, 1).Value End If Next j c.Offset(, 2).Value = Replace(t, Chr(10), "", 1, 1) End If Next c Application.ScreenUpdating = True End Sub

GTO · Oct 7, 2011

Hi Peter

and an overdue Howdy from Arizona!

Here's my shot porear. Pretty similar, I just used MATCH instead of .Find (which I believe would be slower if the range is long).

When I copied your example, Excel plunked the IDs as numbers. If they are text in yours (which you courteously advised), I think you would need to ditch the CLng()'s in mine.

Rich (BB code):

Option Explicit
    
Sub exa()
Dim Cell        As Range
Dim rngDesc     As Range
Dim arySplit    As Variant
Dim strTemp     As String
Dim n           As Long
    
    '// Set a reference to your range somehow... //
    Set rngDesc = ActiveSheet.Range("B2:B11")
    
    For Each Cell In rngDesc.Cells
        '// In case empty description or no IDs to look for //
        If Not Cell.Value = vbNullString And Not Cell.Offset(, 1).Value = vbNullString Then
            arySplit = Empty
            '// If we find a comma                          //
            If InStr(1, Cell.Offset(, 1).Value, ",") > 0 Then
                arySplit = Split(Cell.Offset(, 1).Value, ",")
                strTemp = vbNullString
                For n = LBound(arySplit, 1) To UBound(arySplit)
                    '// See if MATCH finds the ID           //
                    If Not IsError(Application.Match(CLng(arySplit(n)), rngDesc.Offset(, -1), 0)) Then
                        strTemp = strTemp & rngDesc.Rows(Application.Match(CLng(arySplit(n)), rngDesc.Offset(, -1), 0)).Value & vbLf
                    End If
                Next
                '// Trim off the last linefeed              //
                strTemp = Left(strTemp, Len(strTemp) - 1)
            Else
                '// In case we're jsut looking for one ID   //
                arySplit = Cell.Offset(, 1).Value
                If Not IsError(Application.Match(CLng(arySplit), rngDesc.Offset(, -1), 0)) Then
                    strTemp = rngDesc.Rows(Application.Match(CLng(arySplit), rngDesc.Offset(, -1), 0)).Value
                End If
            End If
            Cell.Offset(, 3).Value = strTemp
        End If
    Next
End Sub

porear · Oct 8, 2011

Wow thanks very much for the rapid responses. Yes, VBA is definitely fair game. I will give these a look and report back, I really appreciate the help.

porear · Oct 8, 2011

Both of these work great! If I have an issue with the input data being text versus a number I can modify accordingly.

The help is very much appreciated, this has been a wonderful first experience on the forum. Thank you both.

GTO · Oct 8, 2011

porear said:
... this has been a wonderful first experience on the forum. Thank you both.

ACK! My apologies, I don't believe either of us spotted that. A hearty welcome to the forum

I would use Peter's code, it seems better to me. .Find really looks at text, so all bases should be covered there, and mine was longer than necessary, as for whatever dopey reason, I spaced that Split would work even if no delimiter is found.

PS - my wording was less than clear in the prior post. I meant to say that MATCH would be slower in a longer range.

Rick Rothstein · Oct 8, 2011

Here is another macro for you to consider with uses a completely different approach than has already been presented...

Code:

Sub FindData()
  Dim X As Long, Z As Long, LastRow As Long
  Dim Data As String, Numbers() As String
  Dim CellVal As Variant, Coll As New Collection
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  CellVal = Range("A" & StartRow & ":B" & LastRow)
  For X = 1 To LastRow - StartRow + 1
    Coll.Add CellVal(X, 2), CStr(CellVal(X, 1))
  Next
  Application.ScreenUpdating = False
  For X = StartRow To LastRow
    If Len(Cells(X, "C").Value) Then
      Numbers = Split(Cells(X, "C"), ",")
      For Z = 0 To UBound(Numbers)
        Data = Data & vbLf & Coll(Numbers(Z))
      Next
      Cells(X, "E") = Mid(Data, 2)
      Data = ""
    End If
  Next
  Application.ScreenUpdating = True
End Sub

Rick Rothstein · Oct 8, 2011

The macro I posted earlier should execute somewhat quickly; this modification to it should make it a little bit quicker yet...

Code:

Sub FindData()
  Dim X As Long, Z As Long, LastRow As Long, Numbers() As String
  Dim CellVal As Variant, Coll As New Collection
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  CellVal = Range("A" & StartRow & ":B" & LastRow)
  For X = 1 To LastRow - StartRow + 1
    Coll.Add CellVal(X, 2), CStr(CellVal(X, 1))
  Next
  ReDim CellVal(1 To LastRow - StartRow + 1, 1 To 1)
  For X = StartRow To LastRow
    If Len(Cells(X, "C").Value) Then
      Numbers = Split(Cells(X, "C"), ",")
      For Z = 0 To UBound(Numbers)
        CellVal(X - StartRow + 1, 1) = CellVal(X - StartRow + 1, 1) & vbLf & Coll(Numbers(Z))
      Next
      CellVal(X - StartRow + 1, 1) = Mid(CellVal(X - StartRow + 1, 1), 2)
    End If
  Next
  Application.ScreenUpdating = False
  Range("E" & StartRow & ":E" & LastRow) = CellVal
  Application.ScreenUpdating = True
End Sub

Peter_SSs · Oct 8, 2011

GTO said:
ACK! My apologies, I don't believe either of us spotted that. A hearty welcome to the forum

It was already covered.

Peter_SSs said:
Welcome to the MrExcel board!

Parse delimited cells, use results in lookup, concatenate

porear

New Member

GTO

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

GTO

MrExcel MVP

porear

New Member

porear

New Member

GTO

MrExcel MVP

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Share this page

Parse delimited cells, use results in lookup, concatenate

New Member

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

New Member

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP, Moderator

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock