Log In
Register
Home
February 2, 2022
Offline
Dear all,
I would like to merge/combine different rows with same ID into a single row with multiple columns? The number of columns can be different (depends on how many corresponding accounts there are). I have attached Excel sheet also, hoping it helps to understand the matter.
Regards,
Urmas
See Answer
July 16, 2010
Offline
Hi Urmas,
The correct layout for your data is your 'current table'. From there you can use a PivotTable to group the rows with the same ID into a single row. See attached.
If that's not the layout you want, then create a mockup manually so we can see how the data gets from your current table to your desired table.
Mynda
February 2, 2022
Offline
Hello,
Thank you for the question.
In its current form, the table does not show, for example, the aggregate turnover of the counter accounts of account 11030. Observation by individual rows is not reasonable in case of 100 thousand rows (or even more). In the desired format, on the other hand, the table allows you to select a specific account (for example, 11030) and see what the total turnover of the counter accounts (determined via respective ID) is.
Hopefully it clarifies the matter.
Regards,
Urmas
July 16, 2010
Offline
Hi Urmas,
Please upload an example file that illustrates your desired result. Your explanation is difficult to follow, especially since I don't know what you mean by 'the counter accounts'.
Thanks,
Mynda
February 2, 2022
Offline
Hello,
Seems that the Pivot Tabel matter complicated the case.
The ultimate goal is to convert data from "2 or more rows" format to one row format:
| ID | Account no | Description | Date | Deebet | Kreedit | Type |
| 2548 | 11000 | Kassa | 09.01.2018 | 0,00 | 9 500,00 | Laekumised (va arved) |
| 2548 | 11021 | Kassa | 09.01.2018 | 9 500,00 | 0,00 | Laekumised (va arved) |
| ID | Account no | Description | Date | Deebet | Kreedit | Type | Account no1 | Description1 | Date1 | Deebet1 | Kreedit1 | Type1 |
| 2548 | 11000 | Kassa | 43109 | 0 | 9500 | Laekumised (va arved) | 11021 | Kassa | 43109 | 9500 | 0 | Laekumised (va arved) |
I assume it should be a matter of grouping by ID (all rows) and the nested tables should be somehow converted into rows (this keeps on row for each ID and adds columns for different rows).
Regards,
Urmas
July 16, 2010
Offline
Hi Urmas,
Thanks for clarifying. You can use Power Query to number rows grouped by ID, then Unpivot and append the number, before pivoting back. See file attached.
Mynda
Answers Post
1 Guest(s)
