Yet another Excel formula question for the experts!!

[absthechatter]

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

[TheNorm]

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.

[absthechatter]

Originally Posted by TheNorm

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.

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

[TheNorm]

Originally Posted by absthechatter

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

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?

[Aragorn]

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

[absthechatter]

Originally Posted by TheNorm

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?

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

[TheNorm]

Originally Posted by absthechatter

Not exactly, ...

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?

[absthechatter]

Originally Posted by TheNorm

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?

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

So therefore the example would not be allowed.

Abs

[absthechatter]

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

[TheNorm]

Originally Posted by absthechatter

Is anyone able to help on this ...

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

[absthechatter]

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

[TheNorm]

Ahhh...

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

[absthechatter]

Originally Posted by TheNorm

Ahhh...

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

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