Excel - COLUMN Function Problem - Expert Solution

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
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
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
User 08/08/2018 - 03:40
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc