Get list of row #s with certain value
 

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?

Get list of row #s with certain value
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mitch Powell" wrote:
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?

Get list of row #s with certain value
 

Outstanding! Thank you very much.

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mitch Powell" wrote:
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?

Get list of row #s with certain value
 

welcome, Mitch.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mitch Powell" wrote in message
...
Outstanding! Thank you very much.

Get list of row #s with certain value
 

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

Get list of row #s with certain value
 

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

ctrl+shift+enter, not just enter
copy down


"Mitch Powell" wrote:

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?

Get list of row #s with certain value
 

Hello Teethless Mama,

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

Regards,
Bernd

Get list of row #s with certain value
 

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...
Ahh, but my suggestion also eliminates all those nasty error values, giving
neat outputs for the OP <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bernd P" wrote in message
oups.com...
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

Get list of row #s with certain value
 

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?

Try it. There's calc timer code he

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

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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...
Ahh, but my suggestion also eliminates all those nasty error values,
giving neat outputs for the OP <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bernd P" wrote in message
oups.com...
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

Get list of row #s with certain value
 

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

Get list of row #s with certain value
 

Thanks for the link, Biff.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Get list of row #s with certain value
 

Bernd,
Thanks for the test and results !
You win hands down <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Get list of row #s with certain value
 

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