• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find next match+unique users

K

Karthik Thandapani

Member
Hi,

I have a master table where the actual data is. I have a table where I need to pull users depends on the matching criteria. For an example, matching(country+ReportID+ReportType)=User1, matching user2, matching user3.

Also the users may repeat. I want to find the next unique user from the master table. Can anyone please help.

Please see the attached file.

Thanks,
Karthik
 

Attachments

  • next match+unique record.xlsx
    9.4 KB · Views: 7
@azumi , yes it works really cool. Please note, I'm extending the table to 4k records. Now, the file is very slow and it's taking lot of time. Do you have any solution to speed up the file+upgrade the array formulas?

Please advise. This would be a great help. Thanks in advance!
 
@Somendra Misra , Is there a way to do with advanced filter? Or you really didn't get what question I've raised?
Please see the attached file above. I'm trying to look up user1, next user, user3,.... for a matching pattern.
So I've created a dropdown with values & now trying to expand to 4k records to find the match & pull users.
@azumi cracked it and gave array formula which worked cool but when I try expanding, excel becomes too slow & can't even responding.

Now, do you think we can achieve this by advanced filter? If you still say yes, can you help?

Or do you think we can do this using macros? awaiting for your response
 
@Karthik Thandapani

Yeah Advanced filter combine with macro can be fast and bring the desired output in your required format. But try below array formula in K8 and copy right and see the performance.

=INDEX($D$2:$D$4000,AGGREGATE(15,6,IF(FREQUENCY(IFERROR(MATCH(IF($G$8=$A$2:$A$4000,IF($H$8=$B$2:$B$4000,IF($I$8=$C$2:$C$4000,$D$2:$D$4000))),$D$2:$D$4000,0),"e"),ROW($D$2:$D$4000)-ROW($D$2)+1),ROW($D$2:$D$4000)-ROW($D$2)+1),COLUMNS($K8:K8)))

Enter with Ctrl+Shift+Enter.

Regards,
 
Hi Karthik,

See the attached file. If this meets your requirement.

Regards,
 

Attachments

  • next match+unique record (1).xlsm
    266.8 KB · Views: 3
Just a small correction on the last file.Check this one.

Regards,
 

Attachments

  • next match+unique record (1).xlsm
    266.9 KB · Views: 4
@Somendra Misra , excellent!
a small update, if I want to include the report type matching the criteria? How can I change the range in VB. I tried doing but its throwing up an error at ds.range("k" & i).select
can you please look into it & modify. I need to match on country & Report ID. Please exclude Report type. THanks
 
@Somendra Misra , there is a problem. I moved both the sheets to a new workbook which I'm working on. Data is all the same but it is throwing up an error. Is this something we can fix? when I try to debug, it is automatically opening up the old workbook "next-match-unique-record-2-xlsm"

Please debug this!
 
Hi Karthik!

Can you please check the attached..
Let us know the feedback..

Code: 
Sub UniqueMultipleRow()
    Dim sourceRange As Range, CriteriaRange As Range, Output As String
    Set sourceRange = Range("A1:D19999") ' Set Source Area
    Set CriteriaRange = Range("G7:I24") ' Set Criteria Area
   
    With sourceRange
        For I = 2 To CriteriaRange.Rows.Count
        Output = ""
            For j = 2 To sourceRange.Rows.Count
                If .Cells(j, 1) & "|" & .Cells(j, 2) & "|" & .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)) = arrOutput
            End If
        Next I
    End With
End Sub
 

Attachments

  • next match+unique record (ForLoop Approach).xlsm
    506.5 KB · Views: 5
@Debraj , a quick question

Set cs = ThisWorkbook.Worksheets("Sheet2")
Set ds = ThisWorkbook.Worksheets("Sheet1")

how to convert the above snippet to a particular workbook name ?

Say I want to run this in "home" page. Thanks
 
@Debraj , hope you're doing great there.
Can you please help me with the below error with the code I've received from here.
Please note, there are 2 types of errors showing up;

1. ds.Range("K" & i).Select

2. Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Particularly when we have very few/one in Sheet2"D5"

Can you please fix this? Thanks a lot in advance!
 
Hi Karthik,

are you using my provided code or Somendra's one.. Cause.. :confused:

Please try my code.. hope it will work for you..

Or please let us know.. if you specially need Som's code.. we will modify that one also as per requirement.. :)
 
You must log in or register to reply here.
Back
Top