VLOOKUP to return multiple rows and columns into one cell?

gmooney

Active Member
Joined
Oct 21, 2004
Messages
252
Office Version
  1. 365
Platform
  1. Windows
I have a large table of about 1500 rows that have various facts that I need to return into a single vlookup formula. I need to bring in multiple rows into the same cell based on the fact that the rows can have duplicate dates and geographies.

Below is a snapshot of the data.....

Assume that I am adding the formula into a different sheet in cell C1 and that "Sales Start Date" is in cell A1 and "Retailer" is in cell B1 I need the results of "Product", "Size", and "Unit Cost"

So my answer in cell C1 should be

STR (12-16oz) - $2.50 & BLU (12-16oz) - $2.50 & ORG STR (16oz) - $3.00


Sales Start DateSales End DateRetailerParent RetailerMarketProductBrandManufacturerSizeAd PriceReg PriceOfferUnit Price
8/20/20148/26/2014Kroger ATLKrogerAtlanta, GA<st1:stockticker>STR</st1:stockticker>Fresh GroceryFresh Grocery12-16OZ 2/$5.00$2.50
8/20/20148/26/2014Kroger ATLKrogerAtlanta, GABLUFresh GroceryFresh Grocery12-16OZ 2/$5.00$2.50
8/20/20148/26/2014Kroger ATLKrogerAtlanta, GAORG STRFresh GroceryFresh Grocery16OZ 2/$6.00$3.00
8/13/20148/19/2014Kroger ATLKrogerAtlanta, GABLUFresh GroceryFresh Grocery1PT$1.99 $1.99
8/13/20148/19/2014Kroger ATLKrogerAtlanta, GA<st1:stockticker>STR</st1:stockticker>Fresh GroceryFresh Grocery16OZ 2/$5.00$2.50
8/13/20148/19/2014Kroger ATLKrogerAtlanta, GAORG STRFresh GroceryFresh Grocery16OZ 2/$7.00$3.50
8/6/20148/12/2014Kroger ATLKrogerAtlanta, GA<st1:stockticker>STR</st1:stockticker>Fresh GroceryFresh Grocery16OZ$1.99 $1.99
8/6/20148/12/2014Kroger ATLKrogerAtlanta, GABLUFresh GroceryFresh Grocery1PT 2/$3.00$1.50

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
For clarity, let's assume that A1:M9 contains the table, O2 contains the start date, and P2 contains the retailer. First, place the following code in a standard module (Alt+F11 > Insert > Module > Copy/Paste code > Alt+Q)...

Code:
Option Explicit
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

Then try the following worksheet formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

Code:
=SUBSTITUTE(AConcat(IF($A$2:$A$9=O2,IF($C$2:$C$9=P2," & "&$F$2:$F$9&" ("&$I$2:$I$9&") - "&TEXT($M$2:$M$9,"$#,##0.00"),""),""))," & ","",1)

Hope this helps!
 
Upvote 0
Thank you..this worked perfectly....now I have one more thing that I need to do with this.

In another cell I need to bring in the day of the week that the Start Dates are. For example most of the time the retailers will have the same start day of the week for all of their Ads but occasionally they will change them to a different day of the week.

In my example I want for it to simply give me a list of any day of the week that the start dates align with. If every AD starts on Wednesday then I want only Wednesday returned...If most ADs broke on Wednesday but earlier in the Year they were breaking on Sunday I want my formula to tell me Wednesday and Sunday and the start date that the Ads changed from Sunday to Wednesday.

Any help is greatly appreaciated.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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