Data comparsion in column

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hello,
So I have sort of a 2 prong question. Summary I have a LARGE file with over 100 columns and many rows. The below is what I am trying to do in 2 waves.


  1. Are there any differences in a column. For example do they all say A in them or do some also say B. The size of the file makes it cumbersome on what i thought were workarounds.
  2. If I set a standard so all data in this column should be A; then a trigger will tell me these rows for this column don't fit your criteria.

Thanks in advance!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

abiblequestion

New Member
Joined
Jan 19, 2018
Messages
3
I would use a vlookup or Match formula to comparte the two collums.
for the match you can use =A2=b2 (or critiera) if no match then the output is false otherwise the output will be true.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
If I set a standard so all data in this column should be A; then a trigger will tell me these rows for this column don't fit your criteria.
One simple way would be to use Conditional Formatting to highlight all the cells that do not meet your criteria.
For example, if the value you want to match to is in cell C1, and you want to check all the values in column A, then just highlight column A and use the following Conditional Formatting formula:
Code:
=AND(A1<>"",A1<>$C$1)
and choose your formatting color.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Ok so as I think about this that could replace my first step. if I want my standard to be X and they all say Y then I would want something to alert me. I will test this and comment back thank you!
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Is there a way to pull that into a new tab called variances? for easy review. Just because the file is so large with many rows and columns.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Is there a way to pull that into a new tab called variances? for easy review. Just because the file is so large with many rows and columns.
I would recommend changing your approach then, and going with Advanced Filters.
See the "Extract Data to Another Worksheet" section here: http://www.contextures.com/xladvfilter01.html
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top