Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Compare rows

I column A to J, row 1 and two I have numbers between 1 and 9.

It might look like this

1 1 1 2 3 1 1 1 1 1
1 1 1 1 8 2 1 1 1 1

What I need is a function that compares the two rows, like this:

If J1 and J2 contains the same number, it must be counted.
Next it should test i I1 and I2 contaisn the same number. I n that case it
should be addes to the counter.
The code should go on like this until it meets to numbers, thatr are not the
same. Then it should stop counting and return the resulat of the counter.

In other words it shold compare the numbers in row 1 and 2, going backwatrds
from column J to column A. The first time two numbers are not the same, it
should stop, and return the number counted.

Maby the function could be entered like =COUNTALIKE(A1:J2) and the above
caee the result should be 4 as there are four numbers, that are the same,
before the that are not.

Jan







  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Compare rows

Thank you both, I choose to use the formula solutuion,

Jan

Rick Rothstein wrote:
This array-entered** formula should do what you want...

=10-MAX(IF(A1:J1<A2:J2,COLUMN(A1:J1),0))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself.

Note... the value of 10 represents the column number of the last
column in the range (Column J is the 10th column).


"Jan Kronsell" wrote in message
...
I column A to J, row 1 and two I have numbers between 1 and 9.

It might look like this

1 1 1 2 3 1 1 1 1 1
1 1 1 1 8 2 1 1 1 1

What I need is a function that compares the two rows, like this:

If J1 and J2 contains the same number, it must be counted.
Next it should test i I1 and I2 contaisn the same number. I n that
case it should be addes to the counter.
The code should go on like this until it meets to numbers, thatr are
not the same. Then it should stop counting and return the resulat of
the counter.

In other words it shold compare the numbers in row 1 and 2, going
backwatrds from column J to column A. The first time two numbers are
not the same, it should stop, and return the number counted.

Maby the function could be entered like =COUNTALIKE(A1:J2) and the
above caee the result should be 4 as there are four numbers, that
are the same, before the that are not.

Jan



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
Compare rows Rick Rothstein Excel Programming 0 January 15th 09 06:59 PM
compare rows Papa Jonah Excel Programming 0 August 6th 08 06:13 PM
Compare Rows [email protected] Excel Programming 1 April 30th 08 08:48 PM
Compare rows? sandernoteborn Excel Worksheet Functions 6 May 8th 06 05:47 PM
Compare rows to col greu Excel Programming 5 November 10th 05 10:00 AM


All times are GMT +1. The time now is 08:36 AM.

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"