compare two excel sheets and show missing data in a new sheet

[jhonnyexcel]

Hi guys,

I really need some help on this.

I want to compare 2 excel sheets and find out
the missing user data, not only the name, but also the full details.
The titles of these 2 sheets are the same and it has 13 columns.

And I would like to show the results in a new sheet.

for instance;
users are using 2 different learning systems, lms & lso.
So, I would like to compare the January & February list (based on the USER ID)and
the results should show 'LSO' users who didn't appear in the previous month. (this case January)

cheers
-Jhonny

[Glenn Kennedy]

Are those real names and email addresses??? If so, you should really remove them, unless they're spam lovers....

[jhonnyexcel]

thanks glenn
will do it right now

[Beamernsw]

Hi jhonny, this example uses 2 helper columns that look up the employee ID's and list them if they didn't appear in the previous month.

[MarvinP]

Hi Jhonny,

I have a different answer. It seemed to me you wanted to append Jan and Feb sheets and then do a count on "Local/CHRIS employee ID". If the count was 2 then they were in the other month. If the count was only 1 then they were in Jan and not Feb OR Feb and not Jan. I could have simply copied Feb and pasted it on the bottom of Jan but I instead used a newer feature in Excel called Power Query where you can append tables. See the result on Sheet3. I then created a new column with a formula to count "Local/CHRIS employee ID" and if it was only one it was unique. Is this what you wanted?

[jhonnyexcel]

thanks beamernsw ... what if I change the months.
Because I'm planning keep this as a template.

[MarvinP]

Hi Beamernsw,

Does you workbook show people who were IN Jan and not in Feb? I read this problem as people who appeared only once between the two months. That would be new people in Feb and people who were in Jan and not in Feb.

[jhonnyexcel]

thanks MarvinP...!!!

that's not really what I need.
I may have the same users or new users in the forthcoming months.

So, I would like to compare the sheets and find out the missing names (based on the USER ID)
and the results should show 'LSO' users who didn't appear in the previous month.
Because I would like to keep this as a template for future.

[Beamernsw]

I would suggest, to make it easier, rename the sheets to "This Month" and "Last Month".

Then just for getting this list, select all the data from "Current Month" and paste values over "Last Month", then do the same for the current sheet (say March) and paste it over the "Current Month" sheet.

Sounds confusing but should only take 20 or 30 seconds to do.

[Beamernsw]

Actually, you could use the following in the formulas with indirect to get the sheet names.

=TEXT(TODAY(),"mmm") <----- Current Month
=TEXT(EOMONTH(TODAY(),-1),"mmm") <----- Last Month
=TEXT(EOMONTH(TODAY(),-2),"mmm") <----- Two Months Ago

But I believe that both INDIRECT and TODAY are volatile and using them in nearly every cell would probably slow your workbook down incredibly.

[sktneer]

Here is a VBA workaround, please find the attached workbook and click the button on the Magic Button Sheet to create a Report Sheet with the comparison between two sheets.

You will be prompted to select the sheets which you want to compare from the two comboboxex i.e.
1) With Compare Sheet Combobox, select the sheet e.g. Feb.
2) With With Sheet Combobox, select the sheet e.g. Jan.
3) And then click the Compare button to compare the selected sheets.

See if this is something you can work with.

[jhonnyexcel]

thanks sktneer..!!
but I'm getting different values, I mean different number of users when I tried to make changes in
the list.

cheers, hope you can have a look at it.

[sktneer]

Not sure exactly what are you saying.
The code creates a Report Sheet with the records where the the Users on the Feb sheet use LSO as Primary System but missing from the Jan Sheet.
Is that not what you are trying to achieve?

See the attache. I have changed the User Ids on both the sheet and both the sheets are having 9 rows of data to compare the records with the output you get after running the code. As you can see there are only two users on Feb Sheet with User IDs N and Q who use LSO as Primary System and missing from the Jan Sheet.

Before comparing the sheets, I selected Feb from the 1st combobox and Jan from the 2nd combobox.

[jhonnyexcel]

Thanks a lot @sktneer
I really appreciate it, It's working.

Cheers
-Jhonny

[sktneer]

You're welcome. Glad I could help.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

[jhonnyexcel]

yep, I did it.
thanks again sktneer.