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? ...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 07, 2024

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)
  )

Resources