Mapping from raw data with multiple repeating IDs

[Marcus_230]

Hello,

I am trying to map a raw data (about 1600 rows) with multiple unique IDs on rows due to the different charges on a ledger as shown on the attached test file. Goal is to have one unique ID on each row with correct name tied to it, showing SUM for each column based on the charges in the column per the unique ID.
Thank you.

Marcus

mapping_data.xlsx

[HansV]

Welcome to Eileen's Lounge!

I'll take a look after dinner (European time)

[Marcus_230]

Thank you very much.

[HansV]

See the attached version.

mapping_data.xlsx

[Marcus_230]

Perfect! Now I can see, where I was making mistakes. Thank you very much. I truly appreciate it! Need to start taking some mapping Excel courses to learn more.

[hamster]

with Power Query

Code: Select all

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"ID", "Name"}, {{"Pay", each List.Sum([Payments]), type nullable number}, {"Dep", each List.Sum([Deposits]), type nullable number}}),
    Subtract = Table.AddColumn(Group, "Subtraction", each [Dep] - [Pay], type number)
in
    Subtract

s666-PQ-Mapping from raw data with multiple repeating IDs.xlsx

[hamster]

You need to use Data - From Table , then in PQ Editor: select two first columns choose Group , in Advanced mode add two aggregation (sum) appropriate columns) then simply subtract these two columns and on the end load result to the sheet
as I see you don't know what Power Query is so try to read here: https://learn.microsoft.com/en-us/power-query/

btw. this is NOT a VBA !

[Marcus_230]

You need to use Data - From Table , then in PQ Editor: select two first columns choose Group , in Advanced mode add two aggregation (sum) appropriate columns) then simply subtract these two columns and on the end load result to the sheet
as I see you don't know what Power Query is so try to read here: https://learn.microsoft.com/en-us/power-query/

btw. this is NOT a VBA !

Thank you. That is correct, I have no clue what Power Query is; although, I do appreciate very much the link.
Thank you again.

[hamster]

Thank you again.

You are welcome

[p45cal]

…or a bog standard Pivot table.

[Marcus_230]

Thank you very much!