Excel Formula 2

[vmgodfrey]

I have question. I am trying to do a true false statement that will return the vaildations as Duplicates or Not Duplicates using just an excel formula for 6 columns. Heres an example
Columns:
Row# - RowAlpha - Lot# - LotAlpha - Space# - Space Aplha
1 C 1
1 C 2
2 A 1
I have tried the validation using Sql, but for the column cells that are blank above a cell that has a value, it nulls everything in that column and I can't pin point the duplicates because it is reading the blanks as dups.
I've tried tagging, highlighting and counting but m=nothing works.
HELP

 

[xlbo]

I don't think I'm the only one to which this makes no sense......
If you need help, I'd think about rephrasing the question

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[vmgodfrey]

xlbo
Sorry. The only way I think I can explain is:
I have an excel sheet with about 8000 lines of data. There are 6 columns and 3 are alpha columns and 3 are numeric columns. The columns combined are like address that can not and should not be duplicated. On the spreadsheet the first row may have data in columns 1, 4, 5 and on the second row it may have data in columns 2, 3 and 6. How can I pin point duplicates or create the validation to pop up a message letting them now they are entering duplicate information?

 

[xlbo]

aaah - I think I get it now. I asume that the "address" would be the combination of ALL the 6 columns ? ??
Would it matter if it was duplicated but in a different order
ie
A1B2C3
B2A1C3
would the 2nd one be counted as a duplicate ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[vmgodfrey]

No 2nd would not be a duplicate.

 

[xlbo]

tricky - do you want to stop duplicates happening or check to see if you have duplicates ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

Select the ENTIRE column (using the column headers) for all of the Alpha and Numeric cells that are used to calculate the "Address" (make sure a cell in the first row is activated) and then select Data->Validation... Select "Custom" from the drop-down list.

Enter the following formula into the Formula field at the bottom of the Form:

=COUNTIF($A:$A;$A1)=1

This assumes that the Column A is the column that contains the "Addresses". If the "Addresses" are calculated elswhere, you will have to change the formula (e.g. The "Addresses" are in Column G, you will need the following formula

=COUNTIF($G:$G;$G1)=1

If you did this correctly, you shouldn't be able to enter any values into the Alpha or Numeric cells that would cause your "Address" cell to become a duplicate.


I hope this helps!!



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Bowers - this is what makes it tricky - I believe that the addresses are the concatenation of the 6 fields so we would have to check that the concatentation isn't duplicated

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

Geoff,

That is what I meant with "calculate the "Address""

I tested this in a workbook where the addresses were being concatinated in column A from columns B,C,D,E & F.

Cell A1 had the formula =B1&C1&D1&E1&F1.

After setting the Data Validation for columns A:F as stated in my previous post, the user can't change any of the cells if it would cause the new concatinated address value to be the same as any of the other concatinated addresses.



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

aaaaaaah - cunning - I was trying to come up with a way of doing it without the concatenated address but that's probably the easiest way to go about it

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

And isn't the easiest way, usually the best way? ;-)

As long as you get what you want, that is!

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

darn tootin' it is

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[vmgodfrey]

I am trying it now, but i forgot to mention that not all the 6 cells in the columns would have data, at the most it would be four.

 

[Bowers74]

It should still work no matter how many columns contain data. As long as you enter it just like I said, you shouldn't be allowed to create any duplicates in your address column.

If you have any questions, just post them here. I will be glad to help.



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

shouldn't really matter. The formula is checking the concatenated column so:

=B2&C2&D2&E2&F2&G2

for
B2 = A
C2 = 1
D2 = C
E2 = 2
F2 =
G2 =
would give
A1C2

and for

B2 =
C2 =
D2 = A
E2 = 1
F2 = C
G2 = 2

would still give A1C2


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[tav1035]

Question->
When using the suggested formula =B2&C2&D2&E2&F2&G2
it appears that you have duplicates in column 'A'
but looking at the data, it appears that there aren't any duplicates (because of the blanks not being identified).
Is this a problem? If so, how can this be resolved?


Row# RowAlpha Lot# LotAlpha Space# SpaceAlpha
2B1A 2 B 1 A
3A1A 3 A 1 A
3A1A 3 A 1 A Duplicate
3C1AC 3 C 1 A

tav

 

[vmgodfrey]

The 3rd row would not be a duplicate. Only if the data is identical by row, lot or space using the same characters in each cell.
The formula didn't work.

 

[xlbo]

in that case, try using this to concatenate instead:
=if(B2=""," ",B2)&if(C2=""," ",C2)&if(D2=""," ",D2)&if(E2=""," ",E2)&if(F2=""," ",F2)&if(G2=""," ",G2)

and then apply Bowers' data validation formula....

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[vmgodfrey]

THANKS Bowers74 and xlbo. IT WORKED!!!!!

 

[Bowers74]

Glad to know that we could help vmgodfrey.

Good Luck!



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!