Remove duplicate rows, keep different columns

Copper Contributor

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

6 Replies
can you try to pivot it and put do not summarize on the value column


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.





@Eniola1814 thanks, that worked!

Thanks, that worked!


Using dynamic array formulas, firstly with absolutely consistent data pattern


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)
Glad it worked! You're welcome.