Apply filter based on a condition and predict the matching ID

[Kevin8819]

Hi everyone,

Great if anyone can help with this. I have two sheets,

1) Data - Contains data of pupil from different countries in different age groups with their unique IDs

2) Condition - Contains different combination of countries and age group

Expected output - Looking to filter "data" sheet based on the criteria in "Condition" sheet and predict the matching unique IDs for different condition

Test workbook

P.S. I have to this on over 150K rows and would only need 5 matching IDs for every test.

Any help will be greatly appreciated

Thanks
Kane

 

[Lukasier]

I can write VBA code for you if that's ok.

For now I have no clue how to achive this with functions, filters etc

 

[Lukasier]

Well, it's not the most beautiful code I've written, but it works just fine

Sub Filter()

Dim FilterRange As Range
Dim LookRow As Range
Dim Cell As Range
Dim Filter1 As String, Filter2 As String, Filter3 As String


Set FilterRange = Worksheets("Condition").Range("B3:L" & Worksheets("Condition").Range("A3").End(xlDown).Row)

For Each LookRow In FilterRange.Rows
    For Each Cell In LookRow.Cells
    
        If Cell.Value = "Y" Then
            If Cell.Column >= 2 And Cell.Column <= 6 Then
                Filter1 = Worksheets("Condition").Cells(2, Cell.Column).Value
                ElseIf Cell.Column >= 7 And Cell.Column <= 8 Then
                Filter2 = Worksheets("Condition").Cells(2, Cell.Column).Value
                ElseIf Cell.Column >= 9 And Cell.Column <= 12 Then
                Filter3 = Worksheets("Condition").Cells(2, Cell.Column).Value
            End If
        End If
    
    Next Cell
On Error Resume Next
    If Filter1 <> "" Then
        Worksheets("Data").Range("A1:D" & Worksheets("Data").Range("A1").End(xlDown).Row).AutoFilter Field:=2, Criteria1:=Filter1
    End If
    If Filter2 <> "" Then
        Worksheets("Data").Range("A1:D" & Worksheets("Data").Range("A1").End(xlDown).Row).AutoFilter Field:=3, Criteria1:=Filter2
    End If
    If Filter3 <> "" Then
        Worksheets("Data").Range("A1:D" & Worksheets("Data").Range("A1").End(xlDown).Row).AutoFilterr Field:=4, Criteria1:=Filter3
    End If
    
    Worksheets("Data").Range("A1").CurrentRegion.Copy Worksheets("Condition").Range("Z1")
    
    Worksheets("Condition").Range("Z2:Z" & Worksheets("Condition").Range("Z1").End(xlDown).Row).Copy
    Worksheets("Condition").Cells(LookRow.Row, 14).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Worksheets("Data").ShowAllData
    Worksheets("Condition").Columns("Z:AC").ClearContents
    
    

Next LookRow




End Sub

 

[Kevin8819]

Hi @Lukasier,

Many thanks for your reply.

Set FilterRange = Worksheets("Condition").Range("B3:L" & Worksheets("Condition").Range("A3").End(xlDown).Row)

Should the above refer to B3:M instead of B3:L?

Also, it does not produce any output for me yet, but, provides me a idea of how to develop the macro further. The other alternative I thought was to

1. For each row in "Conditions" look at the conditions and set a variable for each of the condition(when its true)
2. Then apply autofilter to the "Data" sheet as a whole

Do you think the method you have suggested(when we apply auto filter multiple times) or the above would work faster as I need to deal with over 150K rows and lot more conditions than I have represented here in the sample workbook.

Again, thanks a ton for your time on this.

Thanks
Kane

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

Could you give us the sample data from each sheet again using XL2BB to make it easier for helpers by not having to manually type out sample data to test with?

 

[Peter_SSs]

... would only need 5 matching IDs for every test.

Can you estimate how many IDs might actually match all the conditions for a single row on the 'Condition' as a maximum? Could it be as many as 300?

Are you looking for a formula/vba/power query solution?

 

[Kevin8819]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

Could you give us the sample data from each sheet again using XL2BB to make it easier for helpers by not having to manually type out sample data to test with?

Hi Peter,

• Have updated the account details
• I have added a link to the test file(G drive) in the original post

Thanks
Kane

 

[Kevin8819]

Can you estimate how many IDs might actually match all the conditions for a single row on the 'Condition' as a maximum? Could it be as many as 300?

Are you looking for a formula/vba/power query solution?

Yes, there might be more than 300 matching IDs (Estimate could be 10s of thousands for simple scenarios). But, I would just need first 5 matching IDs for every condition. Thanks for your time on this.

Thanks
Kane

 

[Kevin8819]

Also, to note, there might not be any matching IDs for a specific condition.

 

[Peter_SSs]

• Have updated the account details
• I have added a link to the test file(G drive) in the original post

Thanks for updating your profile.
Sorry, I had missed the link. However, I still recommend investigating XL2BB as quite a number of helpers do not download files from an unknown source.

I did add an extra question to post #6 but that may have been after you had already looked at it. Therefore I am providing two options.

BTW, both options would have been a bit simpler if you had M/F or Male/Female in both sheets, not one of each.

Formula
IF your version of Excel 365 has the FILTER function, you could try the formula in column O below, though I'm not sure how it will go (performance wise) with your quite large ranges.
I had to take a different approach here because of the large number of possible ID matches (>300)

VBA
If you prefer a vba approach you could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function IDs(rData As Range, rHdrs As Range, rCond As Range) As String
  Dim a As Variant
  Dim Cond(1 To 3) As String
  Dim i As Long, j As Long, k As Long, uba2 As Long

  a = rData.Value
  uba2 = UBound(a, 2)
  For i = 1 To rCond.Count
    If Len(rHdrs.Cells(0, i).Value) > 0 Then k = k + 1
    If Len(rCond.Cells(i).Value) > 0 Then Cond(k) = rHdrs.Cells(i).Value
    If k = 2 Then Cond(k) = Left(Cond(k), 1)
  Next i
  For i = 1 To UBound(a)
    For j = 2 To uba2
      If a(i, j) <> Cond(j - 1) And Cond(j - 1) <> "" Then Exit For
    Next j
    If j = uba2 + 1 Then IDs = IDs & ", " & a(i, 1)
  Next i
  IDs = Mid(IDs, 3)
End Function

Kevin8819 Testworkbook.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Country					Sex		Age group
2		USA	England	Turkey	India	Dubai	Male	Female	20-25	26-30	31-35	36-40		Formula	UDF
3	Test1	Y						Y				Y		135566788	135566788
4	Test2	Y					Y			Y				135566788	135566788
5	Test3				Y							Y		13456718, 139996788	13456718, 139996788
6	Test4				Y							Y		13456718, 139996788	13456718, 139996788
7	Test5		Y				Y			Y				13456788	13456788
8	Test6		Y					Y		Y				1345326788	1345326788
9	Test7			Y			Y			Y				376768868	376768868
10	Test8					Y				Y				138886788	138886788
11	Test9					Y						Y		1348906788	1348906788
12	Test10	Y					Y		Y
Condition

Cell Formulas
Range	Formula
O3:O12	O3	=IFERROR(TEXTJOIN(", ",1,INDEX(FILTER(Data!B$2:B$10,(Data!C$2:C$10=FILTER(C$2:G$2,C3:G3="Y"))*(IFERROR(Data!D$2:D$10=LEFT(FILTER(H$2:I$2,H3:I3="Y"),1),1))*(Data!E$2:E$10=FILTER(J$2:M$2,J3:M3="Y"))),SEQUENCE(MIN(5,ROWS(FILTER(Data!B$2:B$10,(Data!C$2:C$10=FILTER(C$2:G$2,C3:G3="Y"))*(IFERROR(Data!D$2:D$10=LEFT(FILTER(H$2:I$2,H3:I3="Y"),1),1))*(Data!E$2:E$10=FILTER(J$2:M$2,J3:M3="Y")))))))),"")
P3:P12	P3	=IDs(Data!B$2:E$10,C$2:M$2,C3:M3)