Question description:
This user has given permission to use the problem statement for this
blog.
I have a numeric identifier in column A with a corresponding numeric value (Let's call it Z) X columns below it. There are varying duplicated cells of the identifier before Z I also have a numeric value (Y) in Column B that I am trying to verify is the same value as Z. Is there a way to scan down Column A, identify Z, and match it to Y?
Solved by Z. E. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/08/2018 - 03:24
Hello, I understand that you are trying to lookup values. In order to have a better understanding of your requirement, can you share a sample data using the document to the right?
User
08/08/2018 - 03:24
sure one moment
User
08/08/2018 - 03:25
Column A has a 16 digit identification number, that is duped a random number of times, with a corresponding dollar value below however many dupes it has
User
08/08/2018 - 03:26
then I have VLOOKUP'd a corresponding value to that number from a different source
User
08/08/2018 - 03:27
I need to crash the two to make sure the two dollar values match and identify the 16 digit id numbers where they dont
Excelchat Expert
08/08/2018 - 03:27
Can you recreate the different source as well so I'll be able to understand it better. So far it's not making any sense yet.
Excelchat Expert
08/08/2018 - 03:29
Are you still with me?
User
08/08/2018 - 03:30
I cannot recreate it. Think of the 16 digit number as an "Account Number" and anything smaller than that as a payment on that account
Excelchat Expert
08/08/2018 - 03:30
So in this case, A1 to A3 is the account number and A4 is the payment?
User
08/08/2018 - 03:30
Column A is payment data from an outside system sent to me that I have not manipulated. Column B is payment into from an internal system that I have pulled based off of the same account number
User
08/08/2018 - 03:30
yes
Excelchat Expert
08/08/2018 - 03:31
Okay, and what do you need help with?
User
08/08/2018 - 03:31
A4 is the external record of payment, B3 is the internal record
User
08/08/2018 - 03:31
I need to reconcile the two
Excelchat Expert
08/08/2018 - 03:31
Can you give an example where they don't match?
User
08/08/2018 - 03:31
That's what I can't find
User
08/08/2018 - 03:32
The problem is that there are not always the same number of duplicated account numbers in column A
User
08/08/2018 - 03:32
Hold on I can show you
Excelchat Expert
08/08/2018 - 03:32
I just need to see how a non match will look like.
Excelchat Expert
08/08/2018 - 03:32
Otherwise, I won't even know what to look for.
User
08/08/2018 - 03:33
it follows this format
Excelchat Expert
08/08/2018 - 03:34
So you only need to match the last payment for that particular account number and the payment that is under the account number?
User
08/08/2018 - 03:34
oh wait I displayed that wrong
User
08/08/2018 - 03:36
actually yes you are correct
User
08/08/2018 - 03:36
yes I only need to match the last payment
Excelchat Expert
08/08/2018 - 03:37
Okay. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
08/08/2018 - 03:37
Please give me a few minutes to analyze your data format.
User
08/08/2018 - 03:37
thank you
Excelchat Expert
08/08/2018 - 03:37
Please extend the session when prompted.
Excelchat Expert
08/08/2018 - 03:38
16 digits or 15?
User
08/08/2018 - 03:38
16
Excelchat Expert
08/08/2018 - 03:39
Okay, the formula in column C should help identify those that don't match.
Excelchat Expert
08/08/2018 - 03:39
It first checks if the value in column A is 16 digits long, if it is not then it compares the value in A to the value in B but 1 row less.
Excelchat Expert
08/08/2018 - 03:40
You'll see that it'll return FALSE if they don't match.
User
08/08/2018 - 03:40
ahhhhhh
User
08/08/2018 - 03:40
genius
User
08/08/2018 - 03:40
thank you so much
Excelchat Expert
08/08/2018 - 03:40
You are welcome. Glad to have helped!
Excelchat Expert
08/08/2018 - 03:40
Would there be anything else that I can help you with regards to the original question?
User
08/08/2018 - 03:41
I think that should do it
User
08/08/2018 - 03:41
thank you
Excelchat Expert
08/08/2018 - 03:41
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
08/08/2018 - 03:41
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
08/08/2018 - 03:41
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. 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.
