Question description:
This user has given permission to use the problem statement for this
blog.
If I have a sheet that pulls data from another sheet. How can I compare if the right data is filled in, after the fact?
Solved by Z. Y. in 43 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
12/10/2018 - 10:49
Hello.
Excelchat Expert
12/10/2018 - 10:49
I understand that you need help with comparing data even after the data in the other sheet gets updated, right?
User
12/10/2018 - 10:50
No, as of the moment, i just need to know that the values match accordingly.
Excelchat Expert
12/10/2018 - 10:51
I see. I can help you with this. 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
12/10/2018 - 10:51
Are you able to provide your data or at least a sample so I can provide the best suggestion for your requirement?
User
12/10/2018 - 10:52
Sure.
Excelchat Expert
12/10/2018 - 10:53
You may either type sample using the sheet to the right or upload your file using the clip icon next to this chat.
Excelchat Expert
12/10/2018 - 10:56
Still with me?
Excelchat Expert
12/10/2018 - 10:56
I could just create a sample data if you want but then you will have to adjust the solution to fit your actual file.
User
12/10/2018 - 10:56
Sorry wasnt able to type there fora bit
User
12/10/2018 - 10:58
im unable to attach the file
User
12/10/2018 - 10:58
it justs keeps loading
Excelchat Expert
12/10/2018 - 10:58
The file might be too large for our platform.
User
12/10/2018 - 10:58
Just*
Excelchat Expert
12/10/2018 - 10:59
Are you able to type a sample using the sheet to the right?
User
12/10/2018 - 10:59
Ok. 'll try taht
User
12/10/2018 - 10:59
that*
User
12/10/2018 - 11:03
So i need to check if the correct values from Sheet 2 were filled in sheet 1
Excelchat Expert
12/10/2018 - 11:03
Thank you. It would also be ideal to change the names of the sheet to match yours so the formula I'll be giving you will work with few changes.
Excelchat Expert
12/10/2018 - 11:03
Alright, may I know how the 2 sheets are connected?
User
12/10/2018 - 11:04
Connected, like how?
Excelchat Expert
12/10/2018 - 11:05
How would you know that the values are correct?
User
12/10/2018 - 11:07
they both have countries and job profile that have a min, mid , max and 3 segments
User
12/10/2018 - 11:07
in terms of compensation
Excelchat Expert
12/10/2018 - 11:07
Are they connected via the Name?
Excelchat Expert
12/10/2018 - 11:07
Like you want to check AUS-AD01 if it has the same values in the Ranges sheet AUS-AD01.
User
12/10/2018 - 11:08
So each country/job profile should have the correct values of compensation
User
12/10/2018 - 11:08
yes
Excelchat Expert
12/10/2018 - 11:08
Which columns should be checked?
Excelchat Expert
12/10/2018 - 11:09
Please list down the columns that needs to be checked so I can create formulas for those columns.
Excelchat Expert
12/10/2018 - 11:09
Minimum X1 Midpoint X2 Maximum?
User
12/10/2018 - 11:09
YEs
Excelchat Expert
12/10/2018 - 11:10
Maximum and Segment 3 are the same?
User
12/10/2018 - 11:10
Yes
Excelchat Expert
12/10/2018 - 11:11
Alright, the best way to solve this is to simply use a formula in your DATA sheet so your MIN MID MAX values all come directly from RANGES.
Excelchat Expert
12/10/2018 - 11:11
I'll be working on this using the sheet to the right. PLease give me a few minutes.
User
12/10/2018 - 11:11
Okay..
User
12/10/2018 - 11:11
Sure
Excelchat Expert
12/10/2018 - 11:13
Please see the formula I wrote in the columns I highlighted.
Excelchat Expert
12/10/2018 - 11:13
This formula ensures that the values are 100% equal to the one in RANGES.
Excelchat Expert
12/10/2018 - 11:13
So you won't even need to check.
Excelchat Expert
12/10/2018 - 11:14
In fact, if you change anything in the RANGES sheet, the values in your DATA will automatically adjust themselves to the changes.
Excelchat Expert
12/10/2018 - 11:14
ITA-AD01 is returning #N/A because it's not included in the RANGES yet but once you have the complete data set, it will show you the values.
Excelchat Expert
12/10/2018 - 11:15
You can try changing the values in the RANGES sheet to see how it works.
Excelchat Expert
12/10/2018 - 11:18
Are you still with me?
User
12/10/2018 - 11:18
Yes..
User
12/10/2018 - 11:22
i will change values only in the ranges sheet?
Excelchat Expert
12/10/2018 - 11:22
Yes.
Excelchat Expert
12/10/2018 - 11:22
The DATA sheet relies on what is in the RANGES sheet to ensure that both data are the same.
User
12/10/2018 - 11:23
okay..
Excelchat Expert
12/10/2018 - 11:24
Is that what you need or you just want to compare?
User
12/10/2018 - 11:24
I just need to compare
Excelchat Expert
12/10/2018 - 11:24
If you want to compare then we can just create new columns that get the values.
User
12/10/2018 - 11:26
Okay..
Excelchat Expert
12/10/2018 - 11:27
If you just want to compare then you will need to add the columns to the right. Please see the formula I wrote in there.
User
12/10/2018 - 11:28
i see
Excelchat Expert
12/10/2018 - 11:29
So in here, you will see that if they don't match, you will get an FALSE message.
Excelchat Expert
12/10/2018 - 11:29
You will get #N/A if the Eligibility Rule+ is not found in the RANGES sheet.
User
12/10/2018 - 11:30
understood
Excelchat Expert
12/10/2018 - 11:30
Does this satisfy your requirement?
User
12/10/2018 - 11:30
Yes. Thanks
Excelchat Expert
12/10/2018 - 11:30
Please download this file.
[Uploaded an Excel file]
Excelchat Expert
12/10/2018 - 11:31
You can use it as a reference for when you apply the formula to your data.
User
12/10/2018 - 11:31
Alriight. Thanks
User
12/10/2018 - 11:31
Its ok if I end this session, i ll be sure to leave positive feedback
Excelchat Expert
12/10/2018 - 11:31
Note that you will have to update the references in your file if you need to.
Excelchat Expert
12/10/2018 - 11:31
Sure, don't hesitate to contact us back if you need help in applying the formula to your actual file.
User
12/10/2018 - 11:31
Okay thanks
Excelchat Expert
12/10/2018 - 11:31
But I'm sure you will have no problem with that.
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.
