Forum Discussion

Exil2212's avatar
Exil2212
Copper Contributor
Feb 07, 2024

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

  • 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)
      )
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    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.

     

     

     

Resources