Forum Discussion

Kevin1715's avatar
Kevin1715
Copper Contributor
May 13, 2021
Solved

Array data report

I'm trying to take a table of data and separate it into a report by store location.    Any help is greatly appreciated.     
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    May 13, 2021

    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

HansVogelaar's avatar
HansVogelaar
MVP
May 13, 2021

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

Resources