Question description:
This user has given permission to use the problem statement for this
blog.
I am not sure if my vlookup is working correctly. I have 30,000 ID to match but only 3,000 matched which makes me concern. I need to identify the duplicates and eliminate them.
Solved by V. E. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/10/2018 - 04:33
Hello. Welcome to Excelchat, I see that your question is about VLOOKUP formula.
Excelchat Expert
10/10/2018 - 04:33
I can help you with that problem.
Excelchat Expert
10/10/2018 - 04:34
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
10/10/2018 - 04:34
Please tell me more on your requirement.
User
10/10/2018 - 04:35
Hi Im not sure how this works, is this the free trial?
Excelchat Expert
10/10/2018 - 04:35
Yes, if this is your first query, it is a free trial.
User
10/10/2018 - 04:35
Ok
Excelchat Expert
10/10/2018 - 04:35
Please tell me more on your requirement.
User
10/10/2018 - 04:36
I need to make sure the vlookup formula is correct
Excelchat Expert
10/10/2018 - 04:36
Ok
User
10/10/2018 - 04:36
Im afraid its not because only 3000 id matched out of 30,000 and I believe there should be more'
Excelchat Expert
10/10/2018 - 04:37
Ok.
User
10/10/2018 - 04:37
I'm trying to eliminate the duplicates
User
10/10/2018 - 04:37
through vlookup but my file is very large
User
10/10/2018 - 04:37
the time keeps clocking when I try to eliminate too
Excelchat Expert
10/10/2018 - 04:37
Please try and share the file.
Excelchat Expert
10/10/2018 - 04:37
You can attach the file or if you want please share the formula.
User
10/10/2018 - 04:38
Where do I download
User
10/10/2018 - 04:38
or attached
Excelchat Expert
10/10/2018 - 04:38
If we can eliminate the error just by the formula, I will.
Excelchat Expert
10/10/2018 - 04:38
Please use the the Pin icon just right to this chat window to attach the file
User
10/10/2018 - 04:39
My file is too large to attach
User
10/10/2018 - 04:39
=(
Excelchat Expert
10/10/2018 - 04:39
Ok. Please share the formula or a part of the file.
Excelchat Expert
10/10/2018 - 04:40
Or you can also paste the sample data on the right hand side preview.
Excelchat Expert
10/10/2018 - 04:41
You can also share the file via drop box and then sharing the download link here.
User
10/10/2018 - 04:42
not sure how to do drop box
Excelchat Expert
10/10/2018 - 04:42
Ok.
Excelchat Expert
10/10/2018 - 04:42
Please share the formula then.
User
10/10/2018 - 04:44
Sorry I hit something incorrectly
Excelchat Expert
10/10/2018 - 04:44
Ok.
Excelchat Expert
10/10/2018 - 04:45
We have time restriction per session. So I suggest you to share the formula or file so that I can help you within the assigned time.
Excelchat Expert
10/10/2018 - 04:47
Are you still there?
User
10/10/2018 - 04:48
Yep
Excelchat Expert
10/10/2018 - 04:48
Please respond.
User
10/10/2018 - 04:48
Im trying to load the info
Excelchat Expert
10/10/2018 - 04:48
Ok.
Excelchat Expert
10/10/2018 - 04:48
Can you just share the Vlookup here which you are using in the meantime you can build the info as required.
User
10/10/2018 - 04:50
Ok do you see this
Excelchat Expert
10/10/2018 - 04:50
Yes
User
10/10/2018 - 04:51
Is this formula correct?
Excelchat Expert
10/10/2018 - 04:51
The formula is correct but you need to make the range absolute.
User
10/10/2018 - 04:51
I have more data to add but its super slow because its too much
Excelchat Expert
10/10/2018 - 04:51
=vlookup(A2,Sheet2!A1:I54,1,false)
Excelchat Expert
10/10/2018 - 04:51
=vlookup(A3,Sheet2!A2:I55,1,false)
Excelchat Expert
10/10/2018 - 04:51
See the difference in both the formula
Excelchat Expert
10/10/2018 - 04:52
1st one is checking the data from A1 but the second is checking data from A2.
Excelchat Expert
10/10/2018 - 04:52
=vlookup(A2,Sheet2!$A$1:$I$54,1,false)
Excelchat Expert
10/10/2018 - 04:52
This should be your formula and then you copy it down.
Excelchat Expert
10/10/2018 - 04:53
So i have added $ to the range to make it absolute, so that when you copy the formula down, it will take the complete range for every search.
Excelchat Expert
10/10/2018 - 04:53
This is the reason you are getting less matches.
Excelchat Expert
10/10/2018 - 04:53
Does it make sense?
User
10/10/2018 - 04:54
Let me try it on my end
Excelchat Expert
10/10/2018 - 04:55
Sure.
User
10/10/2018 - 04:57
when you say absolute, should the table array be a specific column only?
Excelchat Expert
10/10/2018 - 04:58
Please copy and paste the exact formula you are using on your file.
Excelchat Expert
10/10/2018 - 04:58
Copy paste the first formula you have for the Vlookup here on chat.
User
10/10/2018 - 04:59
=VLOOKUP(A2,'Ramsey Cty Prim 2016'!Print_Area,1,FALSE)
Excelchat Expert
10/10/2018 - 04:59
so you have the range names as Print_Area
User
10/10/2018 - 05:00
ok, the second sheet is a print page
User
10/10/2018 - 05:00
how do I unformat that
User
10/10/2018 - 05:00
Im matching the vlookup onto the print page id
User
10/10/2018 - 05:01
is this an issue of print area?
Excelchat Expert
10/10/2018 - 05:01
Ramsey Cty Prim 2016 is the tab
User
10/10/2018 - 05:01
yep
Excelchat Expert
10/10/2018 - 05:01
Go to this tab
Excelchat Expert
10/10/2018 - 05:02
Then go to Formulas and Name Manager
Excelchat Expert
10/10/2018 - 05:03
You will see the "Print_Area" name listed there.
User
10/10/2018 - 05:03
the print area is the ramsey county tab
User
10/10/2018 - 05:03
so its included
Excelchat Expert
10/10/2018 - 05:03
I think if there is any error it is with the named range (if any)
Excelchat Expert
10/10/2018 - 05:04
Otherwise, I have to see the file to find the error.
User
10/10/2018 - 05:04
I see Printa Area in "Name Manager"
Excelchat Expert
10/10/2018 - 05:05
Ok. Select it and delete it. The again put the VLOOKUP formula using the absolute references as I explained above.
Excelchat Expert
10/10/2018 - 05:05
O
Excelchat Expert
10/10/2018 - 05:05
Or
Excelchat Expert
10/10/2018 - 05:05
If you select the name range, and click Edit - you will see the range it is for.
Excelchat Expert
10/10/2018 - 05:06
Please make sure it contains all the data.
Excelchat Expert
10/10/2018 - 05:07
Did it work?
User
10/10/2018 - 05:07
Trying it now
Excelchat Expert
10/10/2018 - 05:08
Ok
Excelchat Expert
10/10/2018 - 05:10
This is the last extension we have.
Excelchat Expert
10/10/2018 - 05:11
We will not be able to extend the time further than this.
User
10/10/2018 - 05:11
Yep I understand, I did get the frmula corrected. Thank you. I need to zero out all the NA
Excelchat Expert
10/10/2018 - 05:11
Great.
User
10/10/2018 - 05:12
I really appreciate thank
Excelchat Expert
10/10/2018 - 05:12
Does this solution solve your problem?
Excelchat Expert
10/10/2018 - 05:12
Do you have any other questions about this problem and the solution?
User
10/10/2018 - 05:12
Im not sure completely because my file is too large
Excelchat Expert
10/10/2018 - 05:12
Ok.
User
10/10/2018 - 05:13
Im not able to tell because of all the NA because eI can't calculate how many are being captured
Excelchat Expert
10/10/2018 - 05:13
Ok.
Excelchat Expert
10/10/2018 - 05:13
I hope you will not review me badly because of this.
Excelchat Expert
10/10/2018 - 05:14
If you could have shared the file, I would have solved it within few minutes.
Excelchat Expert
10/10/2018 - 05:14
You can share the file even with google drive.
Excelchat Expert
10/10/2018 - 05:14
I am still here to help and solve the issue.
Excelchat Expert
10/10/2018 - 05:15
You can count the number of N/A by using this formula
Excelchat Expert
10/10/2018 - 05:15
=COUNTIFS(L:L,"#N/A")
User
10/10/2018 - 05:15
No, I won't review you badly. I really appreciate our help. Should I download the file onto good drive?
Excelchat Expert
10/10/2018 - 05:16
just replace L:L with the column in which you have N/A like B:B OR C:c
User
10/10/2018 - 05:16
There I get is there's too much unique id that I can't capture all of them
Excelchat Expert
10/10/2018 - 05:16
Just upload it to google drive and then you can make it shareable and share the download link with me here on chat.
User
10/10/2018 - 05:16
ok
Excelchat Expert
10/10/2018 - 05:19
Do you need further help from my side?
User
10/10/2018 - 05:21
how do I download the file from googe drive
User
10/10/2018 - 05:21
new to this
Excelchat Expert
10/10/2018 - 05:21
Which file you want to download?
Excelchat Expert
10/10/2018 - 05:22
Have you uploaded the file to google drive?
User
10/10/2018 - 05:22
Yep
User
10/10/2018 - 05:22
I have
User
10/10/2018 - 05:22
the file is called Address
User
10/10/2018 - 05:22
Address Info
Excelchat Expert
10/10/2018 - 05:22
Now right click on the file you will see few options
User
10/10/2018 - 05:22
Can you see it?
User
10/10/2018 - 05:22
Ok
User
10/10/2018 - 05:22
Right click then
Excelchat Expert
10/10/2018 - 05:22
Click on get shareable link
Excelchat Expert
10/10/2018 - 05:23
it will generate the link, please share that link with me.
User
10/10/2018 - 05:23
https://drive.google.com/open?id=1NMvEOhEglzlMTJylzK0KpIcspaEO7l7G
Excelchat Expert
10/10/2018 - 05:24
ok
User
10/10/2018 - 05:24
Do you see the file
User
10/10/2018 - 05:24
and are you able to show me on here
Excelchat Expert
10/10/2018 - 05:24
I have the file.
Excelchat Expert
10/10/2018 - 05:24
Give me a minute
Excelchat Expert
10/10/2018 - 05:26
The file is too big to be open in google spreadsheet.
User
10/10/2018 - 05:27
Ok, I want to turn all the NA to 0 or blank so I can filter
Excelchat Expert
10/10/2018 - 05:27
Even my excel is hung because of the file.
User
10/10/2018 - 05:27
Been trying to find a solution to identify and them eliminate duplicates
User
10/10/2018 - 05:27
Im so sorry
Excelchat Expert
10/10/2018 - 05:27
You can use a if formual with Vlookup
User
10/10/2018 - 05:28
Ok what do Ineed to add?
Excelchat Expert
10/10/2018 - 05:30
please do this
Excelchat Expert
10/10/2018 - 05:30
IFERROR(YOUR VLOOK UP FORMULA , "")
Excelchat Expert
10/10/2018 - 05:30
IFERROR(FORMULA,"")
Excelchat Expert
10/10/2018 - 05:30
SO ALL N/A BECOMES BLANK CEE
Excelchat Expert
10/10/2018 - 05:30
CELL
User
10/10/2018 - 05:30
Ok thank you
Excelchat Expert
10/10/2018 - 05:31
i FOUND 10001 matching entries
User
10/10/2018 - 05:32
Ok that sound right
User
10/10/2018 - 05:32
Could you help resend the file to me
User
10/10/2018 - 05:32
I need to filter
Excelchat Expert
10/10/2018 - 05:32
Your formula is correct.
Excelchat Expert
10/10/2018 - 05:32
Just add the ifferror to your formula
Excelchat Expert
10/10/2018 - 05:33
=IFERROR(VLOOKUP(A2,'Ramsey Cty Prim 2016'!$A$1:$N$34282,1,FALSE),0)
Excelchat Expert
10/10/2018 - 05:33
iferror(formula,0)
Excelchat Expert
10/10/2018 - 05:33
then filter it without 0
Excelchat Expert
10/10/2018 - 05:33
thank you.
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.
