Aug 26 2021 02:29 PM
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
Aug 26 2021 02:51 PM - edited Aug 26 2021 03:04 PM
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.
Aug 26 2021 03:21 PM
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.
Aug 26 2021 04:26 PM
Aug 27 2021 01:16 AM
SolutionYou 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.
Aug 27 2021 07:45 AM
Aug 27 2021 08:01 AM
I wish! But I couldn't manage to find one... :(
Aug 27 2021 01:16 AM
SolutionYou 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.