Forum Discussion

Jeremy1565's avatar
Jeremy1565
Copper Contributor
Oct 07, 2022

Combine bank statement entries

Hi,

 

I have downloaded my monthly banks statement as an excel document, and I currently have a table that has 3 columns:

Narration (The business name)

Debit

Credit

 

I want to be able to combine all the debit/credit in a column with all other entries that have the same narration.

 

This is an example of what the data looks like

This is an example of what I want to be able to do

 

 

Thanks for any help!

 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 07, 2022

    Jeremy1565 

     

    In case you want to do it with formulas and run Microsoft 365...

     

    in J3:

    =LET(
    Narrations, UNIQUE(Table1[Narration]),
    Debits,     BYROW(Narrations, LAMBDA(rw, SUMIF(Table1[Narration],rw,Table1[Debit]))),
    Credits,    BYROW(Narrations, LAMBDA(rw, SUMIF(Table1[Narration],rw,Table1[Credit]))),
    CHOOSE(SEQUENCE(,3), Narrations, Debits, Credits)
)

      

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 07, 2022

    Hi Jeremy1565 

     

    A classic PivotTable does it easily (avail. in attachement). Otherwise...

    With Power Query, Group by [Narration] and do an aggregate Sum or [Debit] and an aggregate Sum of [Credit]:

     

     

    Sample attached

Resources