May 12 2021 10:54 PM
I'm trying to take a table of data and separate it into a report by store location.
Any help is greatly appreciated.
May 13 2021 02:03 AM
SolutionIn 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
May 13 2021 02:09 AM
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
May 13 2021 02:03 AM
SolutionIn 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