Formula Help

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
778
Office Version
365, 2010
Platform
Windows
Hi everyone,

I would like to find a formula that will calculate how may cells back when a digit appeared.

For example,
A1 = 2
B1 = 2
C1 = 2

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

Step1: Count how many cells back until the 1st digit 2. In this example the 1st 2 is 5 cells back (9-2-9)
Step2: Count how many cells back until the 2nd digit 2. In this example the 2nd 2 is 7 cells back (2-0-7)
Step3: Count how many cells back until the 3rd digit 2. In this example the 3rd 2 is 9 cells back (7-7-2)

So cell
D1 = 5 cells back
E1 = 7 cells back
F1 = 9 cells back

Thanks in advance!!
 
Last edited:

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,245
Office Version
365
Platform
Windows
1. Could you give a couple more examples of sample data and expected results?

2. Is the 7-7-2 in your sample above all in one cell or does that mean A10=7, B10=7, C10=2?
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
778
Office Version
365, 2010
Platform
Windows
Hi Peter,

7-7-2 is at the bottom of the list.
Your correct A10 = 7, B10=7, and C10=2

Example 1

A1=9
B1=1
C1=5

9-1-5
4-0-4
2-2-9
5-0-8
4-5-4
4-4-0
5-1-4

D1= 2 (5-0-8)
E1= 6 (5-1-4)
F1= 6 (5-1-4)

Example 2:

A1=7
B1=8
C1=8

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

C1=4 (7-4-6) The 1st digit 7
D1=1 (3-8-2)
E1=8 (7-8-7) The 2nd digit 7

If you need additional examples let me know.
Thanks!!
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
778
Office Version
365, 2010
Platform
Windows
Hi Peter,

Calculating repeat digits from the original set such as 3 exact digits 3-3-3 or 2 digits that are the same such as 7-8-8 (see example below) are tricky. My calculations which are incorrect on my spreadsheet always pick up the same "repeat" digit. So in essence when calculating the 2nd repeat digit we need to skip the 1st calculation and find the 2nd. Same as when a set has 3 exact digits such as 3-3-3, I need to find the 1st, then the second by skipping the 1st calculation, then the 3rd by skipping the 1st and 2nd calculations. I have a function that will actually calculate the final value correctly but I need to calculate each position 1, 2, and 3.

=howfar3($A1:$C70,3)
Ctrl-Shft-Enter

Public Function HowFar3(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
HowFar3 = i - 1
Exit Function
End If
Exit For
End If
Next k
Next j
Next i

HowFar = ""
End Function

7-7-2 is at the bottom of the list.
Your correct A10 = 7, B10=7, and C10=2

Example 1

A1=9
B1=1
C1=5

9-1-5
4-0-4
2-2-9
5-0-8
4-5-4
4-4-0
5-1-4

D1= 2 (5-0-8)
E1= 6 (5-1-4)
F1= 6 (5-1-4)

Example 2:

A1=7
B1=8
C1=8

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

C1=4 (7-4-6) The 1st digit 7
D1=1 (3-8-2)
E1=8 (7-8-7) The 2nd digit 7

If you need additional examples let me know.
Thanks!!
 
Last edited:

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
I think I get the general principle, but some of your examples confuse me.

I am sure this can be improved upon, but s a start does this give the correct results?

Code:
=IFERROR(IFERROR(MATCH(A1,$A$2:$A$1000,0),MATCH(A1,$B$2:$B$1000,0)),MATCH(A1,$C$2:$C$1000,0))
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,245
Office Version
365
Platform
Windows
Example 1

A1=9
B1=1
C1=5

9-1-5
4-0-4
2-2-9
5-0-8
4-5-4
4-4-0
5-1-4

D1= 2 (5-0-8)
E1= 6 (5-1-4)
F1= 6 (5-1-4)
I am assuming this example is a mistake and that F1 should be 3 since there is a 5 in row 4 (5-0-8)

If that is the case then you could try this UDF

Code:
Function HowFarBack(MyRange As Range, myCol As Long) As Variant
  Dim MyData As Variant
  Dim i As Long, j As Long, k As Long, c As Long
  
  MyData = MyRange.Value
  For i = 1 To myCol
    If MyData(1, i) = MyData(1, myCol) Then k = k + 1
  Next i
  HowFarBack = ""
  For i = 2 To UBound(MyData)
    For j = 1 To 3
      If MyData(i, j) = MyData(1, myCol) Then
        HowFarBack = i - 1
        c = c + 1
        If c = k Then Exit Function
      End If
    Next j
  Next i
Used in the sheet like this, copied across.

Excel Workbook
ABCDEF
1222579
2970
3813
4143
5735
6929
7668
8207
9148
10772
11
Count Back
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,245
Office Version
365
Platform
Windows
Actually, if all your values are single digits per your examples, then you could use a formula using standard worksheet functions like this, copied across.

Excel Workbook
ABCDEF
1222579
2970
3813
4143
5735
6929
7668
8207
9148
10772
11
Count Back
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,057
Office Version
2013
Platform
Windows
I was about to post my UDF solution this morning, only to find that Peter had pipped me to it. So I didn't bother.
A quick test appeared to show both giving a similar result other than Peter's returned blank, "", if a search number did not exist whereas mine returns "X"

Subsequently, I have noticed that there is a condition, such as there only being two 2's in the sample data, where mine returns "X" as in no additional match and Peters's returns 9 for column 3, which is a duplicate of the return for column 2.

Hence I post my solution in case it is of value.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style="text-align: center;background-color: #E6B8B7;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">1</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">9</td><td style="text-align: center;;">9</td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;;">7</td><td style="text-align: center;;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=HowFar3(<font color="Blue">$A1:$C70,COLUMNS(<font color="Red">$D:D</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=HowFarBack(<font color="Blue">$A$1:$C$70,COLUMNS(<font color="Red">$D:D</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Code:
Public Function HowFar3(ByVal MyRange As Range, ByVal MyCol As Long)

Dim MyData As Variant, i As Long, j As Long, k As Long
Dim GotOne As Integer
MyData = MyRange.Value
    For k = 1 To 3  'for ABC columns loop of row 1
    GotOne = 0
        For i = 2 To UBound(MyData) 'rows 2 to last row
                    For j = 1 To 3  ' for ABC loop of rows 2 to last
                        If MyData(i, j) = MyData(1, k) Then  ' if match
                            'MsgBox MyData(i, j)
                            MyData(1, k) = i - 1
                             MyData(i, j) = "x"  ' to exclude it from other comparisons
                             GotOne = 1
                            Exit For
                         End If
                    Next j
        If GotOne = 1 Then Exit For
        Next i
        If GotOne = 0 Then MyData(1, k) = "X"
    Next k
HowFar3 = MyData(1, MyCol)
End Function
I don't have CONCAT function so have not tested Peter's formula solution.

Hope that helps.
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
778
Office Version
365, 2010
Platform
Windows
Pete,
Great work as always!!
Formulas and code work super.

Thank you so much!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,245
Office Version
365
Platform
Windows
Pete,
Great work as always!!
Formulas and code work super.

Thank you so much!!
You're welcome. Glad to help. :)


.. if a search number did not exist ..
Based on the OP's samples I made the assumption that such a circumstance would not occur. If it can, I would certainly want to modify the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,619
Messages
5,487,895
Members
407,614
Latest member
fuslela

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top