Welcome Guest
Log In | Register )
You last visited December 9, 2016, 8:10 pm
All times shown are
Eastern Time (GMT-5:00)

Excel Question

Topic closed. 6 replies. Last post 5 years ago by phileight.

Page 1 of 1
PrintE-mailLink
Avatar
FL
United States
Member #93841
July 8, 2010
576 Posts
Offline
Posted: June 29, 2011, 1:14 am - IP Logged

Hey,

 

I wanted to know what formula can be used to count the difference of cells between two numbers repeated numbers in different rows?

 

For example...

1014192532
2519143210

I would like to know the how far apart each number from the top is from it's duplicate. If you count from left to right  starting with the top number. The 10 is 5 columns to the right apart from the bottom 10 so my result is 5

Then basically do the same thing for every number, but you have to start the count from the number on top and whichever number you use you go from left to right staring at that number.

14 is 2 columns apart to the right

19 is 0 columns apart

2 is 3 columns apart>>>the two is two columns to the right apart and then one column at the beginning.

5 is 3 columns apart>>>one columns to the right apart and then two column at the beginning.

32 is 5 columns apart>> there is no more space to the right so you start the count from the beginning

 

This was the best way I can explain it....can this be made in to a formula?

"It's evolve or die, really, you have to evolve, you have to move on otherwise it just becomes stagnant."

    Avatar

    United States
    Member #41846
    June 23, 2006
    460 Posts
    Offline
    Posted: June 29, 2011, 7:05 pm - IP Logged

    there is too much decisiom making for a formula. A small macro(VBA program) could handle this problem. some folks here won't consider a macro, let me know if interested.

      Avatar

      United States
      Member #41846
      June 23, 2006
      460 Posts
      Offline
      Posted: June 29, 2011, 8:52 pm - IP Logged

      this code will do what you asked

       

      Sub compare()
      ' find matching # count seperation left to right
      ' always 6 #'s to check

      For z = 1 To 6 ' start of loop to pick up reference #'s
      a = Cells(1, z) ' the 1 picks row 1 z is which of the 6 #'s I'm looking for
      For y = 1 To 6 ' start of 2nd loop
      b = Cells(2, y) 'b has 1 of the 6 numbers from row 2. time to start checking
      If b = a Then GoSub decide ' have found both #'s now what to do with them
      Next y ' complete 2nd loop
      Next z ' complete 1st loop and done

      Exit Sub ' use this to end program when you have subroutines

      decide: 'several choices to make for me easier to do this way
      If z = 6 Then c = y: GoTo done ' if this condition exit subroutine
      If z > y Then c = 6 - z + y: GoTo done
      If z < y Then c = y - z: GoTo done
      If z = y Then c = 0

      done:
      Cells(4, z) = c ' put result in row 4 same column as reference #
      Return ' completes the gosub
      End Sub

        Avatar
        FL
        United States
        Member #93841
        July 8, 2010
        576 Posts
        Offline
        Posted: June 30, 2011, 9:24 am - IP Logged

        this code will do what you asked

         

        Sub compare()
        ' find matching # count seperation left to right
        ' always 6 #'s to check

        For z = 1 To 6 ' start of loop to pick up reference #'s
        a = Cells(1, z) ' the 1 picks row 1 z is which of the 6 #'s I'm looking for
        For y = 1 To 6 ' start of 2nd loop
        b = Cells(2, y) 'b has 1 of the 6 numbers from row 2. time to start checking
        If b = a Then GoSub decide ' have found both #'s now what to do with them
        Next y ' complete 2nd loop
        Next z ' complete 1st loop and done

        Exit Sub ' use this to end program when you have subroutines

        decide: 'several choices to make for me easier to do this way
        If z = 6 Then c = y: GoTo done ' if this condition exit subroutine
        If z > y Then c = 6 - z + y: GoTo done
        If z < y Then c = y - z: GoTo done
        If z = y Then c = 0

        done:
        Cells(4, z) = c ' put result in row 4 same column as reference #
        Return ' completes the gosub
        End Sub

        Thanks, I will give it a try later on tonight.

        "It's evolve or die, really, you have to evolve, you have to move on otherwise it just becomes stagnant."

          Avatar
          Pennsylvania
          United States
          Member #109275
          April 9, 2011
          1495 Posts
          Offline
          Posted: July 13, 2011, 8:20 pm - IP Logged

          there is too much decisiom making for a formula. A small macro(VBA program) could handle this problem. some folks here won't consider a macro, let me know if interested.

          Good code. I know what you mean about macros. I wanted to write VBA code for someone to do conditional formatting and they didnt want any part of it. I said that I didnt think it could be done without, but they didnt want macros. All they had to do was copy/paste amongst workbooks they wanted to use it in.

            Avatar
            FL
            United States
            Member #93841
            July 8, 2010
            576 Posts
            Offline
            Posted: July 14, 2011, 12:04 am - IP Logged

            The formula works... Thank you very much Phileight!!!

            "It's evolve or die, really, you have to evolve, you have to move on otherwise it just becomes stagnant."

              Avatar

              United States
              Member #41846
              June 23, 2006
              460 Posts
              Offline
              Posted: July 14, 2011, 9:00 pm - IP Logged

              Good code. I know what you mean about macros. I wanted to write VBA code for someone to do conditional formatting and they didnt want any part of it. I said that I didnt think it could be done without, but they didnt want macros. All they had to do was copy/paste amongst workbooks they wanted to use it in.

              for a long time i hung onto win 98 and gwbasic because VBA intimidated me.  Conditional formating was the straw that made me start using VBA. I credit Gasmeterguy's blog with the information on basic coding that let me get started. once I got started I realized that around 80% of the code was what I was already doing in GWbasic.

              like most things the first few steps are the hardest

              teamprofit  your welcome