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!!!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Maybe something like: =COUNTA(A2:A100)<>COUNTIF(A2:A100,A2)

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

Dom
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top