Pfthb

I have the following formula to make a unique list from column plant in table 15:

=IFERROR(INDEX(Tabel15[Plant];MATCH(0;COUNTIF(Analyses!$Q$2:$Q2;Tabel15[Plant]);0));"")

This formula is working, but when there is just 1 value in column plant the formula gives a value of 0. This is wrong because it should return the value.
Does anyone know how I can adapt this formula to make it work?

I wanted to change it to this:

=IF(COUNTA(Tabel15[plant])>0;INDEX(Tabel15[Plant];MATCH(0;COUNTIF(Analyses!$Q$2:$Q2;Tabel15[Plant]);0));Kopie - datablad$G$2)

But it doesn't work either.

Good mock example. Try and see if this works:

The formula counts the unique cells against another list. The unique list expects to take the first row, no matter what. It also expects you to have more than one value in your duplicate list. If it doesn't you can't compare since it expect duplicates and it throws an error, #N/A. This is mask as blank cell since it's wrapped in IFERROR:

"Unique formula" = IFERROR(INDEX(Tabel15[Plant],MATCH(0,COUNTIF($Q$1:Q2,Tabel15[Plant]), 0)),"")

To solve this we check how many values it exist in our duplicate list:

=IF(COUNTA(Tabel15[Plant])>1,... "Unique formula" ... ,Tabel15[Plant]) //***//

This will give us this result.

Then you probably don't want duplicates...

So we need to check if previous rows contain any of the values the formula would return.
The VLOOKUP formula do that for us, and as lookup value we use the formula above //***// and lookup range will be our current column: $Q$1:Q2. NOTICE this is a dynamic range so Q2 is relative reference (no $).

=IF(ISERROR(VLOOKUP(IF(COUNTA(Tabel15[Plant])>1,IFERROR(INDEX(Tabel15[Plant],MATCH(0,COUNTIF($Q$1:Q2,Tabel15[Plant]), 0)),""),Tabel15[Plant]),$Q$1:Q2,1,FALSE))

So the Final result we need to apply is this in Cell Q3:

=IF(ISERROR(VLOOKUP(IF(COUNTA(Tabel15[Plant])>1,IFERROR(INDEX(Tabel15[Plant],MATCH(0,COUNTIF(Analyses!$Q$1:Q2,Tabel15[Plant]), 0)),""),Tabel15[Plant]),Analyses!$Q$1:Q2,1,FALSE)),IF(COUNTA(Tabel15[Plant])>1,IFERROR(INDEX(Tabel15[Plant],MATCH(0,COUNTIF(Analyses!$Q$1:Q2,Tabel15[Plant]), 0)),""),Tabel15[Plant]),"")

The macro error can be ignored by:

If Not IsError(Sheets("Hulpblad").Range("B6").Value) Then
t = Sheets("Hulpblad").Range("B6").Value 
'Code...
End If

there is no problem in your formula, it is just telling that there are blanks in the range, 0 means blank. the formula is treating the blank as a value and also considering it in the unique value calculations.

If you want to remove 0 you can just insert an if over your formula to remove it. like

=if(formula = 0, "", formula)

or in orignal form

=IF( (IFERROR(INDEX(Tabel15[Plant],MATCH(0,COUNTIF(Analyses!$Q$2:$Q2,Tabel15[Plant]),0)),""))=0,"",IFERROR(INDEX(Tabel15[Plant],MATCH(0,COUNTIF(Analyses!$Q$2:$Q2,Tabel15[Plant]),0)),""))

or go in the cell formatting and change the format to display 0 as a dash.

sometimes blank is also used as error checking, you can apply such formulae as well to check how many are blank, maybe that would someday be used to check any data entry problems.