Mapping from raw data with multiple repeating IDs

Marcus_230
Lounger
Posts: 37
Joined: 04 Oct 2023, 14:49

Mapping from raw data with multiple repeating IDs

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 79676
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mapping from raw data with multiple repeating IDs

Post by HansV »

Welcome to Eileen's Lounge!

I'll take a look after dinner (European time)
Best wishes,
Hans

Marcus_230
Lounger
Posts: 37
Joined: 04 Oct 2023, 14:49

Re: Mapping from raw data with multiple repeating IDs

Post by Marcus_230 »

Thank you very much.

User avatar
HansV
Administrator
Posts: 79676
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mapping from raw data with multiple repeating IDs

Post by HansV »

See the attached version.

mapping_data.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Marcus_230
Lounger
Posts: 37
Joined: 04 Oct 2023, 14:49

Re: Mapping from raw data with multiple repeating IDs

Post by 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.

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: Mapping from raw data with multiple repeating IDs

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: Mapping from raw data with multiple repeating IDs

Post by 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
Lounger
Posts: 37
Joined: 04 Oct 2023, 14:49

Re: Mapping from raw data with multiple repeating IDs

Post by Marcus_230 »

hamster wrote:
05 Oct 2023, 08:37
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.

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: Mapping from raw data with multiple repeating IDs

Post by hamster »

Marcus_230 wrote:
05 Oct 2023, 13:00
Thank you again.
You are welcome :cheers:

User avatar
p45cal
2StarLounger
Posts: 171
Joined: 11 Jun 2012, 20:37

Re: Mapping from raw data with multiple repeating IDs

Post by p45cal »

…or a bog standard Pivot table.
You do not have the required permissions to view the files attached to this post.

Marcus_230
Lounger
Posts: 37
Joined: 04 Oct 2023, 14:49

Re: Mapping from raw data with multiple repeating IDs

Post by Marcus_230 »

Thank you very much!