+ Reply to Thread
Results 1 to 17 of 17

Compare two lists and generate a new list with what is different from the first list

  1. #1
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Compare two lists and generate a new list with what is different from the first list

    Cross-posted at https://www.mrexcel.com/board/thread...-list.1170504/

    Hello, so as the title says, I am looking to set up an audit template that I can use to quickly find the differences between two separate lists that will be pasted in the template.

    I attached a small sample file containing the idea of what I am trying to do. In columns L thru N, I want it to generate a list of what is different in the Database pasted list (columns F thru I) from the Master Pasted list (columns A thru D)

    So in this specific case and per attachment, John 1 has a different position code and pay rate in the Database list Pasted section from the master list pasted section. His info should appear in the Variance List.
    John 2 has the same pay rate in both lists but his position code differs. His info should appear in the variance list
    John 3 has the same position code in both lists but has a different pay rate. His info should appear in the variance list

    I've seen something done like this before with the Aggregate function which I unfortunately am not at all familiar with. I'm wondering if someone here might have knowledge of it to help me out in this scenario?

    I have another audit type that I do periodically that compares the same amount of fields of info. If I can get a solution to this one, I believe I should be able to apply it to that second one.

    Any help on this would be appreciated. Please and thank you.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 05-12-2021 at 10:45 AM.

  2. #2
    Forum Contributor
    Join Date
    05-03-2021
    Location
    Nashville
    MS-Off Ver
    MS Office 365
    Posts
    184

    Re: Compare two lists and generate a new list with what is different from the first list

    Which is the System of record?
    The master list or the Database list?

    I compared the two lists but depending on the system of record, we can shorten the formula down
    Attached Files Attached Files

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Compare two lists and generate a new list with what is different from the first list

    I have added the cross-post link to your post. Before going any further, please take the time to review our rules. There aren't many, and they are all important.

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: Compare two lists and generate a new list with what is different from the first list

    Hi there! So actually the system of record would be the Database List. That's the one we ourselves maintain. The master list we have no control over directly as it is an online system maintained somewhere else. We simply submit info to make changes for it according to new info we enter in the database list. So basically we have to verify that the changes have been made in the online system by exporting the file to excel format which I can then copy/paste relevant fields into the audit template. I hope that all made sense.

    Also, I am unable to post links to the other forum i had posted this on. It is not allowing me to do so.
    Last edited by PitchNinja; 05-12-2021 at 10:52 AM.

  5. #5
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: Compare two lists and generate a new list with what is different from the first list

    Quote Originally Posted by thenewkidd View Post
    Which is the System of record?
    The master list or the Database list?

    I compared the two lists but depending on the system of record, we can shorten the formula down
    So I can see that this works when everything is aligned exactly in the same row. However, when I cut and paste one of the names and swap it to the bottom, I get an error in the formula where that name was. I'm looking ideally to do as little to no sorting as possible so this could just simply be a quick audit - that I could paste both sets of data and it wouldn't matter what rows their names appear in. If it would be needed to make it easier, I am willing to just do an audit of one field type at a time and then once that is complete, simply paste a new field over it to then audit that.

    So in this case, Just generate any ID's and position codes in the Variance List section whose position codes are different from the Database Posted List section. I can have a formula do a lookup of their names and match that ID's no problem

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Compare two lists and generate a new list with what is different from the first list

    Cutting and pasting cells in a range referenced by formulas often produces errors, however I feel that thenewkidd's formulas will do what you want.
    Try the following with the file attached to post #2.
    Select Cell C2 and then select Sort and Filter.
    Sort A to Z
    The Names and corresponding data will now be relatively random, however the formulas work.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: Compare two lists and generate a new list with what is different from the first list

    Okay so as it turns out, I am unable to have it do a lookup to yield the name of the person associated with ID because it is telling me there is a circular reference. I changed the formula slightly in the ID column to :

    =IF(VLOOKUP($K3,$A$2:$D$28,2,FALSE)=VLOOKUP($K3,$F$2:$I$28,2,FALSE),G3,VLOOKUP($K3,$A$2:$D$28,2,FALSE)&" OR "&VLOOKUP($K3,$F$2:$I$28,2,FALSE))

    This way instead of saying "Match" it will say the employee ID. I also tried this in column K to lookup their name:

    =INDEX(F3,MATCH(L3:L200,G3:G200,0))

    That was where I got the circular reference. How could we do it so it yields their name?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Compare two lists and generate a new list with what is different from the first list

    I don't feel that there is a way to avoid a circular reference when populating column K (name) based on column L (ID) since column L is populating based on column K.
    Perhaps we are not understanding the results that you would like. Do you want to list all of the information for each person, or only the information for those persons where a discrepancy occurs between the Master and Database lists?
    If for all persons then you could populate column K using: =F3
    If only for the persons where there is a discrepancy then we may need to reevaluate the strategy.
    Let us know if you have any questions.

  9. #9
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: Compare two lists and generate a new list with what is different from the first list

    It would only be for those who actually have a discrepancy.

    On the Mr Excel Forum, I asked for help with this but I also had another post which unfortunately I can't post the link to as this site does not allow it but basically there I had a similar situation in populating names to a third list. The difference with that one was It would generate a third list if an item from the second list was missing from the first list. Here are some formulas there for reference:

    I will attach a sample of it just in case it might spark any ideas for this scenario.

    I had asked the person who helped me with this attached sample solution if they could help me with this one but apparently you aren't allowed to do that, and I wasn't getting any answers from anyone else that fit, so I came here with the questions.
    Attached Files Attached Files
    Last edited by PitchNinja; 05-17-2021 at 01:45 PM. Reason: adding link

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Compare two lists and generate a new list with what is different from the first list

    This proposal employs a helper column (J) which may be moved and/or hidden for aesthetic purposes.
    Paste the following into cell J3 and then copy down to cell J28:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Paste the following into cell K3 and then copy over and down to cell N28:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  11. #11
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: Compare two lists and generate a new list with what is different from the first list

    Thank you very much for this. I will give it a try

    EDIT:

    Okay so I went ahead and pasted this among my actual larger sample of data since it has more names. It didn't work like this one and wasn't showing names like this sample did and I was confused as to why even after I changed the formula to make it encompass additional rows. I found a complication and I hadn't realized this until I pasted in the larger sample data. So in the first list of my actual audit template, the names appear first name first and then last name as pasted directly from that report.(why anyone would have it in that format I will never know.) But in the second list from the excel file we maintain, the names are Last name first and then first name. I didn't think of this when I changed their names to the Generic John 1 etc. and pasted them in both lists, but that is why it isn't working for the larger sample.

    I think we are maybe getting close, but that's the next hurdle currently.
    Last edited by PitchNinja; 05-18-2021 at 09:46 AM. Reason: posting results

  12. #12
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: Compare two lists and generate a new list with what is different from the first list

    If it helps any, the ID which is in both column B and G are in consistent format.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Compare two lists and generate a new list with what is different from the first list

    I had received a notification that indicated that changes to the output in columns M:N were wanted.
    To that end try pasting the following into cell M3 and then copy over and down to cell N28:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  14. #14
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: Compare two lists and generate a new list with what is different from the first list

    Quote Originally Posted by JeteMc View Post
    I had received a notification that indicated that changes to the output in columns M:N were wanted.
    To that end try pasting the following into cell M3 and then copy over and down to cell N28:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Yeah i intially posted that then edited it because I had tested it against my larger sample and it hadn't worked quite the same. We still just have the name complication with last name first and first name last on one list and vice versa on the other. If that can be fixed, I THINK we're golden. If it could even some how depend not on names but on the Employee ID's, the ID's are consistent in both lists.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Compare two lists and generate a new list with what is different from the first list

    If it helps any, the ID which is in both column B and G are in consistent format.
    Change the formula in J3 and down to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Change the formula in M3 down and over to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  16. #16
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: Compare two lists and generate a new list with what is different from the first list

    Quote Originally Posted by JeteMc View Post
    Change the formula in J3 and down to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Change the formula in M3 down and over to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Exactly it. I pasted this among my larger sample and changed the row end parameters to 300 to incorporate all the employee names/info and allow a cushion for space. I don't even have to have both sets of data in the same exact row. It's working EXACTLY how I want it to - my co-worker and I tested it and it went so much faster. You did what those in the other forum could/would not. I do thank you so very much for your help!

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Compare two lists and generate a new list with what is different from the first list

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Generate A List Joining Two Other Lists Based On Cell Input
    By sp0ck1 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-12-2019, 10:27 AM
  2. Replies: 4
    Last Post: 06-30-2014, 11:13 AM
  3. Generate multiple lists without the same last and first number of each list...
    By Dreamweaver8882004 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-06-2013, 08:49 PM
  4. Generate a list from two lists
    By ioncila in forum Excel General
    Replies: 2
    Last Post: 06-24-2012, 02:25 PM
  5. Generate List of Inactive Projects Using Two Lists
    By skoodog in forum Excel General
    Replies: 3
    Last Post: 03-27-2009, 06:11 PM
  6. Generate a list from other lists
    By dixiechick11 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-10-2008, 12:52 PM
  7. [SOLVED] Compare worksheets and generate list of missing data?
    By Minuette in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-03-2005, 09:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1