Return True/False if all cells in column are the same.

jmitchells5w

New Member
Joined
Jun 14, 2007
Messages
35
I am trying to find a way to use a formula to compare text in a column labeled UOM (Unit of Measure) to see if all cells are the same. I have a list of purchases and the UOM should be the same but occcasionally they are different. e.g. purchase based on "EA" (eaches) instead of "/M" (thousands). This, of course, skews my sum formulas. These lists are created using sumproduct. Any ideas? Also, the UOM may be different depending on what is purchased. I don't care what the UOM is, it just needs to be the same. Thanks in advance for your help!!!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Maybe something like: =COUNTA(A2:A100)<>COUNTIF(A2:A100,A2)

Will return TRUE if there are any differences in the range.

Dom
 

jmitchells5w

New Member
Joined
Jun 14, 2007
Messages
35
Thanks Domski, but this doesn't work because I am using sumproduct. example:

Company QTY UOM
ABC 100 EA
BBB 200 EA
ABC 10 /M
BBB 300 EA

I am using sumproduct to add the QTY column for company ABC. The formula looks like =sumproduct(--(A:A="ABC"),--(B:B)). I need a formula to look at the UOM for ABC to make sure they are all the same. Depending on which company I'm looking at the UOM will change.

I need a formula to assure me that all UOM for a particular company are the same because I'm working on a different spreadsheet. My data sheet has at least a dozen companies, and 3 or 4 more criteria than just the company name when adding QTY.
 

jmitchells5w

New Member
Joined
Jun 14, 2007
Messages
35
One option I come up with is to use the vlookup formula in a cell somewhere on the spreadsheet to look up a company name and return the UOM of the first purchase. I then use the sumproduct formula as follows:

D1 = vlookup("ABC",datatable,12,false)
C1 = Sumproduct(--(A:A="ABC"),--(B:B=D1))

This will confirm that the UOM's for one company are all the same. The only problem is that I can be comparing up to 12 companies at a time (from a list of 100's) and want to make sure the UOM is the same for all purchases from all companies (that I'm comparing) when adding up quantities.

I'm still searching for a better answer.
 
Joined
Jun 10, 2009
Messages
22
I could be reading your question wrong, but try using using the EXACT function in Excel. Exact compares two strings and tells you if they are the same (TRUE) or not (FALSE).
 

jmitchells5w

New Member
Joined
Jun 14, 2007
Messages
35
I believe EXACT only works when comparing 2 cells. I am comparing hundreds. Also, the cells I am comparing are not in a stand-alone list. It is a virtual list created using the sumproduct formula. Thank you though for your input!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,709
Messages
5,597,693
Members
414,164
Latest member
ARTW

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