Question description:
This user has given permission to use the problem statement for this
blog.
I am working in excel version 15.41
I want to compare to two large lists of doctors to determine matches based on NPI numbers. List are on 2 separate sheets (sheet 1 Column G and Sheet 2 Column A). I would like the sheet with duplications to be shown on Sheet 1 by highlighting the matching NPI numbers and lines that match.
However I can not load the list because the file exceeds 20MB
Solved by E. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
01/08/2018 - 05:50
I am
Excelchat Expert
01/08/2018 - 05:50
Hello, I understand that you need help with identifying duplicates within 2 lists, right?
User
01/08/2018 - 05:50
yes
User
01/08/2018 - 05:51
are you still there?
Excelchat Expert
01/08/2018 - 05:51
I understand that you are not able to upload the file due to size limitations. Are you able to provide a sample instead?
User
01/08/2018 - 05:52
Not really Can I share the two files via G drive?
Excelchat Expert
01/08/2018 - 05:52
Oh so not 2 sheets, but two different workbooks?
Excelchat Expert
01/08/2018 - 05:53
Yes please upload the file in Google Drive and make sure to share to everyone so I can download it.
User
01/08/2018 - 05:53
yes
User
01/08/2018 - 05:53
can I share to an email? or a link?
Excelchat Expert
01/08/2018 - 05:54
I'm afraid it is against policy to share email address. You can try a shared link though.
User
01/08/2018 - 05:55
List match https://drive.google.com/drive/folders/163k9GK9YPd0bpr_ajCrVx-e_e-eJcpGe?usp=sharing
Excelchat Expert
01/08/2018 - 05:55
Please share to everyone with the link.
Excelchat Expert
01/08/2018 - 05:55
The link you provided is set to private.
Excelchat Expert
01/08/2018 - 05:58
Still with me?
Excelchat Expert
01/08/2018 - 05:58
If you are not able to share your file then I'll just have to give you the general steps on how to apply conditional formatting.
User
01/08/2018 - 05:58
try no
User
01/08/2018 - 05:59
try now
Excelchat Expert
01/08/2018 - 05:59
Still asking for permission.
User
01/08/2018 - 05:59
Now?
Excelchat Expert
01/08/2018 - 06:00
Still asking for permission.
Excelchat Expert
01/08/2018 - 06:00
Our time is limited so I'll just have to give you the general steps and you'll just have to adjust it to work on your file. Would that be alright?
User
01/08/2018 - 06:00
I guess
Excelchat Expert
01/08/2018 - 06:01
Okay, in order for conditional formatting to work, both of your data must reside in one workbook.
Excelchat Expert
01/08/2018 - 06:02
Since you have 2 seperate workbooks, you'll have to copy one list over to the other.
Excelchat Expert
01/08/2018 - 06:02
And then we can use conditional formatting which will automatically highlight cells that exist in both lists.
User
01/08/2018 - 06:03
Thats going to take a few minutes
Excelchat Expert
01/08/2018 - 06:04
That's the only way to do it, I'm afraid. Excel requires this to be the case. There's no going around it.
Excelchat Expert
01/08/2018 - 06:05
I can only provide you with a solution that Excel can do.
User
01/08/2018 - 06:05
What if I copy a few over and you can show me how?
Excelchat Expert
01/08/2018 - 06:06
I'm going to provide you the steps and I'll also send over a sample file.
Excelchat Expert
01/08/2018 - 06:08
Please download this file.
[Uploaded an Excel file]
Excelchat Expert
01/08/2018 - 06:08
You will see 3 sheets in that file.
Excelchat Expert
01/08/2018 - 06:08
Sheet 1 is your first file that has the doctor Id in column G.
Excelchat Expert
01/08/2018 - 06:09
Sheet 2 is where your second file where the doctor ID is in column A.
Excelchat Expert
01/08/2018 - 06:09
As you can see, if the ID in column G of Sheet1 exists anywhere in column A of Sheet2, it automatically gets highlighted.
Excelchat Expert
01/08/2018 - 06:09
The third sheet is where I placed the instructions on how to accomplish this.
User
01/08/2018 - 06:11
So list on sheet 2 has 25000 names
User
01/08/2018 - 06:12
Does formula work for the entire column
Excelchat Expert
01/08/2018 - 06:12
It doesn't matter how many names are in Sheet2, it looks at the entire column A so it will still work.
Excelchat Expert
01/08/2018 - 06:13
The only thing you'll need to adjust is the range where the conditional formatting will be applied to.
Excelchat Expert
01/08/2018 - 06:13
In the instructions, I used =$G$2:$G$2000 as the range but you can adjust that to any number you need.
Excelchat Expert
01/08/2018 - 06:13
This depends on how many rows of data you have for the Sheet1.
User
01/08/2018 - 06:14
So I have 95,000 names on list 1 column G and 28,000 names on list 2 ColumnA
User
01/08/2018 - 06:15
What would the conditional formula be?
Excelchat Expert
01/08/2018 - 06:15
The conditional formula won't need to change.
Excelchat Expert
01/08/2018 - 06:15
Again, it doesn't really care how many names in list 2 column A because it will take a look at the entire column.
Excelchat Expert
01/08/2018 - 06:16
As for the 95000, you'll have to change the "Applies to" range to =$G$2:$G$95000
Excelchat Expert
01/08/2018 - 06:16
If you'll look at the last part of the instructions, you'll see =$G$2:$G$2000. Just update that to match the number of names in your list 1
Excelchat Expert
01/08/2018 - 06:17
If you want to keep it simple as well, you can apply it to the entire column G by using =$G:$G instead.
Excelchat Expert
01/08/2018 - 06:27
Any luck?
Excelchat Expert
01/08/2018 - 06:27
Our session is about to end automatically in 2 minutes.
Excelchat Expert
01/08/2018 - 06:28
I don't have the capabilities to extend this session.
Excelchat Expert
01/08/2018 - 06:28
Please note that this is the last extension our system will allow.
User
01/08/2018 - 06:29
I just copied over the files so give me a few please
User
01/08/2018 - 06:31
no formula values appear
User
01/08/2018 - 06:32
The new formatting rule says color?
Excelchat Expert
01/08/2018 - 06:33
[Uploaded an Excel file]
Excelchat Expert
01/08/2018 - 06:33
Please see the above screenshot.
User
01/08/2018 - 06:35
not showing that
Excelchat Expert
01/08/2018 - 06:35
Are you using a Windows computer or a Mac?
User
01/08/2018 - 06:35
mac
[Uploaded an Excel file]
User
01/08/2018 - 06:36
2
[Uploaded an Excel file]
Excelchat Expert
01/08/2018 - 06:36
Click New rule and let me see what comes up.
User
01/08/2018 - 06:37
[Uploaded an Excel file]
Excelchat Expert
01/08/2018 - 06:38
Drop down the Style option and change it to Classic.
Excelchat Expert
01/08/2018 - 06:39
Drop down the next option and select Use a formula to determine which cells to format.
Excelchat Expert
01/08/2018 - 06:39
Got it?
User
01/08/2018 - 06:39
Cell styles?
Excelchat Expert
01/08/2018 - 06:39
On the Home tab, click Conditional Formatting > New Rule.
Excelchat Expert
01/08/2018 - 06:40
In the Style box, click Classic.
Excelchat Expert
01/08/2018 - 06:40
Under the Classic box, click to select Format only top or bottom ranked values, and change it to Use a formula to determine which cells to format.
Excelchat Expert
01/08/2018 - 06:40
In the next box, type the formula
Excelchat Expert
01/08/2018 - 06:41
I don't have a Mac computer so I'm just basing the steps from a guide. If you'd need to contact us again in the future, I'd strongly recommend mentioning that you are using Excel for Mac so Mac experts can pick you up.
Excelchat Expert
01/08/2018 - 06:41
We have 8 minutes remaining. Were you able to find the box to type the formula?
User
01/08/2018 - 06:43
So in this blank box I type in the formula=MATCH(G2,Sheet2!$A:$A,0)
[Uploaded an Excel file]
Excelchat Expert
01/08/2018 - 06:43
Yes. Better if you'd just paste it to be sure.
Excelchat Expert
01/08/2018 - 06:44
Are both your names or ID in column A?
Excelchat Expert
01/08/2018 - 06:44
Because the initial instruction was that the first list has the names or ID in column G.
Excelchat Expert
01/08/2018 - 06:45
And the second list has it in column A.
Excelchat Expert
01/08/2018 - 06:45
The steps I've provided relies on these facts. If there has been a change, the steps may not work.
Excelchat Expert
01/08/2018 - 06:47
We have less than 4 minutes remaining and there's nothing I can do to extend this session further. I've provided all the information you'll need in order to accomplish your goal. Just follow the steps carefully and ensure that you are using the correct columns that you initially gave us.
User
01/08/2018 - 06:48
where is format values
Excelchat Expert
01/08/2018 - 06:48
As you can see, the file I provided earlier works just like how you wanted it. Applying the same technique to your file will result the same.
Excelchat Expert
01/08/2018 - 06:48
You are already way past that.
Excelchat Expert
01/08/2018 - 06:48
You already found the box to type the formula in.
Excelchat Expert
01/08/2018 - 06:48
The last screenshot has that box.
Excelchat Expert
01/08/2018 - 06:49
Under the use a formula
Excelchat Expert
01/08/2018 - 06:49
We have less than a minute. If you can't finish this I'd suggest posting your question again and be ready with your file. Please mention that you are using Excel for Mac so the right instructions can be given to your from the start.
User
01/08/2018 - 06:49
ok thanks
Excelchat Expert
01/08/2018 - 06:50
I'd appreciate a 5-star rating and your feedback if you think I deserve it.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
