Excel Question Repeat Numbers

Winalot2007 · #1 12-24-2009, 11:31 PM

Need help with Excel formula.

I have a spreadsheet with pick 3 combinations.

In cells H20:J20 I have the digits 1 4 3

In cells H21:J21 I have the digits 4 4 0

I would like to find out how many digits repeated from the previous drawing.

The previous drawing in this case was 4 4 0.

So the answer should be 1 because the 4 repeated.

2 0 3
3 4 2

Answer would be 2. The 2 and the 3 repeated from the previous drawing 3 4 2.

If anyone could help it would be greatly appreciated!!

Thanks

Winalot2007 · #2 12-25-2009, 12:02 PM

Looking for a formula to create an ascending combo (or boxed combo).

For example:

In cell a1 I have combo 213

In cell b1 I would like to have 123

Is there an alternative formula than using morefunc add-in.

--MCONCAT(VSORT(MID(A1,INTVECTOR(LEN(A1),1),1)+0,,1) )

Thanks!!

Kenya649 · #3 12-29-2009, 12:51 AM

Quote:

Originally Posted by Winalot2007

Need help with Excel formula.

I have a spreadsheet with pick 3 combinations.

In cells H20:J20 I have the digits 1 4 3

In cells H21:J21 I have the digits 4 4 0

I would like to find out how many digits repeated from the previous drawing.

The previous drawing in this case was 4 4 0.

So the answer should be 1 because the 4 repeated.

2 0 3
3 4 2

Answer would be 2. The 2 and the 3 repeated from the previous drawing 3 4 2.

If anyone could help it would be greatly appreciated!!

Thanks

Hi Winalot2007,

Not sure if you solved the above problem?

I have an Excel Macro that can assist you.

You draw has to be from earliest to the latest going down and on column H-J and may be from H2 downwards

Hope you know how to add a macro in Excel

The macro is as follows and I belief there is an alternative way

Sub comp()
Dim x(10, 1)
For Each c In Range("H2:H1000")
For i = 0 To 9
x(i, 0) = -1
x(i, 1) = 0
Next
If c.Value <> Empty Then
x(c.Offset(0, 0).Value, 0) = c.Offset(0, 0).Value
x(c.Offset(0, 1).Value, 0) = c.Offset(0, 1).Value
x(c.Offset(0, 2).Value, 0) = c.Offset(0, 2).Value
If c.Offset(1, 0).Value <> Empty Then
If x(c.Offset(1, 0).Value, 0) > -1 Then x(c.Offset(1, 0).Value, 1) = 1
If x(c.Offset(1, 1).Value, 0) > -1 Then x(c.Offset(1, 1).Value, 1) = 1
If x(c.Offset(1, 2).Value, 0) > -1 Then x(c.Offset(1, 2).Value, 1) = 1
p = 0
For i = 0 To 9
If x(i, 1) > 0 Then p = p + 1
Next
c.Offset(1, 3).Value = p
End If
End If
Next
End Sub

Thanks

Winalot2007 · #4 12-29-2009, 10:21 AM

Thanks for all your help.

These are 2 additional formulas I received:

=IF($B20="","",MIN(IF(COUNTIF(D20:F20,D21)>0,1)+IF (COUNTIF(D20:F20,E21)>0,1)+IF(COUNTIF(D20:F20,F21) >0,1),IF(COUNTIF(D21:F21,D20)>0,1)+IF(COUNTIF(D21: F21,E20)>0,1)+IF(COUNTIF(D21:F21,F20)>0,1)))

The second requires Ctrl>Shift>Enter

={IF($B20="","",SUMPRODUCT(IF(COUNTIF(H20:J20,{0,1 ,2,3,4,5,6,7,8,9})<COUNTIF(H21:J22,{0,1,2,3,4,5,6, 7,8,9}),COUNTIF(H20:J20,{0,1,2,3,4,5,6,7,8,9}),COU NTIF(H21:J21,{0,1,2,3,4,5,6,7,8,9}))))}

The most current drawing is at the top in cells D20 through F20. Comparing them to the previous drawing in cells D21 through F21

Thanks again!!