How to get a result of column values that have not a unique value in another column

New Contributor

Please help

I want to check if column Facility has unique values in column Sector

In the below case I would like to see the result : Facility 1 and 2

 

 

FacilitySector
1A
2A
3B
4C
5C
1B
2C

 

 

8 Replies

@Sjoerd2312 

=IF(SUM(N(IF($A$2:$A$8=D3,MATCH(IF($A$2:$A$8=D3,$B$2:$B$8),IF($A$2:$A$8=D3,$B$2:$B$8),0)=ROW($1:$7))))>1,"not unique value","unique value")

You can try this formula which seems to work in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

If you work with Office365 or 2021 you can apply functions such as UNIQUE and FILTER functions which should simplify the task considerably.

facility sector.JPG

 

 

 

 

When doing these checks on a large dataset excel responds:
excel ran out of resources while attempting to calculate. as a result the formulas cannot be revaluated.
can you provide the same result with unique and filter? (Office365 or 2021)

@Sjoerd2312 

 

With 2021/365. Probably what @Quadruple_Pawn had in mind:

 

_Screenshot.png

in D2:

 

=LET(
    Facility,  A2:A8,
    uFacility, UNIQUE(Facility),
    FILTER(uFacility, COUNTIF(Facility,uFacility) > 1)
)

 

@Sjoerd2312 

=IF(COUNTA(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8=E2)))>1,"not unique","unique")

This would return the same result as already shown with Excel 2013.

facility sector.JPG 

However @L z.  solution seems to be exactly what you are looking for.

 

How many rows are in your dataset? I've tried the other formula for 20000 rows and it didn't return an error.

is it possible that you upload the excel sheet? i have office 365 but it does not seem to work

@Sjoerd2312 

"i have office 365 but it does not seem to work"

If by any chance one of the proposals you got work there's a Mark as solution link at the bottom - an indicator that helps those who search.... Thanks for them