Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
How can I get Exel to display every (eg) 35th row only?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
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 "browniebodrum" wrote in message ... How can I get Exel to display every (eg) 35th row only? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
Great, Bernie, that worked fine, thanks very much.
BrownieBodrum "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... How can I get Exel to display every (eg) 35th row only? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
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)? "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... How can I get Exel to display every (eg) 35th row only? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
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 "browniebodrum" wrote in message ... 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)? "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... How can I get Exel to display every (eg) 35th row only? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
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. "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... 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)? "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... How can I get Exel to display every (eg) 35th row only? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
brownie
Just for info........... When rows are hidden, you can select a range including hidden rows and F5SpecialVisible cells onlyOK Copy those and the hidden rows won't copy. Gord Dibben MS Excel MVP On Tue, 15 Aug 2006 07:54:02 -0700, browniebodrum wrote: 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. "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... 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)? "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... How can I get Exel to display every (eg) 35th row only? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
Great, thanks - another useful tip I didn't know...
"Gord Dibben" wrote: brownie Just for info........... When rows are hidden, you can select a range including hidden rows and F5SpecialVisible cells onlyOK Copy those and the hidden rows won't copy. Gord Dibben MS Excel MVP On Tue, 15 Aug 2006 07:54:02 -0700, browniebodrum wrote: 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. "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... 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)? "Bernie Deitrick" wrote: 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 "browniebodrum" wrote in message ... How can I get Exel to display every (eg) 35th row only? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
browniebodrum pÃ*Å¡e: How can I get Exel to display every (eg) 35th row only? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I get Exel to display every (eg) 35th row only?
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 On Aug 21, 1:10*am, Vìra Husárvá <Vera wrote: browniebodrum pí¹e: How can I get Exel to display every (eg) 35th row only?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making a formula that will display a "word" in multile cells | Excel Worksheet Functions | |||
how to display subjects taught by a specific teacher upon selection of the teacher name in a drop down box | New Users to Excel | |||
How to display results to certain number of significant figures | Excel Discussion (Misc queries) | |||
How do you display Greek and Russian languages in excel? | Excel Discussion (Misc queries) | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |