Get Unique Values

[kishorkhanal]

I want unique values from DailyJobs worksheet cells B4:B2199 to FixErrors worksheet cells B4:B2199. Please suggest a better option or correct the following two options.

Sub CopyUniques()
Worksheets("DailyJobs").Columns("B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets("FixErrors").Range("B4"), Unique:=True
End Sub

AND


=IF(ROWS($B$4:B4)>SUM((DailyJobs!$B$4:$B$2199<>"")/(COUNTIF(DailyJobs!$B4:$B$2199,DailyJobs!$B4:$B$2199)+(DailyJobs!$B4:$B$2199=""))),"",INDEX(DailyJobs!$B4:$B$2199,MIN(IF(COUNTIF($B$3:B3,DailyJobs!$B4:$B$2199)=0,IF(DailyJobs!$B4:$B$2199<>"",ROW(DailyJobs!$B4:$B$2199)-ROW(DailyJobs!$B4)+1)))))

I would like to copy formula down from cell B4 to B2199. I don't need blank as unique value, if possible.

 

[Jonmo1]

See if this thread helps you.. xld has a very good solution

http://www.mrexcel.com/forum/showthread.php?t=324867&highlight=unique+list+formula

 

[kishorkhanal]

See if this thread helps you.. xld has a very good solution

http://www.mrexcel.com/forum/showthread.php?t=324867&highlight=unique+list+formula

I don't know where I made a mistake in this formula, please correct it.

=IF(ISERROR(MATCH(0,COUNTIF(B$4:B4,DailyJobs!B$4:B$2199&""),0)),"",
INDEX(IF(ISBLANK(DailyJobs!B$4:B$2199),"",DailyJobs!B$4:B$2199),MATCH(0,COUNTIF(B$4:B4,DailyJobs!B$4:B$2199&""),0)))

 

[Jonmo1]

You need the sheet name on ALL ranges, you missed it on the 1st one..

you missed some $

I've removed the sheet names just for ease of comparison to the formula in the other thread...Here's how it looks without the sheet names. just add DailyJobs! to the front of ALL Ranges..

=IF(ISERROR(MATCH(0,COUNTIF(B$4:B4,$B$4:$B$2199&""),0)),"",INDEX(IF(ISBLANK($B$4:$B$2199),"",$B$4:$B$2199),MATCH(0,COUNTIF(B$4:B4,$B$4:$B$2199&""),0)))

Also make sure you're entering it with CTRL + SHIFT + ENTER
After entering the formula, Highlight the cell and press F2, then
Press CTRL + SHIFT + ENTER

 

[Jonmo1]

Wait, scratch the EVERY range part...

=IF(ISERROR(MATCH(0,COUNTIF(B$4:B4,$B$4:$B$2199&""),0)),"",INDEX(IF(ISBLANK($B$4:$B$2199),"",$B$4:$B$2199),MATCH(0,COUNTIF(B$4:B4,$B$4:$B$2199&""),0)))

The bolded parts should refer to the sheet you're putting the new unique list on.

So on the sheet you want to PUT the NEW unique list
in B4 you put

=DailyJobs!B4

Then in B5 you put this

=IF(ISERROR(MATCH(0,COUNTIF(B$4:B4,DailyJobs!$B$4:$B$2199&""),0)),"",INDEX(IF(ISBLANK(DailyJobs!$B$4:$B$2199),"",DailyJobs!$B$4:$B$2199),MATCH(0,COUNTIF(B$4:B4,DailyJobs!$B$4:$B$2199&""),0)))

 

[kishorkhanal]

Wait, scratch the EVERY range part...

=IF(ISERROR(MATCH(0,COUNTIF(B$4:B4,$B$4:$B$2199&""),0)),"",INDEX(IF(ISBLANK($B$4:$B$2199),"",$B$4:$B$2199),MATCH(0,COUNTIF(B$4:B4,$B$4:$B$2199&""),0)))

The bolded parts should refer to the sheet you're putting the new unique list on.

So on the sheet you want to PUT the NEW unique list
in B4 you put

=DailyJobs!B4

Then in B5 you put this

=IF(ISERROR(MATCH(0,COUNTIF(B$4:B4,DailyJobs!$B$4:$B$2199&""),0)),"",INDEX(IF(ISBLANK(DailyJobs!$B$4:$B$2199),"",DailyJobs!$B$4:$B$2199),MATCH(0,COUNTIF(B$4:B4,DailyJobs!$B$4:$B$2199&""),0)))

I did exactly as you said and copied down the formula but it does not work. It gives null value in every cell.

 

[Jonmo1]

Well it works for me...

Here's the sheet named DailyJobs, range B4:B20 (i only went to row 20)

#VALUE!

 

[kishorkhanal]

Thank you very much for your help.