looking up multiple values based on date and category

[halb]

I have information below for date, description, debits and credits. I want to put a formula and get the results like on the right, with each description having their own category and amounts. I have also attached an excel sheet with the same information. I want the formula to pick up any values with the same date and category, and it should be different values every time until there are no values for that date and category combined.

 

[p45cal]

Right-click somewhere in the table at cell I11 and choose Refresh to update the result. (I've deleted several rows of data in the results table so that you can see the information appearing as a demo.)

 

[halb]

Thank you so much, I was able to use the excel sheet and modify the instructions as needed.

Just one question, for the invoked function part below, would there be a simpler way to do it if possible,

(tbl)=>
let
Source = tbl,//#"Table1 (2)",
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
TblHdrs=List.Skip(Table.ColumnNames(#"Removed Top Rows"),1),
#"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
#"Added Custom" = Table.CombineColumnsToRecord(#"Removed Top Rows","xxx",TblHdrs),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each List.RemoveNulls(Record.ToList([xxx]))),
rowCount= List.Max(List.Skip(#"Added Custom2"[Custom.1],1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"xxx"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom.1", each List.Count([Custom])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom.1"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Custom", each Text.Combine(List.Transform(_, Text.From), "¬"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("¬", QuoteStyle.Csv),rowCount),
#"Transposed Table1" = Table.Transpose(#"Split Column by Delimiter"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
#"Promoted Headers"

 

[p45cal]

Very very likely. Busy over the next 2 days but I'll think on it.

 

[p45cal]

As it happens, started looking at it; try:

(tbl)=>
let
    Source = tbl,
    ToCols = Table.ToColumns(Source),
    NewTable = Table.FromColumns(List.Transform(ToCols, each List.RemoveNulls(_)),Table.ColumnNames(Source)),
    #"Removed Blank Rows" = Table.SelectRows(NewTable, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(Record.RemoveFields(_,{"Date","Index"})), {"", null})))
in
    #"Removed Blank Rows"

Adapted from https://stackoverflow.com/questions/49363772/power-query-list-removenulls-on-column-collection

 

[Luke M]

Per forum rules, remember to include you crosspost links:
https://www.excelforum.com/excel-fo...s-based-on-date-and-category.html#post5675517

https://www.mrexcel.com/board/thread...egory.1205058/

 

[Peter Bartholomew]

I managed to make a 365 worksheet formula give a result, but couldn't really recommend it.

= REDUCE(header,UNIQUE(Table1[Date]),LAMBDA(stack,date,
     VSTACK(stack,REDUCE(date,category,DaysTransactionsλ(date)))
  ))

The worksheet formula stacks the single day transactions vertically. The other part of the formula is the Lambda function
DaysTransactionsλ(date)
that builds the block of categorised lists for a single day

= LAMBDA(d,LAMBDA(block,cat,
     IFERROR(
        HSTACK(
           block,
           FILTER(
              Table1[Credits] - Table1[Debits],
              (Table1[Description] = cat) * (Table1[Date] = d),
              ""
           )
        ),
     "")
  ))