VBA: Help extracting multiple sub-strings

[sjinvestigator]

Hi all,

Hope you're doing well today. I've got a tricky problem involving parsing sub-strings that I've been struggling with for several days. I would be REALLY grateful if some kind soul could please help me get (re-)started or give me some guidance on how to approach this problem. I'd love even some simple pointers in the right direction as most of what I've tried has not worked well. I'm beginning to think this is impossible.

In a nutshell, I'm trying to write some VBA code that will loop through a column of cells that have text data similar to THIS:

* Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781) (CB28412)
* Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27) (CB28412)
* Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)

What I need to do is extract substrings that list the different types of margarine and their corresponding scancodes (CBxxxx). I want to just paste the results into another sheet.

So, for example, the result of processing the above would be a row that shows "CB28412" in Column A and "Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781), Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27)" in column B.

Then the next row would have "CB82474" in column A and "Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)" in column B.

Please see attached images and mini-sheets of an example of the data I am working with and expected results I want to get after running the macro on the Result tab.

On the plus side, each list of types of margarine starts with a star (*) and each scancode is in parenthesis at the end of the list of margarines. That's how I have been trying to identify each part of the substring. One the negative side, though, sometimes multiple lists of margarines apply to the same scancode and most entries have more than one scancode/list combo. I also sometimes find an extraneous line of text from our inventory system in between the lists of margarines of scancodes that need to be ignored. I only want to process the parts of the string that corresponds to lists starting with * and ending with a (CBxxxx) number

I've tried to make this as clear and representative of the data I'm working with as humanly possible without being too long or complex. I hope it's clear what I'm trying to do, but if you need any further information, please just let me know.

I want to offer my sincere thanks, in advance, to anyone who takes the time to look at this problem.

Thanks very much!!!!!

SJ

Mini sheet data sample:

Scancode_Example.xlsx
	A	B
1	Month	Scancodes & Type
2	January	* Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781) (CB28412) * Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27) (CB28412) * Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)
3	February	* Margarine Wholesale (11), Margarine Retail (27), Margarine Retail (14), Margarine Bulk (15) (CB91254) * Margarine Wholesale (6), Margarine Retail (399), Margarine Retail (400), Margarine Bulk (51) (CB49185)
4	March	* Margarine Wholesale (1008), Margarine Retail (1009), Margarine Retail (1010), Margarine Bulk (1011) (CB11888) * Margarine Wholesale (227), Margarine Retail (228), Margarine Retail (229), Margarine Bulk (230) (CB11888) extraneous text sometimes present in my data * Margarine Wholesale (583), Margarine Retail (622), Margarine Retail (902), Margarine Bulk (114) (CB31122) more extraneous text * Margarine Wholesale (501), Margarine Retail (601), Margarine Retail (999), Margarine Bulk (293) (CB82474) * Margarine Wholesale (517), Margarine Retail (683), Margarine Retail (872), Margarine Bulk (336) (CB82474)
Data

Min sheet results sample:

Scancode_Example.xlsx
	A	B
1	Scancode	Applies To
2	CB28412	Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781), Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27)
3	CB82474	Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814)
4	CB91254	Margarine Wholesale (11), Margarine Retail (27), Margarine Retail (14), Margarine Bulk (15)
5	CB11888	Margarine Wholesale (1008), Margarine Retail (1009), Margarine Retail (1010), Margarine Bulk (1011), Margarine Wholesale (227), Margarine Retail (228), Margarine Retail (229), Margarine Bulk (230)
6	CB31122	Margarine Wholesale (583), Margarine Retail (622), Margarine Retail (902), Margarine Bulk (114)
7	CB82474	Margarine Wholesale (501), Margarine Retail (601), Margarine Retail (999), Margarine Bulk (293), Margarine Wholesale (517), Margarine Retail (683), Margarine Retail (872), Margarine Bulk (336)
Result

 

[DanteAmor]

Welcome to the MrExcel Message Board!

With the data shown, the following macro obtains the expected result.
The data in the "Data" sheet, results in the "Result" sheet

Sub extracting_multiple_substrings()
  Dim a As Variant, b As Variant, sText As Variant, ky As Variant
  Dim i As Long, j As Long, n As Long
  Dim num As String, cad As String
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Data").Range("B2", Sheets("Data").Range("B" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a) * 100, 1 To 2)
  
  For i = 1 To UBound(a)
    Set dic = CreateObject("Scripting.Dictionary")
    For Each sText In Split(a(i, 1), Chr(10))
      If Left(sText, 1) = "*" Then
        n = InStrRev(sText, "(CB", , vbTextCompare)
        num = Replace(Mid(sText, n + 1, 99), ")", "")
        cad = Mid(sText, 3, n - 4)
        If Not dic.exists(num) Then
          dic(num) = cad
        Else
          dic(num) = dic(num) & ", " & cad
        End If
      End If
    Next
    
    For Each ky In dic.keys
      j = j + 1
      b(j, 1) = ky
      b(j, 2) = dic(ky)
    Next
    
  Next
  
  Sheets("Result").Range("A2").Resize(j, 2).Value = b
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (extracting_multiple_substrings) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.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.

 

[sjinvestigator]

Wow, thank you SO MUCH for your help!!! This is really great! I tried it on several of my datasets and it worked perfectly on almost all of them. This has gotten me 99% of the way there. However, I did have problems with the macro when I ran it on my files that have REALLY long text strings and some carriage returns. Most of them are not this bad, but I do have some where it chokes. The error is "Invalid procedure call or argument " on cad = Mid(sText, 3, n - 4). Do you have any advice on how I could tweak your excellent macro to be able to handle the ones with huge strings with the carriage returns?

Here's an extreme example of what I'm talking about, new row 4 updated minisheet data tab.

FirstReply.xlsm
	A	B
1	Month	Scancodes & Type
2	January	* Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781) (CB28412) * Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27) (CB28412) * Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)
3	February	* Margarine Wholesale (11), Margarine Retail (27), Margarine Retail (14), Margarine Bulk (15) (CB91254) * Margarine Wholesale (6), Margarine Retail (399), Margarine Retail (400), Margarine Bulk (51) (CB49185)
4	March	* Margarine Wholesale (1008), Margarine Retail (1009), Margarine Retail (1010), Margarine Bulk (1011) (CB11888) * Margarine Wholesale (227), Margarine Retail (228), Margarine Retail (229), Margarine Bulk (230) (CB11888) extraneous text sometimes present in my data * Margarine Wholesale (583), Margarine Retail (622), Margarine Retail (902), Margarine Bulk (114) (CB31122) more extraneous text * Margarine Wholesale (501), Margarine Retail (601), Margarine Retail (999), Margarine Bulk (293) (CB82474) * Margarine Wholesale (517), Margarine Retail (683), Margarine Retail (872), Margarine Bulk (336) (CB82474)
5	April	* Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 21H1 for 421-based Inventory (CB5004444) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 21H1 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 20H2 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 WLK Bulk 2004 (2004), Margarine 10 FARUN Retail Bulk 2004 (2004), Margarine 10 FARUN Bulk 2004 (2004), Margarine 10 Wholesale Bulk 2004 (2004) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 2004 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine Triapod 2004 (800), Margarine Triapod Standard Bulk 2004 (800), Margarine Triapod Wholesaler Bulk 2004 (800) 2021-07 Scancode Bulletin for Margarine Triapod, Revis 2004 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2004 (800), Margarine 10 Retail Bulk 2004 (800), Margarine 10 Retail Bulk 2004 (800), Margarine 10 WLK Bulk 2004 (800), Margarine 10 FARUN Retail Bulk 2004 (800), Margarine 10 FARUN Bulk 2004 (800), Margarine 10 Wholesale Bulk 2004 (800) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 2004 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 WLK Bulk 2004 (2004), Margarine 10 FARUN Retail Bulk 2004 (2004), Margarine 10 FARUN Bulk 2004 (2004), Margarine 10 Wholesale Bulk 2004 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 2004 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 21H1 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 20H2 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine Triapod 2009 (800), Margarine Triapod Standard Bulk 2009 (800), Margarine Triapod Wholesaler Bulk 2009 (800) 2021-07 Scancode Bulletin for Margarine Triapod, Revis 20H2 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) 2021-07 Scancode Bulletin for Margarine 10 Revis 21H1 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004) 2021-07 Extracted Scancode Bulletin for Margarine 10 Revis 20H2 for 421-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) 2021-07 Scancode Bulletin for Margarine 10 Revis 20H2 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 2004 (800), Margarine 10 Retail Bulk 2004 (800), Margarine 10 Retail Bulk 2004 (800), Margarine 10 WLK Bulk 2004 (800), Margarine 10 FARUN Retail Bulk 2004 (800), Margarine 10 FARUN Bulk 2004 (800), Margarine 10 Wholesale Bulk 2004 (800) 2021-07 Scancode Bulletin for Margarine 10 Revis 2004 for 281-based Inventory (CB5004237) Extracted from redacted inventory system: https://scancode.redacted.com/5004237 * Margarine 10 1607 (2004), Margarine 10 Retail Bulk 1607 (2004), Margarine 10 Retail Bulk 1607 (2004), Margarine 10 WLK Bulk 1607 (2004), Margarine 10 FARUN Retail Bulk 1607 (2004), Margarine 10 FARUN Bulk 1607 (2004), Margarine 10 Wholesale Bulk 1607 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 1607 for 421-based Inventory (CB5004238) Extracted from redacted inventory system: https://scancode.redacted.com/5004238 * Margarine Triapod 2016 1607 (800), Margarine Triapod 2016 Essentials Bulk 1607 (800), Margarine Triapod 2016 Standard Bulk 1607 (800), Margarine Triapod 2016 Wholesaler Bulk 1607 (800), Margarine Triapod 2016 MultiPoint Premium Bulk 1607 (800), Margarine Storage Triapod 2016 1607 (800) 2021-07 Scancode Bulletin for Margarine Triapod 2016 for 281-based Inventory (CB5004238) Extracted from redacted inventory system: https://scancode.redacted.com/5004238 * Margarine 10 1607 (800), Margarine 10 Retail Bulk 1607 (800), Margarine 10 Retail Bulk 1607 (800), Margarine 10 WLK Bulk 1607 (800), Margarine 10 FARUN Retail Bulk 1607 (800), Margarine 10 FARUN Bulk 1607 (800), Margarine 10 Wholesale Bulk 1607 (800) 2021-07 Scancode Bulletin for Margarine 10 Revis 1607 for 281-based Inventory (CB5004238) Extracted from redacted inventory system: https://scancode.redacted.com/5004238 * Margarine Triapod 2019 1809 (800), Margarine Triapod 2019 Essentials Bulk 1809 (800), Margarine Triapod 2019 Standard Bulk 1809 (800), Margarine Triapod 2019 Wholesaler Bulk 1809 (800) 2021-07 Scancode Bulletin for Margarine Triapod 2019 for 281-based Inventory (CB5004244) Extracted from redacted inventory system: https://scancode.redacted.com/5004244 * Margarine 10 1809 (2004), Margarine 10 Retail Bulk 1809 (2004), Margarine 10 Retail Bulk 1809 (2004), Margarine 10 WLK Bulk 1809 (2004), Margarine 10 FARUN Retail Bulk 1809 (2004), Margarine 10 FARUN Bulk 1809 (2004), Margarine 10 Wholesale Bulk 1809 (2004) 2021-07 Scancode Bulletin for Margarine 10 Revis 1809 for 421-based Inventory (CB5004244) Extracted from redacted inventory system: https://scancode.redacted.com/5004244 * Margarin
Data

Of course, the output on the Result tab for those would be...
A2: CB5004444
B2: Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004)

A3: CB5004237
A3: Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800) (CB5004237)

A4: CB5004237
B4: Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004)

A5: CB5004237
B5: Margarine 10 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 WLK Bulk 2004 (2004), Margarine 10 FARUN Retail Bulk 2004 (2004), Margarine 10 FARUN Bulk 2004 (2004), Margarine 10 Wholesale Bulk 2004 (2004)
....etc....

Thanks again, I sincerely, sincerely appreciate the advice!! Very grateful.

SJ

 

[sjinvestigator]

Please note that I have also posted this same question in another forum at the link below:

Tricky problem parsing multiple substrings (VBA) [SOLVED]

Hi all, Hope you're doing well today. I've got a tricky problem involving parsing sub-strings that I've been struggling with for several days. I would be REALLY grateful if some kind soul could please help me get (re-)started or give me some guidance on how to approach this problem. I'd...

www.excelforum.com

I've re-read the guidelines and understand this should always be disclosed now. My sincere apologies.

SJ

 

[DanteAmor]

Of course, the output on the Result tab for those would be...
A3: CB5004237
A3: Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800)

A4: CB5004237
B4: Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004)

So, for example, the result of processing the above would be a row that shows "CB28412" in Column A and "Margarine Wholesale (27), Margarine Retail (241), Margarine Retail (287), Margarine Bulk (1781), Margarine Wholesale (281), Margarine Retail (42), Margarine Retail (13), Margarine Bulk (27)" in column B.

Then the next row would have "CB82474" in column A and "Margarine Wholesale (19), Margarine Retail (281), Margarine Retail (411), Margarine Bulk (3814) (CB82474)" in column B.

In fact, following your initial rule, the result of the CB5004237 code should have all the lines of that same code concatenated in a single cell:
A3: CB5004237
B3: Margarine 10 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 Retail Bulk 2009 (800), Margarine 10 WLK Bulk 2009 (800), Margarine 10 FARUN Retail Bulk 2009 (800), Margarine 10 FARUN Bulk 2009 (800), Margarine 10 Wholesale Bulk 2009 (800), Margarine 10 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 Retail Bulk 2009 (2004), Margarine 10 WLK Bulk 2009 (2004), Margarine 10 FARUN Retail Bulk 2009 (2004), Margarine 10 FARUN Bulk 2009 (2004), Margarine 10 Wholesale Bulk 2009 (2004), Margarine 10 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 Retail Bulk 2004 (2004), Margarine 10 WLK Bulk 2004 (2004), Margarine 10 FARUN Retail Bulk 2004 (2004), Margarine 10 FARUN Bulk 2004 (2004), Margarine 10 Wholesale Bulk 2004 (2004), Margarine Triapod 2004 (800)
etc...

Try this:

Sub extracting_multiple_substrings()
  Dim a As Variant, b As Variant, sText As Variant, ky As Variant
  Dim i As Long, j As Long, k As Long, n As Long
  Dim num As String, cad As String
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Data").Range("B2", Sheets("Data").Range("B" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a) * 100, 1 To 2)
  
  For i = 1 To UBound(a)
    Set dic = CreateObject("Scripting.Dictionary")
    sText = Split(Replace(a(i, 1), "*", "###"), "###")
    For k = 0 To UBound(sText)
      If InStr(1, sText(k), "(CB") > 0 Then
        n = InStrRev(sText(k), "(CB", , vbTextCompare)
        num = Trim(Left(Replace(Mid(sText(k), n + 1, 99), ")", Space(99)), 90))
        cad = Trim(Mid(sText(k), 1, n - 2))
        If InStr(1, cad, Chr(10)) > 0 Then
          cad = Split(cad, Chr(10))(0)
        End If
        If Not dic.exists(num) Then
          dic(num) = cad
        Else
          dic(num) = dic(num) & ", " & cad
        End If
      End If
    Next
    
    For Each ky In dic.keys
      j = j + 1
      b(j, 1) = ky
      b(j, 2) = dic(ky)
    Next
    
  Next
  
  Sheets("Result").Range("A2").Resize(j, 2).Value = b
End Sub

 

[sjinvestigator]

Dante,

Once again, I want to thank you from the bottom of my heart. This updated code worked PERFECTLY with even the largest, craziest data that I ran it through. In fact, I processed all of my files and it succeeded 100% on every single one. This was WONDERFUL! I wish I could buy you a drink in thanks, you have truly helped me. Combined with the other problem that you also kindly helped me with, I'm going to avoid hundreds of hours of tedious manual work over time. I am sincerely grateful to you. I worked long on this problem before finally posting for help, and had almost given up hope that it was solvable. I've read your code a dozen times now, but plan to keep studying it tomorrow to better understand this elegant solution. I appreciate the learning opportunity, as well as the practical help.

Thank you, thank you, ¡muchísimas gracias!

SJ

P.S. You're quite right about the expected output, I think I typed that bit too hastily, but what you've done is exactly what I was aiming for.

 

[DanteAmor]

Nice to hear that this works for you. Thanks for your kind words.
And of course I accept the virtual drink ?, with your thanks I am more than paid.