We help IT Professionals succeed at work.

Extract Unique Based on Criteria in Another Column in Excel

1,415 Views
Last Modified: 2017-03-25
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
10015	Ade Kambi
10092	
10015	Ade Kambi
10002	Charlie Knowles
10041	James Banks
10002	Charlie Knowles

Open in new window


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

ID	Name
10002	Charlie Knowles
10015	Ade Kambi
10041	James Banks

Open in new window

Comment
Watch Question

ShumsManaging 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

Please find attached for your reference...
Hope this helps
Unique-Values-Sorted.xlsx
ShumsManaging 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),""))
Please find attached...
Unique-Values-Sorted_v1.xlsx

Author

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

Open in new window


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

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

Author

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.
ShumsManaging 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
ShumsManaging 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)),"")
ShumsManaging 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
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Ws.Range("C1").Value = "ID"
Ws.Range("D1").Value = "Name"
Ws.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Open in new window


Please find attached and press Extract IDs

Hope this helps.
Unique-Values-Sorted_v2.xlsm

Author

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

Open in new window

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)
UNLOCK SOLUTION
ShumsManaging 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.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

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

Author

Commented:
You are right!

It worked.  Thank you so much!

Author

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

Commented:
You're Welcome! Glad it worked eventually :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.