Jul 12 2022 03:54 AM
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
Facility | Sector |
1 | A |
2 | A |
3 | B |
4 | C |
5 | C |
1 | B |
2 | C |
Jul 12 2022 04:08 AM
=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.
Jul 12 2022 05:14 AM
Jul 12 2022 05:17 AM
Jul 12 2022 05:35 AM
With 2021/365. Probably what @OliverScheurich had in mind:
in D2:
=LET(
Facility, A2:A8,
uFacility, UNIQUE(Facility),
FILTER(uFacility, COUNTIF(Facility,uFacility) > 1)
)
Jul 12 2022 05:44 AM
=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.
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.
Jul 12 2022 07:12 AM
Jul 12 2022 09:48 AM
Sample attached...
Jul 25 2022 06:47 AM
"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