Forum Discussion
Lowest Unique Value Just Looking At Every Other Cell in a Column
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
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.
6 Replies
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.
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.
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.
