Question description:
This user has given permission to use the problem statement for this
blog.
I have two very large sets of numerical data on the same excel file but on two different sheets and I need to compare them and highlight any differences. Data Set number 1 is all just in one column, But data set number two is also in one column but there is a lot of other data that is not relevant. I used a pivot table, to sum up all of the numbers I needed in Data set 2 and then I summed up the numbers in data set 1 and there was a difference so I need to have excel highlight that difference
Solved by M. H. in 57 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
08/02/2018 - 03:26
Hi
Excelchat Expert
08/02/2018 - 03:26
Hello!
Excelchat Expert
08/02/2018 - 03:26
Welcome to Got It Pro-Excel.
User
08/02/2018 - 03:27
How do I Edit this sheet we can both see?
Excelchat Expert
08/02/2018 - 03:27
Can you share with us the file you are referring for to resolve the problem?
User
08/02/2018 - 03:27
Sady I can not, it is just I problem I would like to know how to solve when It comes up again
Excelchat Expert
08/02/2018 - 03:27
Click the "EDIT DOCUMENT" shown at the upper-right corner of the screen
User
08/02/2018 - 03:28
Ok
Excelchat Expert
08/02/2018 - 03:28
Okay, I understand
User
08/02/2018 - 03:28
So here Ill type up a little example and then maybe you can show me how to do it?
Excelchat Expert
08/02/2018 - 03:29
Sure :)
User
08/02/2018 - 03:29
Can you see what I am doing?
Excelchat Expert
08/02/2018 - 03:29
Yes, got it
User
08/02/2018 - 03:30
Ok
User
08/02/2018 - 03:31
So Basically imagin there is like 5000 data points in row a
User
08/02/2018 - 03:31
and they are all differnt numbers
User
08/02/2018 - 03:31
not just 1
Excelchat Expert
08/02/2018 - 03:31
Okay
User
08/02/2018 - 03:31
So I cant do it by hand
User
08/02/2018 - 03:31
Now I need to compare all of the data from set ID 1003 with the data in A from sheet 1
User
08/02/2018 - 03:32
and highlight differences
Excelchat Expert
08/02/2018 - 03:33
So, are we going to compare exactly A2 at sheet1 vs A2 at sheet2?
Excelchat Expert
08/02/2018 - 03:33
regardless of their set ID?
User
08/02/2018 - 03:34
No thats what I was saying I only want to compare the numbers in Set ID 1003 on sheet 2
User
08/02/2018 - 03:34
So there is other numbers in the way
User
08/02/2018 - 03:34
and the real sheet is like 5000 numbers long so I can just count them by hand, or find where they start or end because it would take to long
User
08/02/2018 - 03:35
So I used a pivot table to find all of the numbers in Set ID 1003
User
08/02/2018 - 03:35
But then I need to know how to compare them to their counterparts in A on sheet 1
Excelchat Expert
08/02/2018 - 03:36
Which column should we place the result,?
User
08/02/2018 - 03:36
Is there a way to highlight the ones that are different?
User
08/02/2018 - 03:36
(Sorry for spelling btw im typeing fast lol)
Excelchat Expert
08/02/2018 - 03:36
no worries
Excelchat Expert
08/02/2018 - 03:37
Okay, can you give me one sample that youn based on your data shown
Excelchat Expert
08/02/2018 - 03:37
So we can set the formula for highlighting the difference
User
08/02/2018 - 03:38
So for example when you do it, on the first sheet all of the number "2"'s should be highlighed because they are all "1" on the other sheet
User
08/02/2018 - 03:38
But just make sure it does not really need to be "2" and "1"
User
08/02/2018 - 03:38
Because on the real sheet is is a lot of differnt numbers that need to be compaired
Excelchat Expert
08/02/2018 - 03:39
Okay, please hold on. I will set the solution, and tell you the steps when done
User
08/02/2018 - 03:39
Ok
Excelchat Expert
08/02/2018 - 03:44
Simply need more time to create the formula so please bear with me
User
08/02/2018 - 03:44
ok
Excelchat Expert
08/02/2018 - 03:45
Thank you :)
Excelchat Expert
08/02/2018 - 03:58
Well, as you can see I've used a helper column at col B to help us identify which number does now exist
Excelchat Expert
08/02/2018 - 03:58
So, the color coding is the next part and I'm working on it, few minutes more
User
08/02/2018 - 03:59
But when I go change the numbers on sheet 2 it doesnt change the results
Excelchat Expert
08/02/2018 - 04:01
The formula had been concentrated in checking column A of sheet1 only, to see if it exist in sheet2 with 1003
User
08/02/2018 - 04:02
But it will sometimes exist
User
08/02/2018 - 04:03
So if there is one "2" in sheet 2 then it was not be highlighin sheet 1?
User
08/02/2018 - 04:03
I need to to copare it number by number
User
08/02/2018 - 04:03
so like
Excelchat Expert
08/02/2018 - 04:03
example the no.2 at sheet1, try to put 2 in sheet2 -1003
User
08/02/2018 - 04:03
I did
User
08/02/2018 - 04:04
See what I did there
User
08/02/2018 - 04:04
I need it to look like that
User
08/02/2018 - 04:04
besides the numbers dont need to be back to back like that
User
08/02/2018 - 04:05
I need it to find the numbers that are different and hi9ghlight them\
Excelchat Expert
08/02/2018 - 04:05
Alright, it will be having another formula
Excelchat Expert
08/02/2018 - 04:05
I have to change once more
User
08/02/2018 - 04:05
There is no easy way to have excel compare two largue sets of numbers for differnces?
Excelchat Expert
08/02/2018 - 04:06
we need to consider the value of your 2rd column while comparing it, right. This makes it a bit complex
Excelchat Expert
08/02/2018 - 04:06
the 2nd column like 1003
Excelchat Expert
08/02/2018 - 04:23
Please check the sample found in response to highlighting your 2-columns
[Uploaded an Excel file]
Excelchat Expert
08/02/2018 - 04:23
Usually, this are the steps on how to do the color coding
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.
