Comparing cells in different columns 1

[Ebes1099]

I have a number of columns that contain a letter and I want to be able to check if all letters of "selected" columns match. I'm using an indicator in row 2 with either a '1' or '0' to indicate whether the column is selected.

I know the Exact function will return a True or False for comparing cells of strings, but I only want to compare the cells that are in selected columns. Is there a way I can do this?

 

[SkipVought]

Hi,

Well the short answer (Excel 2007+) is the COUNTIF() or COUNTIFS() function.

But I suspect that there's more to this question.

Please post a relevant sample of the data in question and the result you expect.

 

[Ebes1099]

I've attached an example of what it could look like in a couple different scenarios.

The Selected row with 1's and 0's will be dynamic and based on user inputs on another tab. There could be up to 50 columns so a few simple if statements won't work.

I think something with COUNTIFS might work...maybe you'll lead me in the right direction.

 

[SkipVought]

=IF(COUNTIFS(Code,Combined_Value,Selected,1)>0,Combined_Value,"some other generic text string")

 

[SkipVought]

BTW, I failed to mention that I used NAMED RANGES based on your example.

 

[Ebes1099]

Skip, that works awesome if I have a defined value for Combined Value. But I don't necessarily know what that will be, in my example case I highlighted that cell in Yellow with a fixed value in because that's what I wanted the result to be. In reality, it could be any of the values in that row (D, A or V). If the first selected ID was a plan with an A, I might want to check if all other selected plans are "A". Or if it was a V, then I'd check whether they are V's.

What I'm trying to say is I won't know what the first Criteria Value is for the Countifs, unless I'm misunderstanding what your named range for "Combined_Value" is.

 

[Ebes1099]

Skip, I'll just use an Index-Match to find the first cell with a 1 in the Selected row.

Thanks for the help.

 

[SkipVought]

Your explanation and example together were not at all clear!

Are you saying that if ALL the corresponding Code values are the same for ALL the Selected values of 1, then return the Code value, for ID values 001 thru 005?

 

[SkipVought]

Well I'm gonna assume that I might be correct, in which case this might work for you.

[tt]
=IFERROR(IF(CHAR(SUMPRODUCT((Selected=1)*(CODE(Code)))/COUNTIF(Selected,1))=INDEX(Code,MATCH(1,Selected,0)),"some other generic text string"),"some other generic text string")
[/tt]

 

[Ebes1099]

Skip,

I ended up doing this, where Code is my named range row where the Code values are and Selected is my named range row where the Selected Values are.
=IF(COUNTIFS(Code,INDEX(Code,MATCH(1,Selected,0)),Selected,1)>=SUM(Selected),INDEX(Code,MATCH(1,Selected,0)),"Mixed")


The issue is there could be any number of ID's loaded into the workbook. I just used 001-005 as an example. The selected plans might start with the first column or they might not. It could by 001, 002 and 005 selected. Or it could be 003, 004 and 005. I will not know what the Code value is for the first plan selected, that's why I ended up using my Index-Match.

All I need to do is find the Code value of any selected plan, then compare that to all other selected plans. If they all match, then it's easy and I can return whatever code value I found and was comparing to. If they don't match, I don't care what the value is, I'll return a string that just says "Mixed".

 

[SkipVought]

Well that's exactly what my latest code does. It could extend to more than 001-005 if you need to. Just redefine the Code & Selected ranges.

 

[Ebes1099]

That's what I figured...thanks for the help.