Forum Discussion
Exil2212
Feb 07, 2024Copper Contributor
Remove duplicate rows, keep different columns
I am trying to combine information from multiple months and remove duplicate rows for information. The chart I have has thousands of rows, multiple duplicates, for 6 months. Is there a way to do it?
What I have example:
Letter April. May
A. 1.
B. 1
C. 1
A. 1
B. 1
C. 1
What I want example:
Letter. April. May
A. 1. 1
B. 1. 1
C. 1. 1
- PeterBartholomew1Silver Contributor
Using dynamic array formulas, firstly with absolutely consistent data pattern
= HSTACK( TAKE(WRAPCOLS(letter,3),,1), WRAPCOLS(TOCOL(values,1,TRUE),3) )
and secondly by unpivoting then using PIVOTBY (in beta release)
= LET( mask, ISNUMBER(values), unpivotL, TOCOL(IF(mask, letter, NA()),3), unpivotH, TOCOL(IF(mask, months, NA()),3), unpivotV, TOCOL(values, 3), PIVOTBY(unpivotL, unpivotH, unpivotV, SUM,,0,,0) )
- Patrick2788Silver Contributor
I'm hesistant to offer a formula solution as I suspect the larger data set might be more nuanced than the sample above. I will offer a simple PowerQuery solution.
- Exil2212Copper ContributorThanks, that worked!
- Patrick2788Silver ContributorGlad it worked! You're welcome.
- Eniola-Adekoya1Copper Contributorcan you try to pivot it and put do not summarize on the value column
- Exil2212Copper Contributor
Eniola-Adekoya1 thanks, that worked!