Yet another Excel formula question for the experts!!
 

Just when I thought I'd got my head around this formula business!!!!

Is it possible to write a formula (or better still, what is the formula) to check that any entries in column A that are identical, all have the same entries in columns B,C and D (although these entries are different from column A)?
i.e
A1 = one, B1 should = Dog, C1 should = Cat, D1 should = Fish.

A2 = two, B2 shoul = Elephant, C2 should = Giraffe, D2 should =Bird.

Now if A3 = one, instead of B3, C3 and D3 being Dog, Cat and Fish, it is Dog, Elephant and Fish, I want the formula to show me that the two are not equal.

There will be quite a few entries, so will probably need an array as well!!

Many thanks for all your help (and patience!)

Abs

Re: Yet another Excel formula question for the experts!!
 

Interesting. Another way of looking at this is to say that the content of every row should be unique. Agreed?

Have a look at Method 2: http://www.mrexcel.com/tip138.shtml

In your case, you would need to add E, which concatenated cells A to D to make a long string. You would then test the uniqueness of it.

Re: Yet another Excel formula question for the experts!!
 

Thanks TheNorm

Does not do quite what I want as the contents of every row would not need to be unique. All of the one's should be unique, all of the two's unique, etc.

I could look at this by filtering. The problem is, that the ones, twos etc, will amount to thousands, and that's a lot of filtering!

Many thanks

Abs

Re: Yet another Excel formula question for the experts!!
 

They would be if you included them in the string. To use your example:

E1 = A1 & B1 & C1 & D1 = oneDogCatFish
E2 = A2 & B2 & C2 & D2 = twoElephantGiraffeBird
E3 = A3 & B3 & C3 & D3 = oneDogElephantFish

You want every E to be unique. No?

Re: Yet another Excel formula question for the experts!!
 

I guess you could have a separate vlookup table with the A values and the expected concatenation (or sum) of the other values.

eg, Vlookup sheet contains
one DogCatFish
two ElephantGiraffeBird

Then you formula would be something like

=If(concatenate(B1,C1,D1)=vlookup(A1,table),"True" ,"False")

HTH

Re: Yet another Excel formula question for the experts!!
 

Not exactly, I want every E to be unique for all the different catagories in column A, and for the formula to tell me if they are not unique.

Many thanks

Abs

Re: Yet another Excel formula question for the experts!!
 

Uhmmmm....

Do you mean:

For every value of A, the combined values in B, C and D should be unique; let me know if this is not the case.

What if:

A1 = one, B1 = Dog, C1 = Cat, D1 = Fish.

A3 = one, B3 = Dog, C3 = Elephant, D3 = Fish

A4 = one, B4 = Fish, C4 = Elephant, D4 = Dog << Would this be allowed?

Re: Yet another Excel formula question for the experts!!
 

I think that's it, for every value of A, the combined values of B,C and D should be unique! My bad explaining:monkey:

So therefore the example would not be allowed.

Abs

Re: Yet another Excel formula question for the experts!!
 

Is anyone able to help on this last part I need, I am after a formula that can check that for every value of Column A, the combined values in Columns B, C and D should be unique and to report back any that are not.

Many thanks

Abs

Re: Yet another Excel formula question for the experts!!
 

As far as I can see this cannot be done using a worksheet formula. I'm sure a VBA macro could do it - would you be happy running code?

Alternatively, if this is a "one-off", there might be a workaround using copy, sort, paste, and a simple formula.

Over to you...

Re: Yet another Excel formula question for the experts!!
 

Macros are fine.

Unfortunately not a one off. Will be used by a variety of people for quite some time. There will be around 30,000 entries, so the sorting, filtering and cutting/pasting is a no no really.

Many thanks

Abs

Re: Yet another Excel formula question for the experts!!
 

Ahhh...

This is where I bow and exit stage right. Macros are not really my forte.

Re: Yet another Excel formula question for the experts!!
 

Not mine either really. Easy enough to record them, but writting from scratch!! At least I get a bump out of it though, and someone may know...

Abs