Detecting Repetitive List Choices

[Fujirich]

Hi folks - I'm a newbie here so be gentle. But I'm so glad I found this great resource!

I'm working on a project I could use some insight with. My problem is that I have 7 non-continous cells across a page, with each of these cells set up with data validation list-choices. Some of the data from the lists could be chosen as duplicates - something I want to alert a user to. So far, there are about 28 possible choices that may appear in those lists

I want to trigger a warning to the users if a duplicate choice is made in any of the seven cells. So I need some way to compare the chosen values, look for a duplicate, and return a simple value (True, False, or 1,2 - whatever) in order to trigger a warning that I'll format on the page.

Any ideas? Thanks in advance :D

 

[Egress1]

Howdy Fujirich,

Sub No_Dupes()
Dim MyValues(7)
For K = 1 To 7
For L = 1 To 7
If L = K Then L = L + 1
If L > 7 Then Exit For
If MyValues(K) = MyValues(L) Then
MsgBox ("Duplicate Picked")
GoTo DoSomething
End If
Next L: Next K
DoSomething:
End Sub


Something Like This?

 

[Fujirich]

Hi Ken:

Thanks for the idea, which may work great. I'm pretty weak on VBA skills, so it may take me a little to test it, but I will get back to you on the outcome.

I note that you're using a message box to signal the user of the duplicate. I'm actually wanting something even simplier - just a conditionally formatted warning right on the page that signals the problem, and then actually prints on the sheet in the event the user isn't wise enough to correct it.

As oposed to a VBA solution (which I'm sure is the most flexible and elegent), I was wondering if there are any array functions that could used to give me what I'm looking for.

Thanks...

 

[Egress1]

Actually the code I gave you has a pretty blatant flaw

You would need to add EXIT SUB or some other code after the loops are finished to keep the code from executing the DoSomething Label everytime.

In any case....what type of warning are you looking to perform?

 

[Fujirich]

Ken - as to the warning I'm going to use, I'll take a few unused cells in the visible and printed portion of the page (the page is a form used by me company to measure profitability and viability of a proposed deal) and format them to display some warning text and a different colored background whenever the duplicate condition exists. I need it to be clearly visible and printable to all who may use or see the sheet.

Thanks for coming back to the subject and making the correction.

 

[Egress1]

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1								1
2		2	3						2
3				4					1
4					2				1
5						6			2
6							7		1
7									1
8									9	<-SumOfI1:I7
9
10	1
11	2			ThereIsADuplicate
12	3							<-----MergedCellsFormattedWhiteOnWhite
13	4							ConditionalFormattedWhenI8>7Then
14	2							BlackOnRed
15	6
16	7	<----TheseCellsAreDefinedRange"TEST1"EachCellReferencesAnAddressOfOneOfyourSelectionCells
17
Sheet1

Ok This Doesn't Show very well but basically you can set up 7 consecutive cells to reference your non-consecutive cell values then define that as a range with INSERT:NAME:DEFINE menu options.

Then set up 7 cells anywhere with the formula checking the range for a duplicate. The sum of those checks should be 7 if no duplicate exists so if you can use that as the trigger ... You can't see it but I Conditional formatted a group of cells to display "DUPLICATE" warning message when the sum of the cells is greater than 7