SOLVED

Lowest Unique Value Just Looking At Every Other Cell in a Column

Copper Contributor

I'm needing help at just identifying the lowest unique value in a column but using every other row in the column. The trick is, there's values in the cells that could be the same in the odd rows. Just need the even lowest unique value?

 

Thanks,

Mark

6 Replies

@mars5050 

Let's say you want to look at A2:A100.

As an array formula confirmed with Ctrl+Shift+Enter:

 

=MIN(IF(ISEVEN(ROW(A2:A100))*(A2:A100<>""),A2:A100))

 

Sorry, you wanted unique values, ignore this.

@mars5050 

Do you have Excel 365? If so:

Let's say you want to look at A2:A100.

Select D2 (or another cell) and enter the formula

=FILTER(A2:A100,ISEVEN(ROW(A2:A100))*(A2:A100<>""))

This will automatically spill to the rows below and return the non-blank values from the even rows.

In yet another cell, enter the formula

=MIN(IF(COUNTIF(D2#,D2#)=1,D2#))

This is the minimum unique value from A2:A100.

Hans, that would work but I was needing something in the same column using conditional formatting. Thanks, I wanted to color the cell for the lowest unique value in even cells in the column.
best response confirmed by mars5050 (Copper Contributor)
Solution

@mars5050 

You could create the formulas that I suggested in my first reply. Let's say the formula that returns the unique lowest value is in F2.

 

Select A2:A100 (the column with the data from my previous example).

A2 should be the active cell in the selection.

On the Home tab, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=AND(ISEVEN(ROW(A2)),A2=$F$2)

 

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.

Hans, Perfect! Now for the big question. Can you just write ONE formula for conditional formatting that does all at one time?

Thanks,
Mark

@mars5050 

I wish! But I couldn't manage to find one... :(

1 best response

Accepted Solutions
best response confirmed by mars5050 (Copper Contributor)
Solution

@mars5050 

You could create the formulas that I suggested in my first reply. Let's say the formula that returns the unique lowest value is in F2.

 

Select A2:A100 (the column with the data from my previous example).

A2 should be the active cell in the selection.

On the Home tab, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=AND(ISEVEN(ROW(A2)),A2=$F$2)

 

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.

View solution in original post