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

@Exil2212 

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.

Patrick2788_0-1707329466087.png

 

 

 

@Eniola1814 thanks, that worked!

Thanks, that worked!

@Exil2212 

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