Get list of row #s with certain value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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...
 
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
 
=SMALL(IF(data=1,ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1))

ctrl+shift+enter, not just enter
copy down
 
Hello Teethless Mama,

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

Regards,
Bernd
 
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...
 
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...
 
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
 
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
 
Back
Top