Forum Discussion
Multiple Values
I have a vertical column with about 1000 addresses' is there any way to show if any address is mentioned multiple times and number of times it appears?
=LET( addr, UNIQUE( FILTER($A$1:$A$10000, COUNTIF(A1:$A$10000,A1:A10000)>1)
), cou, BYROW( addr, LAMBDA(x, COUNTIF(A1:$A$10000, x) ) ), HSTACK( addr, cou ) )With Office 365 or Excel 2021 or Excel for the web an alternative could be this formula. In my sample file the addresses are in column A.
1) Select the column.
On the Home tab of the ribbon, click Conditional Formatting > Highlight Cells Rules > Duplicate Values... and then click OK.
Duplicates will be highlighted in red.
2) To count the number of duplicates:
Let's say the addresses are in column A, starting in A2.
Enter the following formula in B2, then fill down:
=COUNTIF(A:A,A2)
