SOLVED

Array data report

%3CLINGO-SUB%20id%3D%22lingo-sub-2350295%22%20slang%3D%22en-US%22%3EArray%20data%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2350295%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20take%20a%20table%20of%20data%20and%20separate%20it%20into%20a%20report%20by%20store%20location.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2350295%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2350473%22%20slang%3D%22en-US%22%3ERe%3A%20Array%20data%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2350473%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1052674%22%20target%3D%22_blank%22%3E%40Kevin1715%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20A6%20of%20each%20worksheet%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(INDEX(Data!B%241%3AB%24100%2C%20AGGREGATE(15%2C6%2C1%2F(--%24H%241%3DData!%24A%241%3A%24A%24100)*ROW(Data!%24A%241%3A%24A%24100)%2CROW()-ROW(%24A%245)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20B6%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(INDEX(Data!C%241%3AC%24100%2C%20AGGREGATE(15%2C6%2C1%2F(--%24H%241%3DData!%24A%241%3A%24A%24100)*ROW(Data!%24A%241%3A%24A%24100)%2CROW()-ROW(%24A%245)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20them%20down%20till%20end%20of%20the%20form%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2350489%22%20slang%3D%22en-US%22%3ERe%3A%20Array%20data%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2350489%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1052674%22%20target%3D%22_blank%22%3E%40Kevin1715%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%20you%20can%20run.%20It%20assumes%20that%20the%20store%20numbers%20on%20the%20Data%20sheet%20are%20grouped%20together%2C%20as%20in%20your%20sample%20workbook.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20ReportByStore()%0A%20%20%20%20Dim%20wshS%20As%20Worksheet%0A%20%20%20%20Dim%20wshT%20As%20Worksheet%0A%20%20%20%20Dim%20r1%20As%20Long%0A%20%20%20%20Dim%20r2%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Set%20wshS%20%3D%20Worksheets(%22Data%22)%0A%20%20%20%20r1%20%3D%202%0A%20%20%20%20Do%20While%20wshS.Range(%22A%22%20%26amp%3B%20r1).Value%20%26lt%3B%26gt%3B%20%22%22%0A%20%20%20%20%20%20%20%20Set%20wshT%20%3D%20Worksheets(CStr(wshS.Range(%22A%22%20%26amp%3B%20r1).Value))%0A%20%20%20%20%20%20%20%20r2%20%3D%20r1%0A%20%20%20%20%20%20%20%20Do%20While%20wshS.Range(%22A%22%20%26amp%3B%20r2%20%2B%201)%20%3D%20wshS.Range(%22A%22%20%26amp%3B%20r1).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20r2%20%3D%20r2%20%2B%201%0A%20%20%20%20%20%20%20%20Loop%0A%20%20%20%20%20%20%20%20wshT.Range(%22A6%22).Resize(r2%20-%20r1%20%2B%201%2C%202).Value%20%3D%20wshS.Range(%22B%22%20%26amp%3B%20r1).Resize(r2%20-%20r1%20%2B%201%2C%202).Value%0A%20%20%20%20%20%20%20%20r1%20%3D%20r2%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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)
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

@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
This worked perfectly. Thank you!!!!

@Kevin1715 , you are welcome