Welcome Guest
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
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...

 10 14 19 2 5 32 2 5 19 14 32 10

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

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.

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

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

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.

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

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