Find next match+unique users

[Karthik Thandapani]

@Debraj , Ok let's do it. I would like to go ahead with your code.
Please help me to modify that to meet my requirement accordingly.
1. Please remove report type from the criteria and try pull users accordingly
2. If report ID is empty, then pls have the criteria to look for only the country & pull users

 

[Debraj]

Hi Karthik,

Please check with the below code...

* Now you can exclude any criteria, if you left that criteria empty..

Sub UniqueMultipleRow()
    Dim sourceRange As Range, CriteriaRange As Range, Output As String
    Set sourceRange = Range("A1:D13") ' Set Source Area
   Set CriteriaRange = Range("G7:I11") ' Set Criteria Area
    With sourceRange
        For I = 2 To CriteriaRange.Rows.Count
        Output = ""
            For j = 2 To sourceRange.Rows.Count
                If IIf(CriteriaRange.Cells(I, 1) = "", "", .Cells(j, 1)) & "|" & _
                   IIf(CriteriaRange.Cells(I, 2) = "", "", .Cells(j, 2)) & "|" & _
                   IIf(CriteriaRange.Cells(I, 3) = "", "", .Cells(j, 3)) = _
                        CriteriaRange.Cells(I, 1) & "|" & CriteriaRange.Cells(I, 2) & "|" & CriteriaRange.Cells(I, 3) _
                And InStr(Output & ",", "," & .Cells(j, 4) & ",") = 0 Then _
                    Output = Output & "," & .Cells(j, 4)
            Next j
            If Len(Output) > 1 Then
                arrOutput = Split(Mid(Output, 2), ",")
                CriteriaRange(1).Offset(I - 1, 4).Resize(1, UBound(arrOutput) + 1) = arrOutput
            End If
        Next I
    End With
End Sub

PS: Dont forget to clear the output range before proceed..

 

[Karthik Thandapani]

@Debraj , a quick question.
How to set criteria area to look & pull users only for the range with data/non-blank cells?
Because when I Set CriteriaRange = Range("G7:I1222") this way & say data only filled up till "G7:I70" and rest of the below cells till "I1222" pulls up data for empty criteria which is not-a-mandatory flow.
Sorry, could you please help me to set criteria dynamically only to the non-blank cells please?

 

[Debraj]

Sub UniqueMultipleRow()
    Dim sourceRange As Range, CriteriaRange As Range, Output As String
    Set sourceRange = Range("A1:D13") ' Set Source Area
   Set CriteriaRange = Range("G7").CurrentRegion ' Set Criteria Area
    With sourceRange
        For I = 2 To CriteriaRange.Rows.Count
        Output = ""
            For j = 2 To sourceRange.Rows.Count
                If IIf(CriteriaRange.Cells(I, 1) = "", "", .Cells(j, 1)) & "|" & _
                   IIf(CriteriaRange.Cells(I, 2) = "", "", .Cells(j, 2)) & "|" & _
                   IIf(CriteriaRange.Cells(I, 3) = "", "", .Cells(j, 3)) = _
                        CriteriaRange.Cells(I, 1) & "|" & CriteriaRange.Cells(I, 2) & "|" & CriteriaRange.Cells(I, 3) _
                And InStr(Output & ",", "," & .Cells(j, 4) & ",") = 0 Then _
                    Output = Output & "," & .Cells(j, 4)
            Next j
            If Len(Output) > 1 Then
                arrOutput = Split(Mid(Output, 2), ",")
                CriteriaRange(1).Offset(I - 1, 4).Resize(1, UBound(arrOutput) + 1) = arrOutput
            End If
        Next I
    End With
End Sub

Try this..

 

[Karthik Thandapani]

@Debraj , I am looking for your help!
I have attached the same file we workout out a concept. Now I have an additional requirement to pull users matching with Report IDs only. Please see the attached file.
I am trying to expand the below code snippet but it's not pulling. Can you please help? Thanks!

Sub OnlyReportID()

    Dim sourceRange As Range, CriteriaRange As Range, Output As String

Set sourceRange = Range("A1:D1000") ' Set Source Area
  Set CriteriaRange = Range("H7").CurrentRegion ' Set Criteria Area
  With sourceRange
        For I = 2 To CriteriaRange.Rows.Count
        Output = ""
            For j = 2 To sourceRange.Rows.Count
                If IIf(CriteriaRange.Cells(I, 1) = "", "", .Cells(j, 1)) = _
                  IIf(CriteriaRange.Cells(I, 2) = "", "", .Cells(j, 2)) & "|" & _
                  IIf(CriteriaRange.Cells(I, 3) = "", "", .Cells(j, 3)) = _
                        CriteriaRange.Cells(I, 2) & "|" & CriteriaRange.Cells(I, 3) _
                And InStr(Output & ",", "," & .Cells(j, 4) & ",") = 0 Then _
                    Output = Output & "," & .Cells(j, 4)
            Next j
            If Len(Output) > 1 Then
                arrOutput = Split(Mid(Output, 2), ",")
                CriteriaRange(1).Offset(I - 1, 8).Resize(1, UBound(arrOutput) + 1) = arrOutput
            End If
        Next I
    End With


End Sub

 

[Debraj]

Option Compare Text
Sub OnlyReportID()
    Dim sourceRange As Range, CriteriaRange As Range, Output As String
    Set sourceRange = Range("A1:D1000") ' Set Source Area
  Set CriteriaRange = Range("G7:H7").CurrentRegion ' Set Criteria Area
   With sourceRange
        For I = 2 To CriteriaRange.Rows.Count
        Output = ""
            For j = 2 To sourceRange.Rows.Count
                If .Cells(j, 2) = CriteriaRange.Cells(I, 2) _
                And InStr(Output & ",", "," & .Cells(j, 4) & ",") = 0 Then _
                    Output = Output & "," & .Cells(j, 4)
            Next j
            If Len(Output) > 1 Then
                arrOutput = Split(Mid(Output, 2), ",")
                CriteriaRange(1).Offset(I - 1, 4).Resize(1, UBound(arrOutput) + 1) = arrOutput
            End If
        Next I
    End With
End Sub

try this...
and please feedback.. as we are also waiting to see the response..
its motivates us to help you again and again..

 

[Karthik Thandapani]

@Debraj , the recent code you've provided-it works like a breeze. What more can I say.
This is a genius stuff! Thanks Debraj!

 

[Debraj]

Thanks for the feedback..