VLOOKUP to return multiple rows and columns into one cell?

[gmooney]

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 Date

Sales End Date

Retailer

Parent Retailer

Market

Product

Brand

Manufacturer

Size

Ad Price

Reg Price

Offer

Unit Price

8/20/2014

8/26/2014

Kroger ATL

Kroger

Atlanta, GA

<st1:stockticker>STR</st1:stockticker>

Fresh Grocery

Fresh Grocery

12-16OZ

2/$5.00

$2.50

8/20/2014

8/26/2014

Kroger ATL

Kroger

Atlanta, GA

BLU

Fresh Grocery

Fresh Grocery

12-16OZ

2/$5.00

$2.50

8/20/2014

8/26/2014

Kroger ATL

Kroger

Atlanta, GA

ORG STR

Fresh Grocery

Fresh Grocery

16OZ

2/$6.00

$3.00

8/13/2014

8/19/2014

Kroger ATL

Kroger

Atlanta, GA

BLU

Fresh Grocery

Fresh Grocery

1PT

$1.99

$1.99

8/13/2014

8/19/2014

Kroger ATL

Kroger

Atlanta, GA

<st1:stockticker>STR</st1:stockticker>

Fresh Grocery

Fresh Grocery

16OZ

2/$5.00

$2.50

8/13/2014

8/19/2014

Kroger ATL

Kroger

Atlanta, GA

ORG STR

Fresh Grocery

Fresh Grocery

16OZ

2/$7.00

$3.50

8/6/2014

8/12/2014

Kroger ATL

Kroger

Atlanta, GA

<st1:stockticker>STR</st1:stockticker>

Fresh Grocery

Fresh Grocery

16OZ

$1.99

$1.99

8/6/2014

8/12/2014

Kroger ATL

Kroger

Atlanta, GA

BLU

Fresh Grocery

Fresh Grocery

1PT

2/$3.00

$1.50

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

 

[Domenic]

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)...

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...

=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!

 

[gmooney]

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.