We help IT Professionals succeed at work.

# Extract Unique Based on Criteria in Another Column in Excel

on
1,415 Views
Hello EE,

Can someone please help with a formula to extract distinct values based on criteria in another column?  I want to extract unique values where the data in the name column is not blank.  Here is my example data and the expected result

``````ID	Name
10092
10041	James Banks
10092
10002	Charlie Knowles
10041	James Banks
10002	Charlie Knowles
``````

Here is what I want and I wanted the data in column A to be sorted serially

``````ID	Name
10002	Charlie Knowles
10041	James Banks
``````
Comment
Watch Question

## View Solution Only

Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
This is gonna be long formula, first create two Name Range Assuming your data is starting from A2:

ID:
=OFFSET(Sheet1!\$A\$2, 0, 0, COUNT(IF(Sheet1!\$A\$2:\$A\$500="", "", 1)), 1)

Name:
=OFFSET(Sheet1!\$B\$2, 0, 0, COUNT(IF(Sheet1!\$B\$2:\$B\$500="", "", 1)), 1)

Getting Unique IDs sorted use below Array formula in C2:
=IFERROR(INDEX(ID, MATCH(0, IF(MAX(NOT(COUNTIF(\$C\$1:C1, ID))*(COUNTIF(ID, ">"&ID)+1))=(COUNTIF(ID, ">"&ID)+1), 0, 1), 0)),"")
Confirmed with Ctrl+Shift+Enter

Getting Unique Name sorted use below Array Formula in D2:
=IFERROR(INDEX(Name, SMALL(IF(SMALL(IF(COUNTIF(\$D\$1:D1, Name)+ISBLANK(Name)=0, COUNTIF(Name, "<"&Name)+1, ""), 1)=IF(ISBLANK(Name), "", COUNTIF(Name, "<"&Name)+1), ROW(Name)-MIN(ROW(Name))+1), 1), MATCH(MIN(IF(COUNTIF(\$D\$1:D1, Name)+ISBLANK(Name)>0, "", COUNTIF(Name, "<"&Name)+1)), INDEX(IF(ISBLANK(Name), "", COUNTIF(Name, "<"&Name)+1), SMALL(IF(SMALL(IF(COUNTIF(\$D\$1:D1, Name)+ISBLANK(Name)=0, COUNTIF(Name, "<"&Name)+1, ""), 1)=IF(ISBLANK(Name), "", COUNTIF(Name, "<"&Name)+1), ROW(Name)-MIN(ROW(Name))+1), 1), , 1), 0), 1),"")
Confirmed with Ctrl+Shift+Enter

Hope this helps
Unique-Values-Sorted.xlsx
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
If you don't want to make unique list sorted for Name then after extracting unique sorted list of ID, you can use below formula for Name:
=IF(IFERROR(VLOOKUP(C2,A:B,2,0),"")=0,"",IFERROR(VLOOKUP(C2,A:B,2,0),""))
Unique-Values-Sorted_v1.xlsx

Commented:
Hello Shums,

Thanks for your help again.  I actually just need the ID field where column B is not blank?  Is this possible?  I only need
``````10002
10015
10041
``````

and sorted
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
It is bit difficult to extract list with adjacent column being blank

Commented:
i was able to use this formula

IFERROR(INDEX(Sheet1!\$A\$2:\$A\$500,MATCH(0,IF(\$B\$1<>Sheet1!\$B\$2:\$B\$500,COUNTIF(\$A\$2:\$A2,Sheet1!\$A\$2:\$A\$500),""),0)),"")

But I need to sort the data serially.  That formula will not do it.
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
This formula will not give you unique list, did you checked it gives 10002 thrice
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
For getting unique ID list you can try below array formula in C2:
=IFERROR(INDEX(A2:A9, MATCH(0, COUNTIF(\$C\$1:C1, A2:A9)+(ISTEXT(B2:B9)=FALSE), 0)),"")
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I got another approach through VBA, please copy below code and run macro ExtractUniqueList
``````Sub ExtractUniqueList()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("Sheet1")
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Columns("C:D").Delete
Ws.Range("C2").FormulaArray = "=IFERROR(INDEX(ID, MATCH(0, COUNTIF(\$C\$1:C1, ID)+(ISTEXT(Name)=FALSE), 0)),"""")"
Ws.Range("C2:C" & LR).FillDown
Ws.Range("C2:C" & LR).Name = "Helper"
Ws.Range("D2").FormulaArray = "=IFERROR(INDEX(Helper, MATCH(0, IF(MAX(NOT(COUNTIF(\$D\$1:D1, Helper))*(COUNTIF(Helper, "">"" & Helper)+1))=(COUNTIF(Helper, "">"" & Helper)+1), 0, 1), 0)),"""")"
Ws.Range("D2:D" & LR).FillDown
Ws.Range("E2:E" & LR).FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,C1:C2,2,0),"""")"
Ws.Range("C2:E" & LR).Value = Ws.Range("C2:E" & LR).Value
Ws.Columns(3).Delete
With Ws.Columns("C:C")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Ws.Range("C1").Value = "ID"
Ws.Range("D1").Value = "Name"
Ws.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
``````

Please find attached and press Extract IDs

Hope this helps.
Unique-Values-Sorted_v2.xlsm

Commented:
Uhm.  For some reason, when i press the Extract Ids, I got this

``````10041	James Banks
10041	James Banks
10041	James Banks
10041	James Banks
10041	James Banks
10041	James Banks
10041	James Banks
10041	James Banks
``````
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I think your workbook is not calculating the formula automatically, you need to change the setting as per your version:
2003: Tools > Options > Calculation > Calculation > Automatic.
2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.
2010 and 2013: File > Options > Formulas > Workbook Calculation > Automatic.
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
or add this line after Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

Commented:
You are right!

It worked.  Thank you so much!

Commented:
Thank you so much.  I really appreciate you staying with me on this
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome! Glad it worked eventually :)
Unlock the solution to this question.