How to find amendments in second excel sheet

excel_blur

New Member
Joined
Nov 20, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Dear forummers

I hope you can help me. I am a very basic excel user.

If i have
Excel sheet 1, at row 30 - John, john@yahoo.com
Excel sheet 2, at row 1 - John, john_new@yahoo.com
Note that the 2 sheets are not of same length


How do i write a formula such that i can ask Excel to find John in sheet 2, and if the email address is different, highlight it.

Excel sheet 1 is my mailing list that has 4000 names
Excel sheet 2 is a list that I get from another department, using another system, that shows the email address changes, maybe only 100 changes each month.
I have to update my 4000 mailing list monthly.

Right now i put Excel sheet 1 and 2 together and then sort it and then use my eyes to scan 4000 lines for any different emails. I know i am at a very prehistoric excel level. My apologies.

I thank you for any advice.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B)
Dear svendiamond

Thank you for the formula. However A2 means only looking for John is that right?

What if there were Mary, Jane, and many more names in Excel sheet 1 that changed email addresses in Excel sheet 2?
(the names mary, jane are same in sheet 2)

I need to find the changed email addresses.

Thank you so very much!
 
Upvote 0
It's difficult to help you without knowing what the data looks like. Instead of just referring to sheets, tell us which columns have the data. You could take a screenshot, or use one of the handy tools that Mr Excel has built into the forum, such as the XL2BB extension. Screenshot is easiest for newbies I suppose. Or at least just tell me which columns have what. Or post a small example somehow.
 
Upvote 0
Dear svendiamond,

After one hour of trying to install xl2bb in a poor windows 10 Pro, office 2010 computer, i finally succeeded in a newer computer. But sadly i only see xl2bb appear once in a different file.

Then i spent another half an hour trying to add in to any file but it never ever appeared again. It was really a once off disappearing act the xl2bb. And i believe i followed all instructions correctly. I watched the video, I read and read.

So sadly, i am posting an image, as i am now so dizzy.

As you can see, the 4000 is a list of members.
Then every month, the membership department generates a list of members with any amendments in their details. It can be last name, when person has gotten married for example. The one thing that does not change is the membership ID

I want to be able to spot only the email changes in the 200 list.

So for every unique membership number, eg. 56333, Sally Miller has changed her email address. I want to have the email as conditionally formatted or something to spot it. The most important.



Thank you so much SvenDiamond.
 

Attachments

  • 4000list.png
    4000list.png
    51.4 KB · Views: 9
  • 200list_withchanges.png
    200list_withchanges.png
    14.3 KB · Views: 9
Upvote 0
Got it. And so sorry for the trouble about the add-on. I know it can be a pain. I wish you'd have given up earlier in your quest and just sent the screenshots! But I admire your determination.

So, the XLOOKUP function can take a value and look it up on another sheet, and return a value from a different column on that other sheet. But in this case it looks like you don't even want to pull any information from the other sheet, rather you just want to check and see if the membership ID exists on the second sheet at all. For that you can use this formula:

Excel Formula:
=ISNUMBER(XMATCH(A2,Sheet2!A:A))

That will return either TRUE or FALSE. I like to put that in an extra helper column to make things easy to see:

1637635760137.png



Then we can make a conditional formatting rule that reads the cells in G, and applies formatting to the entire range. Like this:
1637635877705.png



Use a formula to determine:
1637635890985.png



And finally, Applies To your range:
1637635914721.png
 
Upvote 0
@svendiamond according to the OP's profile they are using xl 2019 & therefore don't have Xlookup or Xmatch. ;)
 
Upvote 0
Oh no...

Let's change it to

Excel Formula:
=ISNUMBER(MATCH(A2,Sheet2!A:A,0))
 
Upvote 0
Oh no...

Let's change it to

Excel Formula:
=ISNUMBER(MATCH(A2,Sheet2!A:A,0))
Oh my gosh, this forum is like Magic!

If I had been a prehistoric excel user of using my eyes to scan 4000 over lines to spot changes, I am so truly grateful to receive help from big experts!

I was really worried when I saw the replies. With trepidation, I tried, for fear of being an idiot not knowing left from right and it worked.

Thank you so much Svendiamond and Fluff! Indeed I am using 2019, just what kind of bionic eyes do users in this forum have!!

You all have really made my day. Oh man I sure am lucky!

Please may I ask, is there somewhere I could read up and learn more about Excel functions I should know, so I advance a little from the Cambrian age.

I previously tried to read this ablebits page How to compare two Excel files or sheets for differences - Ablebits.com But I didn't get very far as it was comparing to excel sheets with the same number of rows and column descriptors.

Thank you so much Svendiamond and Fluff!!!
 
Upvote 0
Hi again
I have been studying further. It seems like if the membership ID matched in the first row then it does not work? thanks again!
 

Attachments

  • John_in_4000.png
    John_in_4000.png
    57.8 KB · Views: 5
  • John_in_listofchanges.png
    John_in_listofchanges.png
    52.8 KB · Views: 6
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top