Array data report

New Contributor

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


Any help is greatly appreciated. 



4 Replies
best response confirmed by Kevin1715 (New Contributor)


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


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
        wshT.Range("A6").Resize(r2 - r1 + 1, 2).Value = wshS.Range("B" & r1).Resize(r2 - r1 + 1, 2).Value
        r1 = r2 + 1
    Application.ScreenUpdating = True
End Sub
This worked perfectly. Thank you!!!!

@Kevin1715 , you are welcome