Extract Unique Values From Across Column P2:R10

[bjnockle]

1. Looking for formula to extract unique values across column P2:R10 into desired output in column S2:W10. Formula should use Head-0 as the pull condition.

1. Looking for formula to sum the unique values across column P2:R10 into desired output in column X2:X10.

See attached sample file. Thanks.

[Bo_Ry]

Please try at
P2:R2
=IFERROR(INDEX($C2:$O2,AGGREGATE(15,6,COLUMN($C2:$O2)-COLUMN($B2)/(MATCH($C2:$O2,$C2:$O2,)=COLUMN($C2:$O2)-COLUMN($B2)),COLUMNS($P2:P2))),"")

S2:W2

Please Login or Register  to view this content.

or S2:W2 with Volatile Indirect

Please Login or Register  to view this content.

X2
=IF($B1=$B2,X1,COUNTIF(S2:W2,"?*"))

[bjnockle]

Bo_Ry; Your proposed formula works. However, it is crashing the actual excel workbook because of the large datasets - 52,000 rows. Is there a way for non-array? Thanks a lot.

[Bo_Ry]

To find unique value, array calculation is need.

Try Power Query or VBA instead.

Power Query

Please Login or Register  to view this content.

VBA

Please Login or Register  to view this content.