Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Comparison of Columns

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default Comparison of Columns

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 153
Default Comparison of Columns

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   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Comparison of Columns

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparison TiffQ23 Excel Discussion (Misc queries) 1 February 18th 09 09:24 PM
Comparing two columns , and giving comparison results in a third. Colin Hayes Excel Worksheet Functions 6 January 3rd 09 01:43 AM
row comparison Jim Excel Worksheet Functions 4 August 21st 07 10:20 PM
Matching Data Columns for comparison John Excel Discussion (Misc queries) 1 January 8th 07 07:24 AM
XLS comparison Pick Excel Discussion (Misc queries) 1 May 26th 06 06:10 PM


All times are GMT +1. The time now is 04:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"