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
Here is what I want and I wanted the data in column A to be sorted serially
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
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
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,"",IFERR OR(VLOOKUP (C2,A:B,2, 0),""))
Please find attached...
Unique-Values-Sorted_v1.xlsx
=IF(IFERROR(VLOOKUP(C2,A:B
Please find attached...
Unique-Values-Sorted_v1.xlsx
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
and sorted
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
It is bit difficult to extract list with adjacent column being blank
ASKER
i was able to use this formula
IFERROR(INDEX(Sheet1!$A$2: $A$500,MAT CH(0,IF($B $1<>Sheet1 !$B$2:$B$5 00,COUNTIF ($A$2:$A2, Sheet1!$A$ 2:$A$500), ""),0)),"" )
But I need to sort the data serially. That formula will not do it.
IFERROR(INDEX(Sheet1!$A$2:
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)=FALS E), 0)),"")
=IFERROR(INDEX(A2:A9, MATCH(0, COUNTIF($C$1:C1, A2:A9)+(ISTEXT(B2:B9)=FALS
I got another approach through VBA, please copy below code and run macro ExtractUniqueList
Please find attached and press Extract IDs
Hope this helps.
Unique-Values-Sorted_v2.xlsm
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
Please find attached and press Extract IDs
Hope this helps.
Unique-Values-Sorted_v2.xlsm
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
Application.Calculation = xlCalculationAutomatic
ASKER
You are right!
It worked. Thank you so much!
It worked. Thank you so much!
ASKER
Thank you so much. I really appreciate you staying with me on this
You're Welcome! Glad it worked eventually :)
ID:
=OFFSET(Sheet1!$A$2, 0, 0, COUNT(IF(Sheet1!$A$2:$A$50
Name:
=OFFSET(Sheet1!$B$2, 0, 0, COUNT(IF(Sheet1!$B$2:$B$50
Getting Unique IDs sorted use below Array formula in C2:
=IFERROR(INDEX(ID, MATCH(0, IF(MAX(NOT(COUNTIF($C$1:C1
Confirmed with Ctrl+Shift+Enter
Getting Unique Name sorted use below Array Formula in D2:
=IFERROR(INDEX(Name, SMALL(IF(SMALL(IF(COUNTIF(
Confirmed with Ctrl+Shift+Enter
Please find attached for your reference...
Hope this helps
Unique-Values-Sorted.xlsx