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 apologize but I am confused about how to determine the final result.

I have all of the infrastructure to do what you want but how my pieces fit together is not clear from your descriptions of results.

Regarding

Values -- Row and Column Lookup.xlsm
	B	C	D	E
1	C1	C2	C3
2	5	8	5
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
Sheet2

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

This seems to indicate that result values 1. are in the of digits' order occurrence and 2. the ROW that is recorded for the values. So order is 8 - 5 - 5 and the respective value in the result is 1 - 2 - 2.

However regarding this...

Values -- Row and Column Lookup.xlsm
	B	C	D	E
1	C1	C2	C3
2	4	9	0
3	2	2	9	1
4	4	7	3	2
5	6	9	6	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
Sheet1

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
he 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

This seems to indicate that result values 1. are in the of digits' order occurrence and 2. the COLUMN is recorded for the digits. So order is 9 - 4 - 0 and the respective value in the result is 3 - 1 - 2.

Am I misunderstanding something?

If not, which is it, record the row for digits or the column?

 

[Snakehips]

Not sure what is the collective noun for xlfarts is but maybe we just regard ourselves as a trio?
I am similarly confused yet intrigued by this.

Thought I may have cracked it with a modified HowFar function.
Added a third parameter, Wot. To be 1, 2, or 3 to return either the number, the rows down or the column.

Public Function HowFar(ByVal MyRange As Range, ByVal MyCount As Long, ByVal Wot As Integer)
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
Num = MyData(1, k)
MyData(1, k) = "x"
If MyCount = 0 Then

Select Case Wot
    Case 1 'Number matched
    HowFar = Num
    Case 2 'Rows Down
    HowFar = i - 1
    Case 3 'Column
    HowFar = j
End Select

Exit Function

End If
Exit For
End If
Next k
Next j
Next i

HowFar = ""
End Function

Used COLUMNS and ROWS functions rather than hard coding 1, 2, or 3 so formula in H1 can be dragged down and across.
However, while it sort of works ok for the 5 8 5 example?

MrExcel July.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		C1	C2	C3			Number	8	5	5	Max Down
2		5	8	5			Rows Down	1	2	2	2
3		7	8	0		1	Columns	2	1	2
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
16
Sheet4

Cell Formulas
Range	Formula
H1:J3	H1	=Howfar($B$2:$D$15,COLUMNS($H:H),ROWS($1:1))
K2	K2	=MAX($H$2:$J$2)

It does not for the 4 9 0 as it returns column 1 for the first 0 rather than 2 for the second ????

MrExcel July.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		C1	C2	C3			Number	9	4	0	Max Down
2		4	9	0			Rows Down	1	2	4	4
3		2	2	9		1	Columns	3	1	1
4		4	7	2		2
5		6	9	6		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
16
Sheet4

Cell Formulas
Range	Formula
H1:J3	H1	=Howfar($B$2:$D$15,COLUMNS($H:H),ROWS($1:1))
K2	K2	=MAX($H$2:$J$2)

Time for a lie down before Nurse brings my medication....

 

[ststern45]

Thank you for the help.
I'll check this out in a couple minutes and let you know.
XLfart#1

 

[ststern45]

It does not for the 4 9 0 as it returns column 1 for the first 0 rather than 2 for the second ????
This is the tricky part that I could not figure out.
If the 1st 2 values such as the 4 and 9 are assigned a column number such as 3 and 1 and neither of the 3 digits appear on the same row then I guess you could use the "top down" approach. The remaining column number would be 2
So the result would be 3 1 2.

The 5 8 5 example is different because both digit 5's appear on the same row number. Your calculations are correct with the result 2 1 2.

 

[ststern45]

Looks like you figured it out.
If you enter 0 0 0 in cells B2 through D2 the final result could be either columns 3 3 1 or 3 3 2 since the 3rd 0 appears in row 6 in columns B & C (B6 & C6)
Thank you for all your help both of you.
Appreciate the update Function code.
If you would like to add any thoughts I would also be interested but it looks like you have it.
Sorry for the confusion. There are a number of what if's involved.
Thank you!!

 

[OaklandJim]

I know that you said that you already got a solution but I just got back to this. The biggest challenge for me was to ensure that the given value (column or row) does not repeat.

And, I am still unclear as to whether results should be column-by-column or row-by-row. As best I can there was one of each in your examples? My code does both.

So, FWIW, my workbook is HERE.

Beware that my code is usually way too klunky and wordy for more accomplished experts on the list. That is partly due to my somewhat limited expertise and partly because I try to write code that is understandable with less emphasis on efficiency and tightness.

For example I could not figure out how to sort the digits based on order-of-appearance without using a worksheet to do the sort.

Option Explicit
Option Base 1

' ----------------------------------------------------------------
' Procedure Name: GetUnsortedDigits
' Purpose: Load unsorted digits into the parameter array.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter prRangeWithDigits (Range): Range where unsorted digits are located in the source worksheet.
' Parameter prRangeToLookIn (Range): Range containing the data to look within for the digits.
' Parameter paiUnsorted (Long): The ByRef parameter array returned to caller.
' Return Type: Long)
' Author: Jim
' Date: 6/30/2023
' ----------------------------------------------------------------

Function GetUnsortedDigits( _
    ByVal prRangeWithDigits As Range, _
    ByVal prRangeToLookIn As Range, _
    ByRef paiUnsorted() As Long)

    Dim iDigitLoop As Long
   
    Dim iDigitsCount As Long
   
    Dim iDigitCurrent As Long
   
 '  Count of columns in the data to process.
    iDigitsCount = prRangeWithDigits.Columns.Count

'   Do sizing for the array containing raw data directly from the "look in" data (range)
    ReDim paiUnsorted(1 To 2, 1 To iDigitsCount)

    For iDigitLoop = 1 To iDigitsCount

        iDigitCurrent = prRangeWithDigits.Cells(1, iDigitLoop).Value
              
        paiUnsorted(1, iDigitLoop) = iDigitCurrent

        paiUnsorted(2, iDigitLoop) _
            = FindValueNthOccurrence(iDigitCurrent, prRangeToLookIn, 1)

    Next

End Function

Option Explicit

' ----------------------------------------------------------------
' Procedure Name: FindValueNthOccurrence
' Purpose: Find the Nth occurrence of a value in the RangeToLookIn range.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter piValueToFind (Long): The value to look for.
' Parameter prRangeToLookIn (Range): The range to look in.
' Parameter piRowFound (Long): Optional ByRef row where the value was found.
' Parameter piColFound (Long): Optional ByRef column where the value was found.
' Parameter pbShowMessage (Long): Optional Whether to show message with found row and column.
' Return Type: Long
' Author: Jim
' Date: 6/30/2023
' ----------------------------------------------------------------

'Goes row - by -row.

Function FindValueNthOccurrence( _
    ByVal piValueToFind As Long, _
    ByVal prRangeToLookIn As Range, _
    ByVal piNth As Long, _
    Optional ByRef piRowFound As Long = 0, _
    Optional ByRef piColFound As Long = 0, _
    Optional ByVal pbShowMessage As Boolean = False) _
As Long

    Dim rCell As Range
   
    Dim iOccurence As Long
   
    Dim iCellsCount As Long
   
    FindValueNthOccurrence = 0
   
    piRowFound = 1
   
    piColFound = 0
   
    For Each rCell In prRangeToLookIn
   
        iCellsCount = iCellsCount + 1
         
'       Get current column number
        piColFound = piColFound + 1
       
'       If next column # = 4 then start over for columns
        If piColFound = 4 _
         Then
            piColFound = 1
            piRowFound = piRowFound + 1
        End If
       
'       If the value was found...
        If rCell.Value = piValueToFind _
         Then
        
'           Determine if it is the specified number in order.
            iOccurence = iOccurence + 1
            If iOccurence = piNth _
             Then
            
'               Return the nth occurrence.
                FindValueNthOccurrence = iCellsCount
                Exit For
            End If
        End If
       
    Next
   
    If pbShowMessage _
     Then MsgBox "Row found = " & piRowFound & ", " & "Col found = " & piColFound

End Function

Option Explicit
Option Base 1

' ----------------------------------------------------------------
' Procedure Name: GetDigitsOrderColumnar
' Purpose: Get digits' columns order-of-appearance with no duplicates.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jim
' Date: 7/2/2023
' ----------------------------------------------------------------

Sub GetDigitsOrderColumnar()

'   -------------------------------
'            Declarations
'   -------------------------------

'   The worksheet with the data to process.
    Dim wsSource As Worksheet

'   Worksheet created to sort the data in the array.
    Dim wsSort As Worksheet
   
'   The three cell range holding digits to process.
    Dim rRangeWithDigits As Range

'   THe range to look within for the digit.
    Dim rRangeToLookIn As Range
   
'   Range where raw data is placed then sorted.
    Dim rTargetRange As Range
   
'   The "keys" range for sorting the raw data.
    Dim rTargetKeys As Range
   
'   Cell containing reordered digits.
    Dim rReorderedCell As Range
   
'   Cell containing result.
    Dim rResultCell As Range
   
'   Count of digits
    Dim iDigitsCount As Long
   
'   Used to iterate through "records" (rows) in the data.
    Dim iDigitNum As Long

'   The array holding sorted digits
    Dim aiRaw() As Long
   
 '  The array holding results -- based on data in aiRaw. Three elements, one
 '  for each digit.
    Dim aiResults(1 To 3) As Long
   
'   Vars to hold digit 1 through digit 3.
    Dim iDigit1 As Long
    Dim iDigit2 As Long
    Dim iDigit3 As Long

'   Vars to hold digits' column #.
    Dim iDigit1Col As Long
    Dim iDigit2Col As Long
    Dim iDigit3Col As Long

'   -------------------------------
'          Initializations
'   -------------------------------

'   The data source worksheet is the active sheet (user used button to call).
    Set wsSource = ActiveSheet
   
'   The range where the three digits is found in the source worksheet.
    Set rRangeWithDigits = wsSource.Range("B2:D2")
       
    Set rReorderedCell = rRangeWithDigits.Cells(1).Offset(0, 3)
   
    Set rResultCell = rRangeWithDigits.Cells(1).Offset(0, 4)
   
'   The range where data to search within is found in the source worksheet.
    Set rRangeToLookIn = wsSource.Range("B3:D15")
   
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
'   ---------------------------------------------
'         Sort Digits by Order-of-appearance
'   ---------------------------------------------
   
'   Load digits and order-of-appearance data into the aiRaw array.
    Call GetUnsortedDigits(rRangeWithDigits, rRangeToLookIn, aiRaw)
   
'   Delete then recreate the sort worksheet.
    On Error Resume Next
    Worksheets("Sort").Delete
    On Error GoTo 0
   
'   Add them move sorting worksheet to the end of worksheets.
    Set wsSort = Worksheets.Add
    wsSort.Name = "Sort"
   
    wsSort.Move After:=Worksheets(Worksheets.Count)
   
'   Set the target range for the unsorted data
    Set rTargetRange = wsSort.Range("B2").Resize(UBound(aiRaw, 2), UBound(aiRaw, 1))
   
'   Put the unsorted data into the range where sorting will occur.
'   Put column headers into the data sort range.
    With rTargetRange
        .Value = Application.Transpose(aiRaw)
        .Cells(1).Offset(-1, 0).Value = "Digit"
        .Cells(1).Offset(-1, 1).Value = "Occurrence"
    End With
   
'   Reset target range to include column headers.
    Set rTargetRange = rTargetRange.Offset(-1).Resize(4, 2)
   
'   Set range that points to the three cells containing order-of-appearance.
    Set rTargetKeys = rTargetRange.Cells(1).Offset(1, 1).Resize(3)
   
'   Sort the unsorted data.
    With wsSort.Sort

        .SortFields.Clear
        .SortFields.Add2 Key:=rTargetKeys, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       
            .SetRange rTargetRange
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
    End With
   
'   The range where the three digits is found in the source worksheet.
    Set rRangeWithDigits = rTargetRange.Cells(1).Offset(1).Resize(3, 1)
   
'   Get sorted digits 1 through 3
    With rRangeWithDigits
        iDigit1 = .Cells(1)
        iDigit2 = .Cells(2)
        iDigit3 = .Cells(3)
    End With
   
'   ---------------------------
'         Get Column #s
'   ---------------------------

'   NOTE: Function FindValueNthOccurrence sets the column # for the respective
'   via fifth parameter which is a ByRef parameter.

'   Get 1st column where first digit is located.
    Call FindValueNthOccurrence(iDigit1, rRangeToLookIn, 1, , iDigit1Col)

'   Save the value for the first digit which is the 1st column where the digit was found.
    aiResults(1) = iDigit1Col
     
'   Get 1st column where second digit is located.
    Call FindValueNthOccurrence(iDigit2, rRangeToLookIn, 1, , iDigit2Col)
  
'   Get 1st column where third digit is located.
    Call FindValueNthOccurrence(iDigit3, rRangeToLookIn, 1, , iDigit3Col)
  
'   If digit 2 = digit 1...
    If iDigit2 = iDigit1 _
     Then
    
'       Save column # for digit 2 which is the same as the one for digit 1.
        aiResults(2) = iDigit2Col
       
'       If digit 3 = digit 1...
        If iDigit3Col = iDigit1 _
         Then
'           Get second column where third digit is located.
            Call FindValueNthOccurrence(iDigit3, rRangeToLookIn, 2, , iDigit3Col)
           
        End If

'       Save column # for digit 3 which is different than he one for digit 1.
        aiResults(3) = iDigit3Col
   
    Else
   
'       If column # for digit 2 = column # for digit 1...
        If iDigit2Col = iDigit1Col _
         Then
'           Get 2nd column where second digit is located.
            Call FindValueNthOccurrence(iDigit2, rRangeToLookIn, 2, , iDigit2Col)
        End If
   
'       Save unique column # for digit 2.
        aiResults(2) = iDigit2Col
       
'       If digit 3 = digit 2 then...
        If iDigit3 = iDigit2 _
         Then
'           Save column # for digit 3 which is same as the column # for digit 2.
            aiResults(3) = iDigit2Col
       
'       If digit 3 column # = digit 1 column # OR digit 3 column # = digit 2 column #
        ElseIf iDigit3Col = iDigit1Col Or iDigit3Col = iDigit2Col _
         Then
'           Get 2nd column where third digit is located.
            Call FindValueNthOccurrence(iDigit3, rRangeToLookIn, 2, , iDigit3Col)
           
'           If digit 3 column # = digit 1 column # OR digit 3 column # = digit 2 column #
            If iDigit3Col = iDigit1Col Or iDigit3Col = iDigit2Col _
             Then
'               Get 3rd column where third digit is located.
                Call FindValueNthOccurrence(iDigit3, rRangeToLookIn, 2, , iDigit3Col)
            End If
       
        End If
           
'       Save column # for digit 3.
        aiResults(3) = iDigit3Col
       
    End If
   
'   ---------------------------
'            Closeout
'   ---------------------------

'   Delete the sorting worksheet.
    Worksheets("Sort").Delete

'   Activate the sheet with data.
    wsSource.Activate
   
'   Put reorderd digits into source worksheet.
    rReorderedCell.Value = iDigit1 & " - " & iDigit2 & " - " & iDigit3

'   Put column number results into source worksheet.
    rResultCell.Value = aiResults(1) & " - " & aiResults(2) & " - " & aiResults(3)
   
End Sub

Option Explicit
Option Base 1

' ----------------------------------------------------------------
' Procedure Name: GetDigitsOrderRowise
' Purpose: Get digit rows' order-of-appearance with no duplicates.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jim
' Date: 7/2/2023
' ----------------------------------------------------------------

Sub GetDigitsOrderRowise()

'   -------------------------------
'            Declarations
'   -------------------------------

'   The worksheet with the data to process.
    Dim wsSource As Worksheet

'   Worksheet created to sort the data in the array.
    Dim wsSort As Worksheet
   
'   The three cell range holding digits to process.
    Dim rRangeWithDigits As Range

'   THe range to look within for the digit.
    Dim rRangeToLookIn As Range
   
'   Range where raw data is placed then sorted.
    Dim rTargetRange As Range
   
'   The "keys" range for sorting the raw data.
    Dim rTargetKeys As Range
   
'   Cell containing reordered digits.
    Dim rReorderedCell As Range
   
'   Cell containing result.
    Dim rResultCell As Range
   
'   Count of digits
    Dim iDigitsCount As Long
   
'   Used to iterate through "records" (rows) in the data.
    Dim iDigitNum As Long

'   The array holding sorted digits
    Dim aiRaw() As Long
   
 '  The array holding results -- based on data in aiRaw. Three elements, one
 '  for each digit.
    Dim aiResults(1 To 3) As Long
   
'   Vars to hold digit 1 through digit 3.
    Dim iDigit1 As Long
    Dim iDigit2 As Long
    Dim iDigit3 As Long

'   Vars to hold digits' row #.
    Dim iDigit1Row As Long
    Dim iDigit2Row As Long
    Dim iDigit3Row As Long

'   -------------------------------
'          Initializations
'   -------------------------------

'   The data source worksheet is the active sheet (user used button to call).
    Set wsSource = ActiveSheet
   
'   The range where the three digits is found in the source worksheet.
    Set rRangeWithDigits = wsSource.Range("B2:D2")
       
    Set rReorderedCell = rRangeWithDigits.Cells(1).Offset(0, 3)
   
    Set rResultCell = rRangeWithDigits.Cells(1).Offset(0, 4)
   
'   The range where data to search within is found in the source worksheet.
    Set rRangeToLookIn = wsSource.Range("B3:D15")
   
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
   
'   ---------------------------------------------
'         Sort Digits by Order-of-appearance
'   ---------------------------------------------
   
'   Load digits and order-of-appearance data into the aiRaw array.
    Call GetUnsortedDigits(rRangeWithDigits, rRangeToLookIn, aiRaw)
   
'   Delete then recreate the sort worksheet.
    On Error Resume Next
    Worksheets("Sort").Delete
    On Error GoTo 0
   
'   Add them move sorting worksheet to the end of worksheets.
    Set wsSort = Worksheets.Add
    wsSort.Name = "Sort"
   
    wsSort.Move After:=Worksheets(Worksheets.Count)
   
'   Set the target range for the unsorted data
    Set rTargetRange = wsSort.Range("B2").Resize(UBound(aiRaw, 2), UBound(aiRaw, 1))
   
'   Put the unsorted data into the range where sorting will occur.
'   Put column headers into the data sort range.
    With rTargetRange
        .Value = Application.Transpose(aiRaw)
        .Cells(1).Offset(-1, 0).Value = "Digit"
        .Cells(1).Offset(-1, 1).Value = "Occurrence"
    End With
   
'   Reset target range to include column headers.
    Set rTargetRange = rTargetRange.Offset(-1).Resize(4, 2)
   
'   Set range that points to the three cells containing order-of-appearance.
    Set rTargetKeys = rTargetRange.Cells(1).Offset(1, 1).Resize(3)
   
'   Sort the unsorted data.
    With wsSort.Sort

        .SortFields.Clear
        .SortFields.Add2 Key:=rTargetKeys, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       
            .SetRange rTargetRange
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
    End With
   
'   The range where the three digits is found in the source worksheet.
    Set rRangeWithDigits = rTargetRange.Cells(1).Offset(1).Resize(3, 1)
   
'   Get sorted digits 1 through 3
    With rRangeWithDigits
        iDigit1 = .Cells(1)
        iDigit2 = .Cells(2)
        iDigit3 = .Cells(3)
    End With
   
'   ---------------------------
'          Get Row #s
'   ---------------------------

'   NOTE: Function FindValueNthOccurrence sets the row # for the respective
'   via fourth parameter which is a ByRef parameter.

'   Get 1st row where first digit is located.
    Call FindValueNthOccurrence(iDigit1, rRangeToLookIn, 1, iDigit1Row)

'   Save the value for the first digit which is the 1st row where the digit was found.
    aiResults(1) = iDigit1Row
     
'   Get 1st row where second digit is located.
    Call FindValueNthOccurrence(iDigit2, rRangeToLookIn, 1, iDigit2Row)
  
'   Get 1st row where third digit is located.
    Call FindValueNthOccurrence(iDigit3, rRangeToLookIn, 1, iDigit3Row)
  
'   If digit 2 = digit 1...
    If iDigit2 = iDigit1 _
     Then
    
'       Save row # for digit 2 which is the same as the one for digit 1.
        aiResults(2) = iDigit2Row
       
'       If digit 3 = digit 1...
        If iDigit3Row = iDigit1 _
         Then
'           Get second row where third digit is located.
            Call FindValueNthOccurrence(iDigit3, rRangeToLookIn, 2, iDigit3Row)
           
        End If

'       Save row # for digit 3 which is different than he one for digit 1.
        aiResults(3) = iDigit3Row
   
    Else
   
'       If row # for digit 2 = row # for digit 1...
        If iDigit2Row = iDigit1Row _
         Then
'           Get 2nd row where second digit is located.
            Call FindValueNthOccurrence(iDigit2, rRangeToLookIn, 2, iDigit2Row)
        End If
   
'       Save unique row # for digit 2.
        aiResults(2) = iDigit2Row
       
'       If digit 3 = digit 2 then...
        If iDigit3 = iDigit2 _
         Then
'           Save row # for digit 3 which is same as the row # for digit 2.
            aiResults(3) = iDigit2Row
       
'       If digit 3 row # = digit 1 row # OR digit 3 row # = digit 2 row #
        ElseIf iDigit3Row = iDigit1Row Or iDigit3Row = iDigit2Row _
         Then
'           Get 2nd row where third digit is located.
            Call FindValueNthOccurrence(iDigit3, rRangeToLookIn, 2, iDigit3Row)
           
'           If digit 3 row # = digit 1 row # OR digit 3 row # = digit 2 row #
            If iDigit3Row = iDigit1Row Or iDigit3Row = iDigit2Row _
             Then
'               Get 3rd row where third digit is located.
                Call FindValueNthOccurrence(iDigit3, rRangeToLookIn, 2, iDigit3Row)
            End If
       
        End If
           
'       Save row # for digit 3.
        aiResults(3) = iDigit3Row
       
    End If
   
'   ---------------------------
'            Closeout
'   ---------------------------

'   Delete the sorting worksheet.
    Worksheets("Sort").Delete

'   Activate the sheet with data.
    wsSource.Activate
   
'   Put reorderd digits into source worksheet.
    rReorderedCell.Value = iDigit1 & " - " & iDigit2 & " - " & iDigit3

'   Put row number results into source worksheet.
    rResultCell.Value = aiResults(1) & " - " & aiResults(2) & " - " & aiResults(3)
   
End Sub

 

[ststern45]

Thank you Jim for all your help.
Appreciate the time you put into this posting.
I'll use your code and worksheet from the link you provided.
I really do appreciate everyone's help with this.
Thank you!! Thank you!!