Results 1 to 10 of 10

Thread: Formula Help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2005
    Posts
    752
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Formula Help

    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 by ststern45; Jul 21st, 2019 at 12:19 AM.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,968
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Formula Help

    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?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Sep 2005
    Posts
    752
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help

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

  4. #4
    Board Regular
    Join Date
    Sep 2005
    Posts
    752
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help

    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 by ststern45; Jul 21st, 2019 at 08:16 AM.

  5. #5
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help

    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 by theBardd; Jul 21st, 2019 at 08:21 AM.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,968
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Formula Help

    Quote Originally Posted by ststern45 View Post
    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.

    Count Back

    ABCDEF
    1222579
    2970
    3813
    4143
    5735
    6929
    7668
    8207
    9148
    10772
    11

    Spreadsheet Formulas
    CellFormula
    D1=HowFarBack($A1:$C70,COLUMNS($D:D))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,968
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Formula Help

    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.

    Count Back

    ABCDEF
    1222579
    2970
    3813
    4143
    5735
    6929
    7668
    8207
    9148
    10772
    11

    Spreadsheet Formulas
    CellFormula
    D1=ROUNDUP(FIND("#",SUBSTITUTE(CONCAT($A2:$C70),A1,"#",COUNTIF($A1:A1,A1)))/3,0)


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula Help

    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.

    Excel 2010
    ABCDEF
    122279X
    2970799
    3813
    4143
    5735
    6999
    7668
    8207
    9148
    10772

    Sheet1



    Worksheet Formulas
    CellFormula
    D1=HowFar3($A1:$C70,COLUMNS($D:D))
    D2=HowFarBack($A$1:$C$70,COLUMNS($D:D))



    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.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  9. #9
    Board Regular
    Join Date
    Sep 2005
    Posts
    752
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help

    Pete,
    Great work as always!!
    Formulas and code work super.

    Thank you so much!!

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,968
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Formula Help

    Quote Originally Posted by ststern45 View Post
    Pete,
    Great work as always!!
    Formulas and code work super.

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


    Quote Originally Posted by Snakehips View Post
    .. 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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •