Vlookup with Multiple Results in same cell?

[mick0005]

I am trying to take data input like this:

With a formula to output it like this:

Is this possible??

THANKS! This would save hours of work every week.

 

[mick0005]

Ok, so I was able to easily use a concat function to create the "Coop1(5)" part... now I just need a formula to then reference the DC number to output the multiple results for each concat coop number and restaurant count for that respective DC (in the same cell, with a semi colon inbetween the results)

Please help! Thanks

 

[Armando Montes]

You might try this UDF

Function MyLookup(LookFor, FindCol As Range, GetCol As Range) As String
For Each c In FindCol
    If LookFor = c Then
        If MyLookup = "" Then
            MyLookup = Cells(c.Row, GetCol.Column)
        Else
            MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
        End If
    End If
Next c
End Function

 

[mick0005]

You might try this UDF

Function MyLookup(LookFor, FindCol As Range, GetCol As Range) As String
For Each c In FindCol
    If LookFor = c Then
        If MyLookup = "" Then
            MyLookup = Cells(c.Row, GetCol.Column)
        Else
            MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
        End If
    End If
Next c
End Function

Armando, I am not sure I know what to do with that? Is that a macro or a formula?

 

[mick0005]

Armando, I am not sure I know what to do with that? Is that a macro or a formula?

Alright so I was able to get as far as finding out that was a user defined function and I copied it verbatim into a VBA module. Do I need to edit that function at all?

I ran it once but I don't know what to put in the LookFor, FindCol, and GetCol argument fields.

 

[Armando Montes]

Based on your sample, try somethink like this
=MyLookup(U7,U:U,V:V)

 

[Marcelo Branco]

Hi,

Maybe this

Copy the function below to a standard module

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

A G
<TABLE style="WIDTH: 204pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=272><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=64>DC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 156pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=208>Market</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Coop1(5); Coop9(30); Coop7(19)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Coop1(20); Coop5(60)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Coop2(30)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Coop3(45)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Coop4(55)</TD></TR></TBODY></TABLE>


formula in G2
=SUBSTITUTE(ACONCAT(IF($U$2:$U$9=A2,"; "&$V$2:$V$9&"("&$W$2:$W$9&")","")),"; ","",1)

confirmed with CTRL+SHIFT+ENTER simultaneously (not just Enter)

Excel wraps the formula with curly-braces { }

copy down

HTH

M.

 

[mick0005]

Hi,

Maybe this

Copy the function below to a standard module

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

A G
<table style="width: 204pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="272"><colgroup><col style="width: 48pt;" width="64"><col style="width: 156pt;" width="208"></colgroup><tbody><tr style="height: 15pt;" height="20"><td style="border: 0.5pt solid windowtext; background-color: rgb(197, 217, 241); width: 48pt; height: 15pt;" class="xl66" height="20" width="64">DC</td><td style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; background-color: rgb(197, 217, 241); width: 156pt;" class="xl67" width="208">Market</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 15pt;" class="xl65" align="right" height="20">7</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">Coop1(5); Coop9(30); Coop7(19)</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 15pt;" class="xl65" align="right" height="20">8</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 15pt;" class="xl65" align="right" height="20">14</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">Coop1(20); Coop5(60)</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 15pt;" class="xl65" align="right" height="20">15</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">Coop2(30)</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 15pt;" class="xl65" align="right" height="20">20</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">Coop3(45)</td></tr><tr style="height: 15pt;" height="20"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 15pt;" class="xl65" align="right" height="20">21</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">Coop4(55)</td></tr></tbody></table>


formula in G2
=SUBSTITUTE(ACONCAT(IF($U$2:$U$9=A2,"; "&$V$2:$V$9&"("&$W$2:$W$9&")","")),"; ","",1)

confirmed with CTRL+SHIFT+ENTER simultaneously (not just Enter)

Excel wraps the formula with curly-braces { }

copy down

HTH

M.

Marcello - this one looks promising but I was not able to get it to work, it gives me a Name? error. Would it be possible for me to send you my spreadsheet and you can get it working properly? That way I can see what you did and hopefully be able to recreate it on future editions of this tool I hope to use going forward.

Thanks!

 

[Marcelo Branco]

Marcello - this one looks promising but I was not able to get it to work, it gives me a Name? error. Would it be possible for me to send you my spreadsheet and you can get it working properly? That way I can see what you did and hopefully be able to recreate it on future editions of this tool I hope to use going forward.

Thanks!

Hi,

Did you copy the ACONCAT function to a standard module in VBEditor?

To do this
1. Copy (Ctrl+C) the function as i posted
2. Alt+F11 to open VBEditor
3. F7 to show the code-panel in the right side of the window (if it is already open, no problem)
4. Click on Insert and pick Module
5. A new module is inserted (Module1 if you do not have another already created)
6. In the right-panel paste the function (Ctrl+V) and check if everything is ok
7. Back to Excel (click in the green-icon at toolbar, the first from left to right) and insert the formula in the proper place (cell G2 in your case, i think)
8. Confirm with Ctrl+Shift+Enter and copy down

Done!

Save the file as macro-enabled (.xlsm)

HTH

M.

 

[mick0005]

Hi,

Did you copy the ACONCAT function to a standard module in VBEditor?

To do this
1. Copy (Ctrl+C) the function as i posted
2. Alt+F11 to open VBEditor
3. F7 to show the code-panel in the right side of the window (if it is already open, no problem)
4. Click on Insert and pick Module
5. A new module is inserted (Module1 if you do not have another already created)
6. In the right-panel paste the function (Ctrl+V) and check if everything is ok
7. Back to Excel (click in the green-icon at toolbar, the first from left to right) and insert the formula in the proper place (cell G2 in your case, i think)
8. Confirm with Ctrl+Shift+Enter and copy down

Done!

Save the file as macro-enabled (.xlsm)

HTH

M.

Got it! I wasn't using a module, I was using a sheet.

One more thing I am hoping maybe you can tweak... With this spreadsheet, there are instances where there is a Market listed (phoenix for example) but the rest count is blank (by means of an if then function which makes the cell blank by the false result being "" or the positive result being a positive rest count number)

With your function, it is outputting a "Phoenix()". What I would like for it to do is to leave that cell blank and ONLY put a result in the form of "Market(rest count)" when the rest count field contains a number and is not blank (or in essence the cell's value is > 0).

Can you tweak the function??