Array data report

[Kevin1715]

I'm trying to take a table of data and separate it into a report by store location. 

Any help is greatly appreciated. 

[#2 · Accepted solution]

@Kevin1715 

In A6 of each worksheet

=IFERROR(INDEX(Data!B$1:B$100, AGGREGATE(15,6,1/(--$H$1=Data!$A$1:$A$100)*ROW(Data!$A$1:$A$100),ROW()-ROW($A$5))),"")

In B6

=IFERROR(INDEX(Data!C$1:C$100, AGGREGATE(15,6,1/(--$H$1=Data!$A$1:$A$100)*ROW(Data!$A$1:$A$100),ROW()-ROW($A$5))),"")

and drag them down till end of the form

[#3]

@Kevin1715 

Here is a macro you can run. It assumes that the store numbers on the Data sheet are grouped together, as in your sample workbook.

Sub ReportByStore()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim r1 As Long
    Dim r2 As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Set wshS = Worksheets("Data")
    r1 = 2
    Do While wshS.Range("A" & r1).Value <> ""
        Set wshT = Worksheets(CStr(wshS.Range("A" & r1).Value))
        r2 = r1
        Do While wshS.Range("A" & r2 + 1) = wshS.Range("A" & r1).Value
            r2 = r2 + 1
        Loop
        wshT.Range("A6").Resize(r2 - r1 + 1, 2).Value = wshS.Range("B" & r1).Resize(r2 - r1 + 1, 2).Value
        r1 = r2 + 1
    Loop
    Application.ScreenUpdating = True
End Sub

[#4]

This worked perfectly. Thank you!!!!

[#5]

@Kevin1715 , you are welcome