Identifying cells that match each other.

[Neil07979]

Hi all,

I have 4 columns within a spreadsheet - and each of these columns contain a text value.

I am trying to identify in, Column A, within each row if any of the cells contain the same value, and if so I will Omit them from the further calculation.

The columns I am using are apart from each other, and not available, to my knowledge, as a range.

Some cells may be blank - so need this to be taken into consideration.

Any idea's are welcome.

Thanks

[NBVC]

you mean something like?

=IF(OR(B1=A1,D1=A1,K1=A1),"duplicate","none") copied down

where B1, D1 and K1 are compared to A1

[Neil07979]

Something like that - however, I want to compare all of them to each other.

So: A1 = D1 or D1 = K1 or K1 = J1 ... etc.

Would there be a way to do this?


I basically need to identify if any of the 4 columns - are equal to each other. If they are - I will then exclude this from any other calculation.

[NBVC]

Probably easiest just to add all the possibilities within the OR() function...

e.g. =IF(OR(A5=C5,A5=E5,A5=H5,C5=E5,C5=H5,E5=H5),"duplicates","none")

[Neil07979]

Hmm - always an option - although I was hoping not to do this - as the number of Columns may increase, which means the possibilities could be very large.

Also I wanted to build it into another formula, so wanted to keep the size down.

wouldn't there be an array formual that I could use to keep this as neat as possible?

This appears also to take a blank cell as active, and states that I have a duplicate if there are 2 blank cells?

[NBVC]

Can you post a sample of what you've got and what result you wish to get?

[Neil07979]

I have attached a sample file for you to have a look at.

Rule 4 is what I am trying to do - I have inserted "Duplicate" where I believe they should be. The "source" could grow to as many as 20 in the future so want to try and have something that is simple to add to.

Ideally - I would want to build rule 4 in to rules 2 and 3, where if the rule is True, but the data sources in question are duplicated - then have false - but happy to just have a seperate rule for now that looks at all of the sources and states if duplicate.


Thanks for looking at this.

[NBVC]

Try this formula in E3:

Please Login or Register  to view this content.

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER.

Then copied down.

I am assuming that columns between Source and Diff are either blank or numeric...and that only the Source columns have actual text in them.

[Neil07979]

Thanks - this seems to work - so far so good.

Dont know how you do it, but as always - an answer to every question.

Thanks again.