Returning Multiple Values Through A Vlookup

A

aagold

New Member
Joined
Jan 6, 2010
Messages
16
I am trying to return multiple values associated with a single lookup ID. Please see following screen shot:
<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=187 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 27.75pt; mso-height-source: userset" height=37><TD class=xl69 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: white 1pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 27.75pt; BACKGROUND-COLOR: transparent" width=102 height=37>Workqueue ID #</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: white; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=85>Associated Rules</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right width=102 height=17>290</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right width=85>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right width=102 height=17>291</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=85></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right width=102 height=17>292</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=85></TD></TR></TBODY></TABLE>

The Associated Rules column is populated by looking up to a spreadsheet which looks like the following:
<TABLE style="WIDTH: 172pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=229 border=0><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 27.75pt; mso-height-source: userset" height=37><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 76pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 27.75pt; BACKGROUND-COLOR: transparent" width=101 height=37>Workqueue ID #</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Rule #</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>290</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>290</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>57</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>290</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>182</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>290</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>618</TD></TR></TBODY></TABLE>
Of course, vlookups only return the 1st value, but I need to capture all rules associated with each workqueue ID #. How might I accomplish this?

Thank you
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
aagold,

You can try a Funciton created by jindon.
http://www.mrexcel.com/forum/showthread.php?t=344561&highlight=muvlookup


Sample data (you may have to change the sheetnames):


Excel Workbook
AB
1Workqueue ID #Rule #
229012
329057
4290182
5290618
629118
7292333
829122
929277
10
Rule #





Your main sheet to find the "Rule #":


Excel Workbook
AB
1Workqueue ID #Associated Rules
2290618,182,57,12
329122,18
4292333,77
5
Workqueue ID #





The formula/function in cell B2 copied down:
=VLookUpMulti(A2,'Rule #'!$A$2:$B$100,2,",",FALSE)




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro/Function
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel


Code: 
Option Explicit
Function VLookUpMulti(ByVal strIndex As String, ByVal rng As Range, _
                 Optional ref As Integer = 1, Optional myJoin As String = " ", _
                 Optional myOrd As Boolean = True) As String
'
' jindon
' http://www.mrexcel.com/forum/showthread.php?t=344561&highlight=muvlookup
'
' =VLookUpMulti(A1, C1:D100, 2, ",", False)
'
Dim a, b(), i As Long, n As Long
a = rng.Value
ReDim b(1 To UBound(a, 1), 1 To 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 1)
        If a(i, 1) = strIndex Then
            If Not .exists(a(i, ref)) Then
                .Add a(i, 2), Nothing
                n = n + 1: b(n, 1) = a(i, 2)
                b(n, 2) = IIf(IsNumeric(a(i, 2)), a(i, 2), UCase(a(i, 2)))
            End If
        End If
    Next
End With
VSortM b, 1, n, 2, False
For i = 1 To n
    VLookUpMulti = VLookUpMulti & IIf(VLookUpMulti = "", "", myJoin) & b(i, 1)
Next
End Function
 
Sub VSortM(ary, LB, UB, ref, myOrd)
Dim i As Long, ii As Long, iii As Long, M, temp
i = UB: ii = LB
M = ary(Int((LB + UB) / 2), ref)
Do While ii <= i
    If myOrd Then
        Do While ary(ii, ref) < M: ii = ii + 1: Loop
        Do While ary(i, ref) > M: i = i - 1: Loop
    Else
        Do While ary(ii, ref) > M: ii = ii + 1: Loop
        Do While ary(i, ref) < M: i = i - 1: Loop
    End If
    If ii <= i Then
        For iii = LBound(ary, 2) To UBound(ary, 2)
            temp = ary(ii, iii): ary(ii, iii) = ary(i, iii): ary(i, iii) = temp
        Next
        i = i - 1: ii = ii + 1
    End If
Loop
If LB < i Then VSortM ary, LB, i, ref, myOrd
If ii < UB Then VSortM ary, ii, UB, ref, myOrd
End Sub


You may have to change the sheetname(s) for the Function.
 
Upvote 0
Hiker,

Thanks very much for your fast response. I'm having some trouble getting the formula to work properly.

To be clear, the elements of the vlookupmulti funtion are:
(lookup value, range, column index #, "seperator", false), correct?

Do I need to alter the VB in the function at all?

Thanks,
Aaron
 
Upvote 0
aagold,

To be clear, the elements of the vlookupmulti funtion are:
(lookup value, range, column index #, "seperator", false), correct?
Click to expand...

Correct, but the sheetname and range may be different


Do I need to alter the VB in the function at all?
Click to expand...

You should not.



What is the sheetname that contains the "Rule #"?

Can we have some screenshots?

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
 
Last edited:
Upvote 0
Here is what the sheet looks like that I would like to Look up from (entitled "WQ_Lookup"). I want to pull data from the "Rule # column).

WQ_Lookup

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 86px"><COL style="WIDTH: 441px"><COL style="WIDTH: 45px"><COL style="WIDTH: 398px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">WQ ID#</TD><TD style="FONT-WEIGHT: bold">Workqueue Name</TD><TD style="FONT-WEIGHT: bold">Rule #</TD><TD style="FONT-WEIGHT: bold">Rule Name</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">24</TD><TD>$0 CHARGE</TD><TD style="TEXT-ALIGN: right">26</TD><TD>$0 CHARGE - NOT LISTED IN FEE SCHEDULE</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">24</TD><TD>$0 CHARGE</TD><TD style="TEXT-ALIGN: right">318</TD><TD>HB CHARGE CONVERSION</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">190</TD><TD>$0 LATE CHARGE PROCESSING</TD><TD style="TEXT-ALIGN: right">182</TD><TD>ACCOUNT STATUS=BILLED</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">190</TD><TD>$0 LATE CHARGE PROCESSING</TD><TD style="TEXT-ALIGN: right">248</TD><TD>LATE CHARGES NET EFFECT $0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">147</TD><TD>15 DAY OUTSTANDING WQ</TD><TD style="TEXT-ALIGN: right">226</TD><TD>15 DAY OUTSTANDING WQ</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">503</TD><TD>ADMISSION DAY BILLING</TD><TD style="TEXT-ALIGN: right">490</TD><TD>ADMISSION DAY BILLING</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">84</TD><TD>ADT - COVERAGE UPDATES FOR OPEN/DNB ACCOUNTS</TD><TD style="TEXT-ALIGN: right">115</TD><TD>STOP BILL = COVERAGE UPDATE</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">84</TD><TD>ADT - COVERAGE UPDATES FOR OPEN/DNB ACCOUNTS</TD><TD style="TEXT-ALIGN: right">170</TD><TD>ACCOUNT STATUS=OPEN,DNB</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I would like the code to populate the "Associated Rules" column of this sheet (Entitled "POC 2 Build")
POC2 Build

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 113px"><COL style="WIDTH: 136px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 78px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Arial; BACKGROUND-COLOR: #666699; TEXT-ALIGN: center">ID #</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Arial; BACKGROUND-COLOR: #666699; TEXT-ALIGN: center">Associated Rules</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">290</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">291</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">292</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">610</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">167</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">98</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">599</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">289</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">605</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">606</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">70</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">77</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">78</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">236</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">79</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">80</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">81</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">240</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">237</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">238</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">239</TD><TD style="FONT-SIZE: 10pt"></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Thanks again.
 
Upvote 0
aagold,

Sample data before the macro:


Excel Workbook
ABCD
1WQ ID#Workqueue NameRule #Rule Name
2
324$0 CHARGE26$0 CHARGE - NOT LISTED IN FEE SCHEDULE
424$0 CHARGE318HB CHARGE CONVERSION
5
6190$0 LATE CHARGE PROCESSING182ACCOUNT STATUS=BILLED
7190$0 LATE CHARGE PROCESSING248LATE CHARGES NET EFFECT $0
8
914715 DAY OUTSTANDING WQ22615 DAY OUTSTANDING WQ
10
11503ADMISSION DAY BILLING490ADMISSION DAY BILLING
12
1384ADT - COVERAGE UPDATES FOR OPEN/DNB ACCOUNTS115STOP BILL = COVERAGE UPDATE
1484ADT - COVERAGE UPDATES FOR OPEN/DNB ACCOUNTS170ACCOUNT STATUS=OPEN,DNB
15
WQ_Lookup





Excel Workbook
FG
1ID #Associated Rules
2290
3291
4292
5610
6167
798
8599
9289
10605
11606
1270
1377
1478
15236
1679
1780
1881
19240
20237
21238
22239
23190
24
POC2 Build





After the macro:


Excel Workbook
FG
1ID #Associated Rules
2290
3291
4292
5610
6167
798
8599
9289
10605
11606
1270
1377
1478
15236
1679
1780
1881
19240
20237
21238
22239
23190182,248
24
POC2 Build





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub FindRuleNbr()
' hiker95, 20100107
Dim ws1 As Worksheet, ws2 As Worksheet, a As Long, IDNbr As Long
Dim c As Range, firstaddress As String, Hold As String
Set ws1 = Sheets("POC2 Build")
Set ws2 = Sheets("WQ_Lookup")
Application.ScreenUpdating = False
With ws1
  For a = 2 To .Cells(Rows.Count, 6).End(xlUp).Row Step 1
    Hold = ""
    IDNbr = .Cells(a, 6).Value
    With ws2.Columns(1)
      Set c = .Find(IDNbr, LookIn:=xlValues, LookAt:=xlWhole)
      If Not c Is Nothing Then
        firstaddress = c.Address
        Do
          Hold = Hold & c.Offset(, 2).Value & ","
          Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
      End If
    End With
    If Right(Hold, 1) = "," Then
      Hold = Left(Hold, Len(Hold) - 1)
      ws1.Cells(a, 7) = Hold
    End If
  Next a
End With
Application.ScreenUpdating = True
ws1.Select
End Sub


Then run the "FindRuleNbr" macro".
 
Upvote 0
Hi there,

This has been absolutely fantastic for me - and has really sorted out an issue I was having with a VLookUp that has multiple return values. However, I have hit a stumbling block and don't know where to begin.

In my sheet there is often more than one instance of a value to return (10 lots of 300, for example), and when that happens "#VALUE!" is returned - which I guess means that the duplicates are crashing the formula.

Is there a fix for this? It doesn't really matter whether it comes back with, say, "300" or "300,300,300,300" for my purposes.

Thanks,

Andrew
 
Upvote 0
phl1ag,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


I am going to assume that your data structure is not the same as aagold's.

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


After you create a NEW thread, send me a Private Message, with a link to this thread, and, with a link to your NEW thread, and, I will have a look.

Some things to consider with your NEW thread:

Can you post a screenshot of the actual raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
671
Micron
M

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top