Combining a table of data into summarised lines

[TheRedCardinal]

I've tried to solve this myself but I've now hit a brick wall.

I have a table of data with 12 columns and multiple rows that vary.

I want to split that table out into two workbooks depending on the value of the content of column 5 - which will either be "Payable or Receivable"

I then want to condense all lines which have the same reference in column 2 into a single line, maintaining all the other data (which is text and so identical) except the values in columns 7 8 and 9 which I need to sum up.

I've attempted a summary here (I've removed some columns for the sake of the visual)

I started with this, which was to create a dictionary containing unique values.

Set SalesObjDict = CreateObject("Scripting.Dictionary")
Set PurchObjDict = CreateObject("Scripting.Dictionary")
Set Wbk1 = ThisWorkbook
Set WS1 = Wbk1.Sheets("Results")

ResultsArray = WS1.Range("Data")

For Counter = 2 To UBound(ResultsArray)

    If ResultsArray(Counter, 5) = "VAT payable" Then
    
        SalesObjDict(ResultsArray(Counter, 2)) = 1
    
    Else
    
        PurchObjDict(ResultsArray(Counter, 2)) = 1
    
    End If

Next Counter

End Sub

This worked well and I have 2 dictionaries containing unique values.

But now I am not sure where to go....?

 

[alansidman]

If you use XL2BB to post your sample file, I am sure that those here can offer up several options to achieve your expected results.

 

[Zack Barresse]

Hi there,

Why not use Power Query for this? You would need two queries, one for each desired output. You wouldn't quite get what you're asking for, as columns 1 and 3 (Date and Source) do not contain unique values by Voucher.

Assuming your data is in a Table, further the Table is named "tData", your queries would look like this...

qPayable

let
    Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
    #"TypeColumns" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Voucher", Int64.Type}, {"Source", type text}, {"VAT code", type text}, {"VAT direction", type text}, {"Name", type text}, {"Amounts", Int64.Type}, {"Origin", Int64.Type}, {"Other1", Int64.Type}, {"Other2", Int64.Type}}),
    #"Filter" = Table.SelectRows(#"TypeColumns", each ([VAT direction] = "VAT payable")),
    #"Group" = Table.Group(#"Filter", {"Voucher", "VAT code", "VAT direction", "Name"}, {{"Amount_Sum", each List.Sum([Amounts]), type number}, {"Origin_Sum", each List.Sum([Origin]), type number}, {"Other1_Sum", each List.Sum([Other1]), type number}, {"Other2_Sum", each List.Sum([Other2]), type number}})
in
    #"Group"

qReceivable

let
    Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
    #"TypeColumns" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Voucher", Int64.Type}, {"Source", type text}, {"VAT code", type text}, {"VAT direction", type text}, {"Name", type text}, {"Amounts", Int64.Type}, {"Origin", Int64.Type}, {"Other1", Int64.Type}, {"Other2", Int64.Type}}),
    #"Filter" = Table.SelectRows(#"TypeColumns", each ([VAT direction] = "VAT receivable")),
    #"Group" = Table.Group(#"Filter", {"Voucher", "VAT code", "VAT direction", "Name"}, {{"Amount_Sum", each List.Sum([Amounts]), type number}, {"Origin_Sum", each List.Sum([Origin]), type number}, {"Other1_Sum", each List.Sum([Other1]), type number}, {"Other2_Sum", each List.Sum([Other2]), type number}})
in
    #"Group"

They're almost identical, of course. Load them to Tables, connection, whatever. You can even export the data connection as a file.

Is this still something you want VBA for?

 

[TheRedCardinal]

Thanks!
Power Queries are not something I'm familiar with... I'll look them up! They sound interesting and useful.

 

[alansidman]

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

 

[Zack Barresse]

Edit: Of course I forgot Jon's post lol. Thanks Alan.

You should definitely look into Power Query. It's one of the most powerful tools ever put into Excel. There's a bit of a learning curve but you'll find many good resources online for it. Below are some resources to get you started.

Websites

Power Query 101 - Microsoft Support

Use Excel's Power Query experience to create a query that imports data from a web page, then transform the data.

support.microsoft.com

About Power Query in Excel - Microsoft Support

With Power Query, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.

support.office.com

The Complete Guide to Power Query | How To Excel

www.howtoexcel.org

Videos