Creating many lists from a report

[Kris V]

Hi, Excel Experts! I have a very urgent question.

I am automating a report I'm doing every month. I will usually download this financial report which contains many accounts. Then on my reconciliation file, I need to separate the report per each account on each tab (each tab corresponds to one account). Currently, i would filter the downloaded report and then copy the data for each account. What I want to do is to have the tabs formulated that they will just list all the items in that downloaded report.

Downloaded Report
Account | Reference | Amount
A | 1212 | $10
B |3140 | $20
C | 6333 | $52
A | 1521 | $60
C | 6453 | $90
C | 6789 | $20

Output:
Tab 1- Account A
Reference | Amount
1212 | $10
1521 | $60


Tab 2 - Account B
Reference | Amount
3140 | $20

Tab 3 - Account C
Reference | Amount
6333 | $52
6453 | $90
6789 | $20

So, what formula do I need to use so that Tab 1 or Tab 2 (and so forth) just lists the data from the downloaded file?

I tried sumproduct by assigning 1,2,3,4 for each line but then it won't work because it doesn't mean that Account A will always be on line #1, etc.

Thanks ahead for your help!

 

[Kris V]

Here is a sample of what my report would look like. But my downloaded data is usually thousands of line items and I just want them listed on each tab.
Thanks again!

 

[Debraj]

try this..

Delete the top line from A1 in 1st sheet..

Sub test()
    deb = Sheets("Download Sample").Range("A2").CurrentRegion
    For j = 2 To UBound(deb)
        If InStr(raj & ",", "," & deb(j, 1) & ",") = 0 Then raj = raj & "," & deb(j, 1)
    Next
    roy = Split(Mid(raj, 2), ",")
    For i = 1 To UBound(roy) + 1
        Application.DisplayAlerts = False
        On Error Resume Next
            Sheets("Account " & roy(i - 1)).Delete
        On Error GoTo 0
        Application.DisplayAlerts = True
     
            Sheets.Add(, Sheets(Sheets.Count)).Name = "Account " & roy(i - 1)
     
            With Sheets("Download Sample").Range("A2").CurrentRegion
                ActiveSheet.Range("A1:B1") = Array("Account", roy(i - 1))
                .AutoFilter 1, roy(i - 1)
                .SpecialCells(12).Offset(, 1).Copy ActiveSheet.Range("A3")
                .AutoFilter
            End With
    Next i
End Sub

 

[Xiq]

How about a PivotTable solution? (see attached file)
I'm pretty sure you can slice the table in different tabs by account (if you still need it), but not sure how to yet.

 

[Abhijeet R. Joshi]

You can try this as well..

Once you execute this macro you can then use freeze panes to hold the first row as it is..

Sub AJ_08()
Dim i As Long
    Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:D7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    drc = Range("A" & Application.Rows.Count).End(xlUp).Row
        For i = Range("A65536").End(xlUp).Row To 3 Step -1
        If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
            Rows(i).Insert
        End If
    Next i
End Sub

Hope this helps....

 

[Xiq]

How about a PivotTable solution? (see attached file)
I'm pretty sure you can slice the table in different tabs by account (if you still need it), but not sure how to yet.

Alright, found an easy way to split each account to a different sheet when using the PivotTable.
This is what you do:

1. Move the "Account"-field to the "Report Filter"-box
2. Remove any applied filter (so your example shows all the accounts)
3. In the ribbon, go to: "PivotTable Tools"-tab > "Options"-subtab > "PivotTable"-section
4. There you see the button "Options", click the little arrow for a dropdown menu
5. Select "Show Report Filter Pages..."
6. You should see "Account" highlighted, press OK
7. ...
8. Profit!

 

[Kris V]

Thanks, all! But is there any way i can just formulate the account tabs to get the data from the download tab, without doing a macro?

 

[SirJB7]

Hi, Kris V!
In this case even pivot tables are flexible, I'd stick to Debraj(ex-Roy)'s suggestion and go for the VBA code solution.
Regards!

 

[SirJB7]

@Abhijeet R. Joshi
Hi!

May I write a few comments about the code? Hope you don't mind.

I'd write it as follows, without analyzing if it works or not, it's just a matter of clearness and accuracy.
- Use With...EndWith blocks
- Don't assume all worksheets have 65536 rows, they might have 1048576 and perhaps all full or all empty or just titles full (aka border conditions)

Option Explicit
 
Sub AJ_08()
    Dim i As Long, drc As Long
    Cells.Select
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        With .SortFields
            .Clear
            .Add Key:=Range("A2:A7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("B2:B7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("C2:C7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange Range("A1:D7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    If Range("A" & ActiveSheet.Rows.Count).Value <> "" Then
        drc = ActiveSheet.Rows.Count
    Else
        drc = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
    End If
    For i = drc To 3 Step -1
        If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
            Rows(i).Insert
        End If
    Next i
End Sub

Despite of the first part of the code, I won't recommend to do this, in fact I'd recommend to don't to this:

...

...
    drc = Range("A" & Application.Rows.Count).End(xlUp).Row
        For i = Range("A65536").End(xlUp).Row To 3 Step -1
        If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
            Rows(i).Insert
        End If
    Next i
End Sub

... what if the worksheet has many thousands of non empty rows? You'll be executing thousands of times a statement that it's slow and heavy by itself: the Insert method applied to the object Row.

And I didn't understand at all what it does, but it isn't important here.

BTW, I assume you've yet carefully read this:
http://chandoo.org/forum/threads/new-users-please-read.294/
Always get sure that your answers are compliant with the 4th paragraph in the Always section. This is in general, not specifically in this case.

Regards!

 

[Haseeb A]

Hello Kris,

In Account A tab A4, enter this Array Formula with CTRL+SHIFT+ENTER

=IFERROR(INDEX('Download Sample'!B:B,SMALL(IF('Download Sample'!$A$3:$A$100=$B$1,ROW('Download Sample'!$A$3:$A$100)),ROWS(A$4:A4))),"")

Then copy down & across.

Then change Account No in B1, Or copy this sheet & change account no.

 

[Abhijeet R. Joshi]

@SirJB7,

Apologies if I have responsed a wrong code in here....just tried it at my system and this seem to suffice...
However, your guidance will surely help me out in future...
Might be I have a poor analysing and logical thinking capacity....

 

[SirJB7]

Hi, Abhijeet R. Joshi!
Thanks for ack but it's nothing related to capacities; learning process it's just a matter of read, practice, try, fail, retry, ... until succeed. It happens to me all the time, and I hope it'd happen for a long time. And always remember Albert Einstein quote: "We are all very ignorant, what happens is that not all ignore the same things".
Regards!

 

[James Perry]

Hello Kris V....try this out:
You can use a Pivot Table with Account as a filter field.
Then use the Pivot Table Option to "Show Report Filter Pages" which will create a new worksheet for each item in the Account field.
No copy/paste. The original data stays untouched. All you need to do is 'Refresh All' to update all of the PT's when new data is added.
HTH

 

[kchiba]

Thanks James, that is powerful