Question description:
This user has given permission to use the problem statement for this
blog.
hi can you help me out with the vlookup formula
Solved by A. D. in 22 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
28/08/2018 - 07:09
hello how are you?
Excelchat Expert
28/08/2018 - 07:09
Hello, welcome to got it pro.
Excelchat Expert
28/08/2018 - 07:09
I'm fine. Thanks for asking. How about you?
User
28/08/2018 - 07:09
[Uploaded an Excel file]
User
28/08/2018 - 07:09
thank you i am well too
Excelchat Expert
28/08/2018 - 07:09
I understand you need help with a vlookup formula.
User
28/08/2018 - 07:10
i'm sending you a file for reference
User
28/08/2018 - 07:10
yes please
Excelchat Expert
28/08/2018 - 07:10
I have the file opened on my PC.
User
28/08/2018 - 07:10
thank you
User
28/08/2018 - 07:10
here's the background....
Excelchat Expert
28/08/2018 - 07:10
Please tell me details about the problem.
Excelchat Expert
28/08/2018 - 07:10
I'm listening...
User
28/08/2018 - 07:10
the original file is the one under JACINTA
User
28/08/2018 - 07:10
and the new file is ADAM...
User
28/08/2018 - 07:11
i want to know who are the clients that still exist based on both files and who are the clients no longer there
User
28/08/2018 - 07:11
so basically just a comparison based on JACINTA's file
User
28/08/2018 - 07:11
so the two columns F & G
User
28/08/2018 - 07:12
i basically just need a comparison on both list
User
28/08/2018 - 07:12
makes sense?
User
28/08/2018 - 07:12
i'm not sure if i'm explaining clearly though
Excelchat Expert
28/08/2018 - 07:12
Totally makes sense. Although I'm not sure how to compare.
Excelchat Expert
28/08/2018 - 07:12
Are you saying that we should look at column C and D?
Excelchat Expert
28/08/2018 - 07:13
To compare which account is present on another account or not?
User
28/08/2018 - 07:13
yes....but the amounts sometimes wont match
User
28/08/2018 - 07:13
see go to line 3 and line 13
User
28/08/2018 - 07:13
there were changes for the naming
User
28/08/2018 - 07:13
in line 3 you will see "LUX"
User
28/08/2018 - 07:14
in 13 you will see "LUXEMBOURG"
User
28/08/2018 - 07:14
but same amount
User
28/08/2018 - 07:14
there were changes in the names in the new year that's' why it's a pain
User
28/08/2018 - 07:14
:(
Excelchat Expert
28/08/2018 - 07:14
Alright. In that case excel isn't much of a help. I'm really sorry to say that.
User
28/08/2018 - 07:14
and go to line 14 and 15
User
28/08/2018 - 07:15
i see...
User
28/08/2018 - 07:15
what do you suggest
User
28/08/2018 - 07:15
manual check?
Excelchat Expert
28/08/2018 - 07:15
Line 14 and 15 is pretty simple, they are a direct match. So, it is comparable but in the case of 3 and 13, excel recognize them as very different.
Excelchat Expert
28/08/2018 - 07:16
Yeah, manual check is the best way to go here.
Excelchat Expert
28/08/2018 - 07:16
Let me explain why.
Excelchat Expert
28/08/2018 - 07:18
You are saying LUX and LUXEMBOURG are the same. And this is only one instance. Now a rule can be written for this one instance. There might me numerous instances where another thing might be equal to one other thing. If you can tell me that these and these are equal, then I can also write rules for those.
User
28/08/2018 - 07:19
yes they are equal
User
28/08/2018 - 07:20
maybe write me a formula for that and i'll manually apply it to some other clients who i know are the same
Excelchat Expert
28/08/2018 - 07:20
The thing is you might forget to mention some match cases and there might also be some variations of the same case. For example, like 3 says " (LUX) SA LUX" where in line 13 says "LUXEMBOURG" only.
User
28/08/2018 - 07:20
ill do both manual and formula driven
User
28/08/2018 - 07:20
yeah then i'll manually change the data in the vlookup if that's the case
User
28/08/2018 - 07:21
can you give me one vlookup formula for lux then?
User
28/08/2018 - 07:21
i want to test it to this file
Excelchat Expert
28/08/2018 - 07:21
Ah, I'm sorry I should have said in clearly. When I said about writing a rule, I should have mentioned that there might not be a single formula.
User
28/08/2018 - 07:21
yeah that's fine
Excelchat Expert
28/08/2018 - 07:21
The formula would depend on case basis.
User
28/08/2018 - 07:21
just give me on single formula for the LUX client
User
28/08/2018 - 07:21
yes i know
User
28/08/2018 - 07:22
and i will manually amend it
Excelchat Expert
28/08/2018 - 07:23
Okay, first thing is 3 and 13 cannot be equal. There are additional strings in 3. For example, 3 has the texts "(LUX) SA LUX" and 13 has the text "BANK LUXEMBOURG".
Excelchat Expert
28/08/2018 - 07:24
if it was just LUX and "LUXEMBOURG" then it was possible.
Excelchat Expert
28/08/2018 - 07:24
I'm sorry I'm not able to explain why it is not possible more clearly.
Excelchat Expert
28/08/2018 - 07:28
I hope not to disappoint you but there is only so much we can do with excel. And this problem is such a thing that cannot be solved with excel formula.
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.
