Link to home
Start Free TrialLog in
Avatar of fb1990
fb1990

asked on

Extract Unique Based on Criteria in Another Column in Excel

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

Avatar of Shums Faruk
Shums Faruk
Flag of India image

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
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
Avatar of fb1990
fb1990

ASKER

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
It is bit difficult to extract list with adjacent column being blank
Avatar of fb1990

ASKER

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.
This formula will not give you unique list, did you checked it gives 10002 thrice
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)),"")
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
Avatar of fb1990

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
or add this line after Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
Avatar of fb1990

ASKER

You are right!

It worked.  Thank you so much!
Avatar of fb1990

ASKER

Thank you so much.  I really appreciate you staying with me on this
You're Welcome! Glad it worked eventually :)