Excel - General Question on Pivot Table - Expert Solution

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.

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
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc