Forum Discussion
Remove Duplicates Preserving Values
Perhaps create a column that concatenates the two columns as text. Then you could remove your first column, group on that new column and sum the 2nd column.
You would end up with:
a10, 10
a30, 30
b20, 40
This could be a valid solution if I'd to work with only one table. However, my main goal is to create a function, use it on multiple tables from diferent sources, and then group all using sum.
To be more specific:
- The dataset is a balance sheet (Column A: assets, liability, capital, Column B: Year 20XX )
- I appended the same table (exact same format) from different companies multiple times
- Then Grouped by Column A, and sum Column B to get Consolidated data for the Industry.
The problem is that there is one line in Column A, that repeats 2 times (as an asset and as a liability), making the consolidation wrong. Here's a graphical example of my problem:
Note:
A =Asset
AL = the duplicated row in Assets and Liabilities
L = Liability
C = Capital
In the example, Total Assets = 15 and Total Liabilities and Capital = 15 for each company