B
browniebodrum
How can I get Exel to display every (eg) 35th row only?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bernie Deitrick
Brownie,
Insert a new column A. The put the number 35 into cell A1, then in A2, use the formula
=MOD(ROW()-2,$A$1)
and copy down to the bottom of your used range.
Then filter on column A, to show values of 0.
Change the value in cell A1 to show different row counts (i.e., 10 to show every tenth row).
HTH,
Bernie
MS Excel MVP
Insert a new column A. The put the number 35 into cell A1, then in A2, use the formula
=MOD(ROW()-2,$A$1)
and copy down to the bottom of your used range.
Then filter on column A, to show values of 0.
Change the value in cell A1 to show different row counts (i.e., 10 to show every tenth row).
HTH,
Bernie
MS Excel MVP
B
browniebodrum
Great, Bernie, that worked fine, thanks very much.
BrownieBodrum
BrownieBodrum
B
browniebodrum
Can I please extend this line of enquiry? I might be going about it the
wrong way.
I've got a huge spreadsheet (a dump of a web page with booking forms data on
it) and I just want to pick out the 'organisation' field, to make sure I
haven't omitted anyone's booking. The 'organisation' field is in cell B2,
then every 33 rows further down, ie B35, B68 etc. Using your solution below,
(thanks!) I can now SEE just the organisations, but if I then want to use
just those cells in another spreadsheet, what's the best way to
select/identify them (eg so I can cut and paste them or sort them
alphabetically in another spreadsheet)?
wrong way.
I've got a huge spreadsheet (a dump of a web page with booking forms data on
it) and I just want to pick out the 'organisation' field, to make sure I
haven't omitted anyone's booking. The 'organisation' field is in cell B2,
then every 33 rows further down, ie B35, B68 etc. Using your solution below,
(thanks!) I can now SEE just the organisations, but if I then want to use
just those cells in another spreadsheet, what's the best way to
select/identify them (eg so I can cut and paste them or sort them
alphabetically in another spreadsheet)?
B
Bernie Deitrick
Simply select the entire range and press Ctrl-C to copy them. When you paste them elsewhere, Excel
will remove the rows that were hidden by the filter, and you will get your compacted list.
HTH,
Bernie
MS Excel MVP
will remove the rows that were hidden by the filter, and you will get your compacted list.
HTH,
Bernie
MS Excel MVP
B
browniebodrum
Ah, I see. Sorry, I should have tried that first - I know 'hide' doesn't
work like that, that gives you the whole range including the hidden rows, so
I thought the effect would be the same when they'd been filtered. That's a
further useful thing I've learned today. Many thanks, again.
work like that, that gives you the whole range including the hidden rows, so
I thought the effect would be the same when they'd been filtered. That's a
further useful thing I've learned today. Many thanks, again.
G
Gord Dibben
brownie
Just for info...........
When rows are hidden, you can select a range including hidden rows and
F5>Special>Visible cells only>OK
Copy those and the hidden rows won't copy.
Gord Dibben MS Excel MVP
Just for info...........
When rows are hidden, you can select a range including hidden rows and
F5>Special>Visible cells only>OK
Copy those and the hidden rows won't copy.
Gord Dibben MS Excel MVP
B
browniebodrum
Great, thanks - another useful tip I didn't know...
V
Věra Husárvá
browniebodrum pÃÅ¡e:
P
Pete_UK
Put this in a helper column:
=MOD(ROW(A1)-1,35)+1
and copy down as far as required. It will give you the numbers 1 to35
and then repeat these over and again. Apply autofilter to this column,
and just choose any number from the filter pull-down (eg 10) to
display rows 10, 45, 80 etc.
Hope this helps.
Pete
=MOD(ROW(A1)-1,35)+1
and copy down as far as required. It will give you the numbers 1 to35
and then repeat these over and again. Apply autofilter to this column,
and just choose any number from the filter pull-down (eg 10) to
display rows 10, 45, 80 etc.
Hope this helps.
Pete
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.