Solved How do I compare two lists in excel to find differences?

September 18, 2018 at 08:25:33
Specs: Windows 10, Ryzen 1600, 32GB Dominator RAM
So Basically I am tracking who is and who is not logging into a web based system .

On xls spreadsheet A, I have a list of all individuals ( names emails ) of around 300 Users

On S[spreadsheet B I have a list of all individual ( repeating ) instances of 'log in ' . Each time a user logs in , a record ( row ) is created with a columns of the user's name and emails . There are thousands of log ins each month

Basically I want to quickly highlight the users on spreadsheet A , who have not appeared on the 'log in ' spreadsheet B.

So far I have been doing this manually but can take hours to complete . I manually check each username using find .

Is there a quick way of doing this ?

message edited by 90Ninety


See More: How do I compare two lists in excel to find differences?

Reply ↓  Report •

✔ Best Answer
September 19, 2018 at 06:49:21
Take a look at your post now. While it can be a bit cumbersome, all it takes is some manual editing, previewing, more editing, etc. to add the Column letters and Row numbers as requested. Column letters and Row numbers help us help you. We can be more specific with our suggestions if we understand the layout of the data.

Regarding the VBA portion of the How-To, that does not apply to this situation. The purpose of the How-To is to explain the use of the pre tags for both example data and VBA code. In your case, only the example data portion of the How-To applies.

Based on the example data that you provided, it seems like VLOOKUP will fulfill your needs.

Using the Sheet names that you provided (A & B), and the Column letters and Row numbers that I added, this should work for you:

Put this in Sheet B C2 and drag it down. It will return "No Login Found" for any name that does not appear in Sheet A, A2:A11.

=IF(ISNA(VLOOKUP(A2,A!$A$2:$A$11,1,0)),"No Login Found","")

Explanation:

The VLOOKUP will search Sheet A A2:A11 for each name in Sheet B Column A. If a name is not found, VLOOKUP will return a #N/A error. The ISNA function will capture that error and return "True". Since the ISNA function is True, the IF will be True and it will return the value_if_true argument, "No Login Found". If the ISNA is false, then the IF will return a blank cell.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
September 18, 2018 at 08:33:26
There are a number of built-in lookup functions that should help you accomplish your goal. VLOOKUP, HLOOKUP, LOOKUP, INDEX(MATCH(...), etc.

If we knew more about your data layout, we might be able to offer a solution. If you could tell us what columns are used for the data that you want to "match" that would help. Obviously, exact matches between the 2 lists is best, but we know that that may not always be possible.

If you need to post example (non-personal) data to help us out, please click on the following How-To link and read those instructions. Please be sure to include Column letters and Row numbers as shown in the example.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
September 19, 2018 at 01:55:33
Sheet A

         A                       B                     C        D            E
1       User                   Email                 Date    Sessions      Views
2    John Smith          jhonsmith@email.com        19-Sep	2            4
3    Peter Jones         peterjones@email.com       19-Sep	3            6
4    Tom Woods           tomwoods@email.com         19-Sep	1            2
5    Jill Remington      llremington@email.com      19-Sep	4            8
6    Remi Shar           remishar@email.com         19-Sep	3            6
7    John smith          jhonsmith@email.com        20-Sep	4            6
8    Peter Jones         peterjones@email.com       20-Sep	2            3
9    Tom Woods           omwoods@email.com          20-Sep	4            9
10   Jill Remington      jillremington@email.com    20-Sep	3            6
11   Remi Shar           remishar@email.com         20-Sep	10           5
			

Sheet B

         A                       B                 
1      User	               Email 
2   Jill Remington       jillremington@email.com
3   John Smith           jhonsmith@email.com
4   Peter Jones          peterjones@email.com
5   Remi Shar            remishar@email.com
6   Tom Woods            tomwoods@email.com
6   Joe Cherry           joecherry@email.com


Okay , here are two sheet examples which are conceptually the same as what I am comparing . On sheet A I have a list instances of log in per person per day

Sheet B is just a list of all users

In principle I am trying to figure who has not logged in per period ( from day 1 to day 2 ) . For example between the dates of 19th September to the 20th September , I can see that Joe Cherry has not logged in .


Reply ↓  Report •

#3
September 19, 2018 at 03:20:39
From my previous post:

"Please be sure to include Column letters and Row numbers as shown in the example"

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
September 19, 2018 at 03:42:01
Thats the best I could do with displaying a spreadsheet , I did not really understand the VBA code part sorry

Reply ↓  Report •

#5
September 19, 2018 at 06:49:21
✔ Best Answer
Take a look at your post now. While it can be a bit cumbersome, all it takes is some manual editing, previewing, more editing, etc. to add the Column letters and Row numbers as requested. Column letters and Row numbers help us help you. We can be more specific with our suggestions if we understand the layout of the data.

Regarding the VBA portion of the How-To, that does not apply to this situation. The purpose of the How-To is to explain the use of the pre tags for both example data and VBA code. In your case, only the example data portion of the How-To applies.

Based on the example data that you provided, it seems like VLOOKUP will fulfill your needs.

Using the Sheet names that you provided (A & B), and the Column letters and Row numbers that I added, this should work for you:

Put this in Sheet B C2 and drag it down. It will return "No Login Found" for any name that does not appear in Sheet A, A2:A11.

=IF(ISNA(VLOOKUP(A2,A!$A$2:$A$11,1,0)),"No Login Found","")

Explanation:

The VLOOKUP will search Sheet A A2:A11 for each name in Sheet B Column A. If a name is not found, VLOOKUP will return a #N/A error. The ISNA function will capture that error and return "True". Since the ISNA function is True, the IF will be True and it will return the value_if_true argument, "No Login Found". If the ISNA is false, then the IF will return a blank cell.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question