Arrays and Table Filtering

[lrobbo314]

I have a fairly large dataset of 140,000 rows.

In columns B:E, I have Place, Serial #, Occurrences, and Date/Time.

There are 18 different places that repeat in different orders. A serial number will have a series of places that it shows up in, and each have a date/time value associated to them. Below is an example.

<style id="Lego-Tron 3000XL (PA 4)_12456_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1512456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6412456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:14.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:middle; border:.5pt solid windowtext; background:gray; mso-pattern:black none; white-space:normal;}.xl6512456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:14.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:middle; border:.5pt solid windowtext; background:gray; mso-pattern:black none; white-space:nowrap;}.xl6612456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6712456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; background:yellow; mso-pattern:black none; white-space:nowrap;}.xl6812456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; background:yellow; mso-pattern:black none; white-space:nowrap;}.xl6912456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:"General Date"; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; background:yellow; mso-pattern:black none; white-space:nowrap;}.xl7012456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl7112456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:"General Date"; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl7212456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; background:yellow; mso-pattern:black none; white-space:nowrap;}.xl7312456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; background:yellow; mso-pattern:black none; white-space:nowrap;}.xl7412456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:"General Date"; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; background:yellow; mso-pattern:black none; white-space:nowrap;}.xl7512456 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style></head>******><!--[if !excel]>  <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!-----------------------------><div id="Lego-Tron 3000XL (PA 4)_12456" align=center xublishsource="Excel"><table border=0 cellpadding=0 cellspacing=0 width=422 style='border-collapse: collapse;table-layout:fixed;width:317pt'> <col width=35 style='mso-width-source:userset;mso-width-alt:1280;width:26pt'> <col width=42 style='mso-width-source:userset;mso-width-alt:1536;width:32pt'> <col width=65 style='mso-width-source:userset;mso-width-alt:2377;width:49pt'> <col width=68 style='mso-width-source:userset;mso-width-alt:2486;width:51pt'> <col width=80 style='mso-width-source:userset;mso-width-alt:2925;width:60pt'> <col width=132 style='mso-width-source:userset;mso-width-alt:4827;width:99pt'> <tr height=20 style='height:15.0pt'> <td height=20 class=xl1512456 width=35 style='height:15.0pt;width:26pt'></td> <td class=xl7512456 width=42 style='width:32pt'>A</td> <td class=xl7512456 width=65 style='width:49pt'>B</td> <td class=xl7512456 width=68 style='width:51pt'>C</td> <td class=xl7512456 width=80 style='width:60pt'>D</td> <td class=xl7512456 width=132 style='width:99pt'>E</td> </tr> <tr height=25 style='height:18.75pt'> <td height=25 class=xl7512456 style='height:18.75pt'>1</td> <td class=xl6412456 width=42 style='width:32pt'>ID</td> <td class=xl6512456 style='border-left:none'>Place</td> <td class=xl6512456 style='border-left:none'>Serial</td> <td class=xl6512456 style='border-left:none'>pCount</td> <td class=xl6512456 style='border-left:none'>Elapsed Short</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>2</td> <td class=xl6712456 align=right style='border-top:none'>80</td> <td class=xl6712456 style='border-top:none;border-left:none'>Place 3</td> <td class=xl6812456 style='border-top:none;border-left:none'>12345</td> <td class=xl6712456 align=right style='border-top:none;border-left:none'>3</td> <td class=xl6912456 align=right style='border-top:none;border-left:none'>12/2/2016 14:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>3</td> <td class=xl6712456 align=right style='border-top:none'>895</td> <td class=xl6712456 style='border-top:none;border-left:none'>Place 1</td> <td class=xl6812456 style='border-top:none;border-left:none'>12345</td> <td class=xl6712456 align=right style='border-top:none;border-left:none'>3</td> <td class=xl6912456 align=right style='border-top:none;border-left:none'>12/2/2016 15:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>4</td> <td class=xl6712456 align=right style='border-top:none'>894</td> <td class=xl6712456 style='border-top:none;border-left:none'>Place 1</td> <td class=xl6812456 style='border-top:none;border-left:none'>12345</td> <td class=xl6712456 align=right style='border-top:none;border-left:none'>3</td> <td class=xl6912456 align=right style='border-top:none;border-left:none'>12/5/2016 8:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>5</td> <td class=xl6612456 align=right style='border-top:none'>641</td> <td class=xl6612456 style='border-top:none;border-left:none'>Place 2</td> <td class=xl7012456 style='border-top:none;border-left:none'>45678</td> <td class=xl6612456 align=right style='border-top:none;border-left:none'>4</td> <td class=xl7112456 align=right style='border-top:none;border-left:none'>12/1/2016 10:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>6</td> <td class=xl6612456 align=right style='border-top:none'>641</td> <td class=xl6612456 style='border-top:none;border-left:none'>Place 2</td> <td class=xl7012456 style='border-top:none;border-left:none'>45678</td> <td class=xl6612456 align=right style='border-top:none;border-left:none'>4</td> <td class=xl7112456 align=right style='border-top:none;border-left:none'>12/3/2016 7:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>7</td> <td class=xl6612456 align=right style='border-top:none'>641</td> <td class=xl6612456 style='border-top:none;border-left:none'>Place 2</td> <td class=xl7012456 style='border-top:none;border-left:none'>45678</td> <td class=xl6612456 align=right style='border-top:none;border-left:none'>4</td> <td class=xl7112456 align=right style='border-top:none;border-left:none'>12/3/2016 17:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>8</td> <td class=xl6612456 align=right style='border-top:none'>641</td> <td class=xl6612456 style='border-top:none;border-left:none'>Place 2</td> <td class=xl7012456 style='border-top:none;border-left:none'>45678</td> <td class=xl6612456 align=right style='border-top:none;border-left:none'>4</td> <td class=xl7112456 align=right style='border-top:none;border-left:none'>12/5/2016 8:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>9</td> <td class=xl7212456 align=right style='border-top:none'>652</td> <td class=xl7212456 style='border-top:none;border-left:none'>Place 2</td> <td class=xl7312456 style='border-top:none;border-left:none'>98765</td> <td class=xl7212456 align=right style='border-top:none;border-left:none'>7</td> <td class=xl7412456 align=right style='border-top:none;border-left:none'>12/1/2016 9:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>10</td> <td class=xl7212456 align=right style='border-top:none'>895</td> <td class=xl7212456 style='border-top:none;border-left:none'>Place 1</td> <td class=xl7312456 style='border-top:none;border-left:none'>98765</td> <td class=xl7212456 align=right style='border-top:none;border-left:none'>7</td> <td class=xl7412456 align=right style='border-top:none;border-left:none'>12/1/2016 11:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>11</td> <td class=xl7212456 align=right style='border-top:none'>893</td> <td class=xl7212456 style='border-top:none;border-left:none'>Place 1</td> <td class=xl7312456 style='border-top:none;border-left:none'>98765</td> <td class=xl7212456 align=right style='border-top:none;border-left:none'>7</td> <td class=xl7412456 align=right style='border-top:none;border-left:none'>12/1/2016 14:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>12</td> <td class=xl7212456 align=right style='border-top:none'>893</td> <td class=xl7212456 style='border-top:none;border-left:none'>Place 1</td> <td class=xl7312456 style='border-top:none;border-left:none'>98765</td> <td class=xl7212456 align=right style='border-top:none;border-left:none'>7</td> <td class=xl7412456 align=right style='border-top:none;border-left:none'>12/2/2016 8:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>13</td> <td class=xl7212456 align=right style='border-top:none'>650</td> <td class=xl7212456 style='border-top:none;border-left:none'>Place 4</td> <td class=xl7312456 style='border-top:none;border-left:none'>98765</td> <td class=xl7212456 align=right style='border-top:none;border-left:none'>7</td> <td class=xl7412456 align=right style='border-top:none;border-left:none'>12/3/2016 15:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>14</td> <td class=xl7212456 align=right style='border-top:none'>895</td> <td class=xl7212456 style='border-top:none;border-left:none'>Place 1</td> <td class=xl7312456 style='border-top:none;border-left:none'>98765</td> <td class=xl7212456 align=right style='border-top:none;border-left:none'>7</td> <td class=xl7412456 align=right style='border-top:none;border-left:none'>12/4/2016 11:00</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl7512456 style='height:15.0pt'>15</td> <td class=xl7212456 align=right style='border-top:none'>895</td> <td class=xl7212456 style='border-top:none;border-left:none'>Place 1</td> <td class=xl7312456 style='border-top:none;border-left:none'>98765</td> <td class=xl7212456 align=right style='border-top:none;border-left:none'>7</td> <td class=xl7412456 align=right style='border-top:none;border-left:none'>12/5/2016 10:00</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=35 style='width:26pt'></td> <td width=42 style='width:32pt'></td> <td width=65 style='width:49pt'></td> <td width=68 style='width:51pt'></td> <td width=80 style='width:60pt'></td> <td width=132 style='width:99pt'></td> </tr> <![endif]></table></div><!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></body></html>
<style id="Lego-Tron 3000XL (PA 4)_12475_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1512475 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6412475 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style></head>******><!--[if !excel]>  <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!-----------------------------><div id="Lego-Tron 3000XL (PA 4)_12475" align=center xublishsource="Excel"><table border=0 cellpadding=0 cellspacing=0 width=156 style='border-collapse: collapse;table-layout:fixed;width:118pt'> <col width=78 span=2 style='mso-width-source:userset;mso-width-alt:2852; width:59pt'> <tr height=20 style='height:15.0pt'> <td colspan=2 height=20 class=xl6412475 width=156 style='height:15.0pt; width:118pt'>Formulas</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=78 style='width:59pt'></td> <td width=78 style='width:59pt'></td> </tr> <![endif]></table></div><!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></body></html>

I also have a list, from code I wrote, which gives me a unique list of every unique pattern of places and times. So, for the first one, serial #12345, the pattern would be; Place 3 12/2/2016 14:00, Place 1 12/2/2016 15:00, Place 1 12/5/2016 08:00.

I have 52,000 of these patterns.

So to run 52,000 patterns against 140,000 records, my code has to loop 7,280,000,000 times. After testing small portions, I estimate that it will take 2.5 hours to run everything and I'm trying to get that time down.

The code below. I know it's kind of a mess, but I've tried to comment it as well as possible.

I've tried filtering the data and copying it to temporary sheets, so that I don't need to iterate through all 140,000 rows for each pattern. But, it seems like just going through the entire 140,000 in an array is still the fastest. I feel like there must be some way to speed this up more.

Sub test2()
On Error GoTo erTrap
Dim ar()    As Variant
Dim nAR()   As Variant
Dim AP()    As Variant
Dim PO()    As String
Dim AO()    As Variant
Dim FA()    As Variant
Dim DA()    As Single
Dim LR      As Long
Dim tLR     As Long
Dim i       As Long
Dim a       As Long
Dim c       As Long
Dim cnt     As Long
Dim Cyc     As Integer
Dim pCyc    As Integer
Dim x       As Integer
Dim pos     As Integer
Dim FI      As Integer
Dim tmp     As String
Dim r       As Range
Dim rTmp    As Range
Dim wsIn    As Worksheet
Dim wsOut   As Worksheet
Dim wsCon   As Worksheet
Dim wsTmp   As Worksheet
Dim wsTmp2  As Worksheet
Dim b       As Boolean
Dim s, e
s = Timer
sOff
b = True
Set wsIn = Sheets("PatternIn")
Set wsOut = Sheets("PatternOut")
Set wsCon = Sheets("Convoy")
Set wsTmp = Sheets("tmp")
Set wsTmp2 = Sheets("tmp2")
Set r = wsIn.Range("A1").CurrentRegion
LR = wsIn.Range("A" & Rows.Count).End(xlUp).Row()
cnt = 0
AP() = wsOut.Range("A2", wsOut.Range("A" & Rows.Count).End(xlUp)).Value 'Fill array with all the 52,000 patterns
ar() = wsIn.Range("B2", wsIn.Range("E" & Rows.Count).End(xlUp)) 'Fill array with data as posted above in this post
For a = 1 To UBound(AP()) 'Cycle through each pattern
    PO = Split(AP(a, 1), ",") 'Patterns are delimited, this splits it into an array to work with.
    
        For i = 1 To UBound(ar()) 'Cycle through Each Serial#
            Cyc = ar(i, 3) 'This number is the pCount number in the table above
            pCyc = UBound(PO()) 'Number of places in the pattern
                If pCyc <= Cyc Then
                    'For j = 1 To Cyc - 2 'Cycle groups from 3 to Max Count
                        For x = 0 To Cyc - 2 - 1 'Cycle through group iterations
                        
                            For pos = 0 To pCyc 'Cycle to add values to string
                                If ar(i + x + pos, 1) = Left(PO(pos), InStr(PO(pos), "=") - 1) _
                                And (ar(i + x + pos, 4) >= CDate(Right(PO(pos), Len(PO(pos)) - InStr(PO(pos), "=") - 1)) - (1 / 24) _
                                And ar(i + x + pos, 4) <= CDate(Right(PO(pos), Len(PO(pos)) - InStr(PO(pos), "=") - 1)) + (1 / 24)) Then 'check if places and times correspond in each array
                                Else
                                    b = False
                                    GoTo Boot
                                End If
                            Next pos
                            
                            If b = True Then
                                tmp = ar(i, 2) & " : " & AP(a, 1)
                                cnt = cnt + 1
                                ReDim Preserve nAR(1 To cnt)
                                nAR(cnt) = tmp 'This will be the output of the code
                            End If
                            
                            tmp = vbNullString
                            b = True
                            
                        Next x
                    'Next j
                End If
Boot:
            b = True
            tmp = vbNullString
            i = i + Cyc - 1
        Next i
    
    If a Mod 100 = 0 Then
        e = Timer
        Debug.Print e - s
        'Debug.Print a
        s = Timer
    End If
Next a
Set r = wsCon.Range("A2")
Set r = r.Resize(UBound(nAR), 1)
For i = 1 To r.Cells.Count
    r.Cells(i, 1) = nAR(i)
Next i

e = Timer
Debug.Print e - s
sOn
Exit Sub

 

[MickG]

I'm not quite sure what you want to Return , but the data example below reflect your columns "C & E"
This actual test data continuous for 200,000 rows
Using the code below the Unique numbers in "A" and there related Dates in "C" where returned to Column "F & G" in 2.66 Secs.
Perhaps you could use something similar ???

[COLOR="Navy"]Sub[/COLOR] MG08Dec21
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Q [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] t
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
t = Timer
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray)
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Ray(n, 1)) [COLOR="Navy"]Then[/COLOR]
        ReDim nRay(1 To 1)
        nRay(1) = Ray(n, 3)
    Dic.Add Ray(n, 1), nRay
[COLOR="Navy"]Else[/COLOR]
    Q = Dic(Ray(n, 1))
    ReDim Preserve Q(1 To UBound(Q) + 1)
        Q(UBound(Q)) = Ray(n, 3)
    Dic(Ray(n, 1)) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
    c = c + 1
    Cells(c, "F") = K
    Cells(c, "G").Resize(, UBound(Dic(K))) = Dic(K)
[COLOR="Navy"]Next[/COLOR] K
MsgBox Timer - t
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

	A	B	C
1	123		01/01/2016
2	123		02/01/2016
3	123		03/01/2016
4	123		04/01/2016
5	124		05/01/2016
6	124		06/01/2016
7	124		07/01/2016
8	124		08/01/2016
9	124		09/01/2016
10	125		10/01/2016
11	125		11/01/2016
12	125		12/01/2016
13	125		13/01/2016
14	125		14/01/2016
15	126		15/01/2016
16	126		16/01/2016
17	126		17/01/2016
18	126		18/01/2016
19	126		19/01/2016

<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="64" style="width: 48pt;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;" span="2"> <tbody> </tbody>

 

[lrobbo314]

Thanks for the reply @MickG. I wasn't super clear on what I wanted the output to be. But I was able to get a solution that ended up being completely array based.

Thanks for the input.

 

[MickG]

You're welcome