• 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.

List comparison

P

Plutothedog

New Member
I have two lists of names that has first name, middle name and last name. I am trying to compare to see if a user from 1st list is in the 2nd list. any easy way to do this? more like a user audit for our dept.

any help is greatly appreciated. kind of in a limbo :(
 
Sample workbook please. And version of Excel you have.

This sort of operation is very easy to do with PowerQuery if you have access to it.
 
Here you go. Both lists converted to table.

Both formula method and PowerQuery method on the workbook attached.

1. Formula - Match column added to App User list (Showing corresponding Row# when match is found in Total User List).
2. PowerQuery - See Sheet2 & 3 queries.


Transformation applied to both queries from original table
  • Transformed 3 columns to UPPERCASE. This is done since PowerQuery merge is case sensitive
  • Duplicated 3 columns and merged to concatenate string, ignoring "null" values. This step is needed since some last name appear in Middle Name column of App table and because of null values.
Resulting output is List of Names present in both tables.
 

Attachments

  • TestWorksheet.xlsx
    151.1 KB · Views: 6
Hi Chihiro, when i tried to edit the formula its returning with an error "the Name that you entered is not valid. i have tried to execute with ctrl+shift+enter key as well but still no go.

any suggestions?

Thanks again!
 
Is this in your file or the file I uploaded?

Post your formula exactly as you entered it (before ctrl+shift+enter).
 
This is what i am putting in my worksheet. i have duplicated the name manager as well.

=IFERROR(MATCH([@First Name]&[@Middle Name]&[@Last Name],tblUser[First Name]&tblUser[Middle Name]&tblUser[Last Name],0),"")
 
Check the column name. If there is space, syntax will be [@[First Name]] instead of [@FirstName].

Also, named range for tblUser looks a bit off.
 
Hi Chihiro,

here is the my worksheet and formula. i am not sure if i am doing something wrong but it seems to be doing the same thing.

Formulas that i tried

=IFERROR(MATCH([@FirstName]&[@Middle]&[@LastName],tblUser[First Name]&tblUser[Middle Name]&tblUser[Last Name],0),"")



=IFERROR(MATCH([@[First Name]]&[@[Middle Name]]&[@[Last Name]],tblUser[First Name]&tblUser[Middle Name]&tblUser[Last Name],0),"")

Again i can't thank you enough for your help!
 

Attachments

  • TestCompare101916.xlsx
    481.2 KB · Views: 1
Ah, I see your issue. Formula is meant for structured table. Not just the named range.

Also, which direction are you comparing? Data range to right of Match column contains duplicates. Are your trying to find matching row# from left data range for entries in right? Or is it opposite?
 
I got ya. so here is what i am trying to do in the attached worksheet. Columns (A to H) are appusers with different levels of access. Columns (K to AB) are the total population.

I am trying to run the comparison for all the records from left side(blue) to right(red).

Thanks,
 

Attachments

  • PDSEC-20161019.xlsx
    968 KB · Views: 2
There are 2 duplicates found in table to the right.

How should these be differentiated?

Ex:
John A Almanza occurs twice, different EE#
Sharon D Freeman occurs twice, different EE#
 
Chihiro!!! thank you very much for your help!!! i really appreciated!!! you got me exactly what i needed!! your a lifesaver!!!!

Thanks again for your help!
 
You must log in or register to reply here.
Back
Top