Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have three columns thast represent Set numbers, I want to compare the
values in column "B" with those in column "A" find where they match and then calculate how many rows they have moved either up or down and put the answer in column "C" Col A Col B Col C 1127 1129 +1 1129 1130 +1 1130 1132 +3 1102 1102 0 1131 1131 0 1132 1133 +1 1133 1135 +1 1135 1137 +2 1062 1138 +2 1137 1062 -1 1138 1141 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You need to provide much more detail. For example, what exactly do you
mean by "where they match"? Where what matches what? Also, you need to explain how the value in column C is to be calculated. For example, in the first row of you example data you have 1127 and 1129 and a result of +1. Why is this 1 when the difference between 1127 and 1129 is 2, not 1, especially in light of the fact that in the second row you have 1129 and 1130 also yielding a result of +1. Why does the first row get a result of 1, not 2? You need to explain in detail what it is that you want to compare and the meaning of the results. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 21 Jan 2010 06:23:01 -0800, Rob wrote: I have three columns thast represent Set numbers, I want to compare the values in column "B" with those in column "A" find where they match and then calculate how many rows they have moved either up or down and put the answer in column "C" Col A Col B Col C 1127 1129 +1 1129 1130 +1 1130 1132 +3 1102 1102 0 1131 1131 0 1132 1133 +1 1133 1135 +1 1135 1137 +2 1062 1138 +2 1137 1062 -1 1138 1141 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Jan 21, 6:23*am, Rob wrote:
I have three columns thast represent Set numbers, I want to compare the values in column "B" with those in column "A" find where they match and then calculate how many rows they have moved either up or down and put the answer in column "C" Col A * * * * Col B * * *Col C 1127 * * * * 1129 * * * * +1 1129 * * * * 1130 * * * * +1 1130 * *1132 * *+3 1102 * *1102 * *0 1131 * *1131 * *0 1132 * *1133 * *+1 1133 * *1135 * *+1 1135 * *1137 * *+2 1062 * *1138 * *+2 1137 * *1062 * *-1 1138 * *1141 * * Here's one way with Excel 2003. First, in C1 put =IF(COUNTIF(A:A,B1)=0,"",MATCH(B1,A:A,0)-ROW()) and copy downward. Then select column C and use Format Cells Number and for "Category" choose "Custom" and for "Type" put +0;-0;0 Hope this helps getting started. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thank you both for your time
"zvkmpw" wrote: On Jan 21, 6:23 am, Rob wrote: I have three columns thast represent Set numbers, I want to compare the values in column "B" with those in column "A" find where they match and then calculate how many rows they have moved either up or down and put the answer in column "C" Col A Col B Col C 1127 1129 +1 1129 1130 +1 1130 1132 +3 1102 1102 0 1131 1131 0 1132 1133 +1 1133 1135 +1 1135 1137 +2 1062 1138 +2 1137 1062 -1 1138 1141 Here's one way with Excel 2003. First, in C1 put =IF(COUNTIF(A:A,B1)=0,"",MATCH(B1,A:A,0)-ROW()) and copy downward. Then select column C and use Format Cells Number and for "Category" choose "Custom" and for "Type" put +0;-0;0 Hope this helps getting started. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Comparison | Excel Discussion (Misc queries) | |||
Comparing two columns , and giving comparison results in a third. | Excel Worksheet Functions | |||
row comparison | Excel Worksheet Functions | |||
Matching Data Columns for comparison | Excel Discussion (Misc queries) | |||
XLS comparison | Excel Discussion (Misc queries) |