How arrange, concatenate and be alerted if a duplicate when entering.

[arnold_p]

I am looking for help to accomplish the following task:

• Enter a sequence of 5 numbers with each number being 2 digits (e.g. 2=02, 10=10, etc.)
• Arrange sequence from smallest to largest
• Then concatenate sequence
• Then alert if concatenated sequence is a duplicate

Thank you for your anticipated help.

 

[Rick Rothstein]

I am looking for help to accomplish the following task:

• Enter a sequence of 5 numbers with each number being 2 digits (e.g. 2=02, 10=10, etc.)
• Arrange sequence from smallest to largest
• Then concatenate sequence
• Then alert if concatenated sequence is a duplicate

Thank you for your anticipated help.

Where is this sequence of 5 numbers being entered... different contiguous cells in a row or a column (and starting at which cell)? Where is the outputted, concatenated sequence to go at? What delimiter do you want between the values being concatenated?

 

[arnold_p]

[TABLE="width: 649"]
<colgroup><col width="64" style="width: 48pt;" span="12"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Cell 1[/TD]
[TD="width: 64, bgcolor: transparent"]Cell 2[/TD]
[TD="width: 64, bgcolor: transparent"]Cell 3[/TD]
[TD="width: 64, bgcolor: transparent"]Cell 4[/TD]
[TD="width: 64, bgcolor: transparent"]Cell 5[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 97, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]33[/TD]
[TD="bgcolor: transparent"]Then[/TD]
[TD="bgcolor: transparent"]02[/TD]
[TD="bgcolor: transparent"]07[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]20[/TD]
[TD="bgcolor: transparent"]33[/TD]
[TD="bgcolor: transparent"]Then[/TD]
[TD="bgcolor: transparent"]0207132033[/TD]
[/TR]
</tbody>[/TABLE]

Enter 5 numbers, then arrange, then concatenate then alert if duplicate. Hope this helps explain....

 

[Rick Rothstein]

[TABLE="width: 649"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Cell 1[/TD]
[TD="width: 64, bgcolor: transparent"]Cell 2[/TD]
[TD="width: 64, bgcolor: transparent"]Cell 3[/TD]
[TD="width: 64, bgcolor: transparent"]Cell 4[/TD]
[TD="width: 64, bgcolor: transparent"]Cell 5[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 97, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]33[/TD]
[TD="bgcolor: transparent"]Then[/TD]
[TD="bgcolor: transparent"]02[/TD]
[TD="bgcolor: transparent"]07[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]20[/TD]
[TD="bgcolor: transparent"]33[/TD]
[TD="bgcolor: transparent"]Then[/TD]
[TD="bgcolor: transparent"]0207132033[/TD]
[/TR]
</tbody>[/TABLE]

Enter 5 numbers, then arrange, then concatenate then alert if duplicate. Hope this helps explain....

Assuming your first set of numbers goes in Row 1, cells A thru E, (skipping a column) put this formula in G1 and copy it across to K1...

=IF(A1="","",TEXT(SMALL($A1:$E1,COLUMNS($A:A)),"00"))

Then (again, skipping a column) put this formula in M1...

=G1&H1&I1&J1&K1

Then (again, skipping a column) put this formula in O1...

=IF(AND(M1<>"",COUNTIF(M:M,M1)>1),"Duplicate","")

These formulas may then be copied down as needed.

 

[arnold_p]

Can the formula be consolidated to show just the concatenated result. Also, can it be created to be used as a VBA function?

 

[Rick Rothstein]

Can the formula be consolidated to show just the concatenated result.

Everything about Message #1 and Message #3 said you wanted those intermediate, sorted results, which is why I gave them to you. Here is the formula that does the concatenation you want directly (without those individual sorted values)...

=IF(A1&B1&C1&D1&E1="","",TEXT(SMALL(A1:E1,1),"00")&TEXT(SMALL(A1:E1,2),"00")&TEXT(SMALL(A1:E1,3),"00")&TEXT(SMALL(A1:E1,4),"00")&TEXT(SMALL(A1:E1,5),"00")

Also, can it be created to be used as a VBA function?

Nowhere in either of your previous messages did you say anything about creating a VBA function. Anyway, here is one...

Function SortedCombo(Rng As Range) As String
  SortedCombo = Evaluate(Replace("TEXT(SMALL(@,1),""00"")&TEXT(SMALL(@,2),""00"")&TEXT(SMALL(@," & _
                "3),""00"")&TEXT(SMALL(@,4),""00"")&TEXT(SMALL(@,5),""00"")", "@", Rng.Address))
End Function