Need Help - Formula

[ststern45]

Master Set of 3 numbers between 0 through 9
Cell A1 = 4
Cell B1 = 9
Cell C1 = 0

Next 3 sets of number between 0 through 9

Cell A2 = 2
Cell B2 = 2
Cell C2 = 9

Cell A3 = 4
Cell B3 = 7
Cell C3 = 3

Cell A4 = 6
Cell B4 = 9
Cell C4 = 6

The remainder of the cells below are as follows that stay the same values:

A5 = 0, B5 = 0, C5 = 0
A6 = 1, B6 = 1, C6 = 1
A7 = 2, B7 = 2, C7 = 2
A8 = 3, B8 = 3, C8 = 3
A9 = 4, B9 = 4, C9 = 4
A10 = 5, B10 = 5, C10 = 5
A11 = 6, B11 = 6, C11 = 6
A12 = 7, B12 = 7, C12 = 7
A13 = 8, B13 = 8, C13 = 8
A14 = 9, B14 = 9, C14 = 9

If I align the values as follows
C1-C2-C3
Row 01 - 4 - 9 - 0
Row 02- 2 - 2 - 9
Row 03 - 4 - 7 - 2
Row 04 - 6 - 9 - 6
Row 05 - 0 - 0 - 0
Row 06 - 1 - 1 - 1
Row 07 - 2 - 2 - 2
Row 08 - 3 - 3 - 3
Row 09 - 4 - 4 - 4
Row 10 - 5 - 5 - 5
Row 11 - 6 - 6 - 6
Row 12 - 7 - 7 - 7
Row 13 - 8 - 8 - 8
Row 14 - 9 - 9 - 9

4-9-0 is the main set
Below I calculate how far back until all 3 values 4-9-0 appear which is 4

The formula I am trying to figure out is the following
The 4 from the 4-9-0 appears in the 1st column in the 3rd row under the 2-2-9
The 9 from the 4-9-0 appears in the 3rd column in the 2nd row under the 4-9-0
The 0 from the 4-9-0 appears in both the 1st, 2nd, and 3rd columns on the 5th row under the 6-9-6

This is the outcome I'm trying to achieve:
Since the digits 4-9-0 appear in the 4 rows below the 4-9-0 (see below)
C1 C2 C3
4 - 9 - 0

x - x - 9
4 - x - x
x - x - x
x - 0 - x

The final result I am looking for is the following:
The 9 = C3 (Column 3) since it was the 1st digit to appear below the 4 - 9 - 0
The 4 = C1 (Column 1) since it was the 2nd digit to appear below the 4 - 9 - 0
The 0 = C2 (Column 2) since it was the 3rd and final digit to appear below the 4 - 9 - 0

So my final outcome would be C3, C1, C2 or 3 - 1 - 2

Some digit will repeat on the top line so for example, instead of 4 - 9 - 0 it cold be 4 - 4 -4 , 4 - 4 - 0, 4 - 9 -9 etc.

Any help would be greatly appreciated.

Thank you in advance!!

 

[OaklandJim]

I am really trying to understand the outcome and how to get there. I know that you put a lot into the description but I got confused. Maybe try again to describe the outcome and the steps to get there.

For example, why does the order of appearance in the RESULT go from 9 - 4 - 0 (columns 3 - 1 -2 ) when the ORIGINAL value was 4 - 9 - 0?

Why is the zero "found" in the SECOND column and not the first one? The first zero is in column 1.

How is the first result -- how far back until all 3 values 4-9-0 appear which is 4 -- used in the outcome?

 

[ststern45]

From top to bottom the main number is 4 - 9 - 0
Below 4 9 0 I want to calculate what number hits first from the set of numbers below
Example, the 9 is the 1st number that appears in the 3rd column as 2 - 2 - 9 so the result would be 3 or column 3
The next digit 4 appears in the 1st column as 4 - 7 - 2 so the result would be 1 or column 1
The last digit 0 appears on row 5 as 0 0 0. Since we already have 3 for column 3, 1 for column 1, the last result would be 2 or column 2 which is the remaining column not selected.
There will be times when 2 to 3 number from the Main number will appear on the same row. It's just matching where the digit(s) hit below and in what column under the Main number.

I'm sorry for the confusion
Thank you for your reply!!

C1-C2-C3
Row 01 - 4 - 9 - 0 (Main Number)
Row 02- 2 - 2 - 9
Row 03 - 4 - 7 - 2
Row 04 - 6 - 9 - 6
Row 05 - 0 - 0 - 0
Row 06 - 1 - 1 - 1
Row 07 - 2 - 2 - 2
Row 08 - 3 - 3 - 3
Row 09 - 4 - 4 - 4
Row 10 - 5 - 5 - 5
Row 11 - 6 - 6 - 6
Row 12 - 7 - 7 - 7
Row 13 - 8 - 8 - 8
Row 14 - 9 - 9 - 9

 

[OaklandJim]

I suspect that this take a bit of back and forth. I need to understand the logic or I cannot write code to implement it.

I kind of understand...but not totally. Nine appears first, before the other digits and is in column three. Three has not been to assigned to another digit so that is the value for nine. Four appears next (before 0) and is in column one. One has not been assigned yet to a digit. So four's value is one. Zero appears next, in column one. But one has been assigned to another digit. The next occurrence is in column two so zero gets the value of one.

Please provide a few more examples so I can tell if code/logic is working. I hope that I can implement this.

Maybe if I understand what this is for it will help me to imagine why the logic is how it is.

 

[ststern45]

Here is another example:

5 - 8 - 5 is the set to analyze

The 8 appears 1st on the 1st row below so the result is C2 (column 2) or just the value 2 for column 2
The 5 appears 2 times from the 5 8 5
Both of the 5's occur on the 2nd row below the 5 8 5.
The 1st 5 from the 5 - 8 - 5 the result would be C1 (column 1) or just the value 1 for column 1
The 2nd 5 from the 5 - 8 - 5 the result would be C2 (column 2) or just the value 2 for column 2
In this example both the 8 & 5 appear in the same C2 (column 2)
So the final result would be 1 - 2 - 2
I've tried a number of formulas.

5 - 8 - 5

x - 8 - x
5 - 5 - x


5 8 5

7 8 0
5 5 4
7 7 0
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9

CountExample1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		C1	C2	C3									P1	P2	P3
2		5	8	5				1	2	2	2		2	1	2
3		7	8	0		1
4		5	5	4		2
5		7	7	0		3
6		0	0	0		4
7		1	1	1		5
8		2	2	2		6
9		3	3	3		7
10		4	4	4		8
11		5	5	5		9
12		6	6	6		10
13		7	7	7		11
14		8	8	8		12
15		9	9	9		13
Example1

Cell Formulas
Range	Formula
H2	H2	=VALUE(howfar($B2:$D15,1))
I2	I2	=VALUE(howfar($B2:$D15,2))
J2	J2	=VALUE(howfar($B2:$D15,3))
K2	K2	=MAX($H$2:$J$2)
M2	M2	=MIN(MATCH($B2,$B$3:$B$15,0)-0,MATCH($B2,$C$3:$C$15,0)-0,MATCH($B2,$D$3:$D$15,0)-0)
N2	N2	=MIN(MATCH($C2,$B$3:$B$15,0)-0,MATCH($C2,$C$3:$C$15,0)-0,MATCH($C2,$D$3:$D$15,0)-0)
O2	O2	=MIN(MATCH($D2,$B$3:$B$15,0)-0,MATCH($D2,$C$3:$C$15,0)-0,MATCH($D2,$D$3:$D$15,0)-0)

 

[ststern45]

Cell H2, I2, J2 contain the How Far Back formula that calculates how many rows down until all 3 digits in cell range B2 through D2 appear below.
Cell K2 is the maximum number of rows from B2 through D2
I tried using a couple methods without any success.
Due to the number of possible outcomes this may be a lost cause.
The Match formulas in cells M2, N2, O2 kind of give me a starting point.
Creating a formula that will calculate what column where each of the 3 digits from B2, C2, D2 appear based on the results below.
I had to add the values 000 through 999 in cell range B6 through D15 in order to pick up any values from B3 through D5 that are not in this group.

Thank you for your assistance!!

This is the "how far back" function code:
Public Function HowFar(ByVal MyRange As Range, ByVal MyCount As Long)
Dim MyData As Variant, i As Long, j As Long, k As Long

MyData = MyRange.Value
For i = 2 To UBound(MyData)
For j = 1 To 3
For k = 1 To 3
If MyData(i, j) = MyData(1, k) Then
MyCount = MyCount - 1
MyData(1, k) = "x"
If MyCount = 0 Then
HowFar = i - 1
Exit Function
End If
Exit For
End If
Next k
Next j
Next i

HowFar = ""
End Function

 

[OaklandJim]

Yes this is very convoluted logic. I am not sure that I can do this in code. I'll try a bit more but it seems that I'll have to give up. Wish me luck.

 

[ststern45]

If it's too involved I don't want you to spend any time on this.
I think it's harder than I thought. I spent a good afternoon trying different formulas.
I do appreciate you taking the time.
Thanks you

 

[OaklandJim]

I'm a retired old fart and these types of projects keep my brain working. So if I think there is even a modest chance of success I'll keep grinding.

Maybe if I know what this is for I'd be more inclined to work on it.

So far I have functions that get the position of the first occurrence value in the data and that get the position of the second occurrence of a value in the data. So I can now figure out what order to use when processing. I also have functions that 1. find the first column that a value is in and 2. find the second column that a value is in.

How close that gets me to a solution is not clear yet. But IT IS progress. I think that I can get it...

 

[ststern45]

You and I are old farts.
The formulas are for a pick 3 lottery game. Using straight results, gather the column data, do more analysis.
Thanks for the efforts.