Get list of row #s with certain value

M

Mitch Powell

I have a column in which rows will contain the value of "1" when that row
meets a certain criteria. I want to build an ascending list in another
location of the row numbers in that column that contain "1". Any ideas?
 
M

Max

Assume that col is col B, which would contain only 1's or blanks in B2 down

In C2:
=IF(B2=1,ROW(),"")
Leave C1 blank

In D2:
=IF(ROWS($1:1)>COUNT(C:C),"",SMALL(C:C,ROWS($1:1)))

Select C2:D2, copy down to cover the max expected extent in col B. Hide away
col C. Col D returns what you seek, all neatly bunched at the top. Adapt to
suit.
 
M

Mitch Powell

Outstanding! Thank you very much.

Max said:
Assume that col is col B, which would contain only 1's or blanks in B2 down

In C2:
=IF(B2=1,ROW(),"")
Leave C1 blank

In D2:
=IF(ROWS($1:1)>COUNT(C:C),"",SMALL(C:C,ROWS($1:1)))

Select C2:D2, copy down to cover the max expected extent in col B. Hide away
col C. Col D returns what you seek, all neatly bunched at the top. Adapt to
suit.
Click to expand...
 
B

Bernd P

Hello,

Another approach:
Enter into E2:
=MATCH(1,B2:$B$65536,)+1
Enter into E3:
=MATCH(1,INDEX(B:B,E2+1):$B$65536,)+E2
Copy down until you see error values...

Calculation times for 2000 rows with 115 "one"s: 0.65 millisecs
[Max': 48.77 millisecs where column D took 45.48ms]

Regards,
Bernd
 
T

Teethless mama

=SMALL(IF(data=1,ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1))

ctrl+shift+enter, not just enter
copy down
 
B

Bernd P

Hello Teethless Mama,

Your calculation time for 2000 rows with 115 "one"s: 100.03 millisecs
[FastExcel]

Regards,
Bernd
 
M

Max

Interesting timings there, Bernd ! Thanks.

Wonder how these comparative timings would look like if we were to say cover
20000 rows instead, and with error trapping* included for your/TM's
suggestions? *see below.
Copy down until you see error values...
Click to expand...
Ahh, but my suggestion also eliminates all those nasty error values, giving
neat outputs for the OP <g>

Bernd P said:
Hello,

Another approach:
Enter into E2:
=MATCH(1,B2:$B$65536,)+1
Enter into E3:
=MATCH(1,INDEX(B:B,E2+1):$B$65536,)+E2
Copy down until you see error values...

Calculation times for 2000 rows with 115 "one"s: 0.65 millisecs
[Max': 48.77 millisecs where column D took 45.48ms]

Regards,
Bernd
Click to expand...
 
T

T. Valko

Wonder how these comparative timings would look like if we were to say
cover 20000 rows instead, and with error trapping* included for your/TM's
suggestions?
Click to expand...

Try it. There's calc timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

--
Biff
Microsoft Excel MVP


Max said:
Interesting timings there, Bernd ! Thanks.

Wonder how these comparative timings would look like if we were to say
cover 20000 rows instead, and with error trapping* included for your/TM's
suggestions? *see below.
Copy down until you see error values...
Click to expand...
Ahh, but my suggestion also eliminates all those nasty error values,
giving neat outputs for the OP <g>

Bernd P said:
Hello,

Another approach:
Enter into E2:
=MATCH(1,B2:$B$65536,)+1
Enter into E3:
=MATCH(1,INDEX(B:B,E2+1):$B$65536,)+E2
Copy down until you see error values...

Calculation times for 2000 rows with 115 "one"s: 0.65 millisecs
[Max': 48.77 millisecs where column D took 45.48ms]

Regards,
Bernd
Click to expand...
Click to expand...
 
B

Bernd P

Hello Max,

For 20,000 rows with about 1,600 "one"s I got [FastExcel]:

You: about 6,000 msec
Teethless Mama without cleansing: 10,000 msec
Myself with cleansing: about 15 msec (almost no diff to the original
without cleansing)

Regards,
Bernd
 
B

Bernd P

Hi Biff,

Good reference, thanks.

And it is exactly that guy who offers FastExcel.

IMHO anyone who uses Excel seriously should have it and use it. Who
would drive a car without a speedometer?

Regards,
Bernd
 
Top