 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.

4 Replies
best response confirmed by Kevin1715 (New Contributor)
Solution

# Re: Array data report

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

# Re: Array data report

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

# Re: Array data report

This worked perfectly. Thank you!!!!

# Re: Array data report

@Kevin1715 , you are welcome