[Solved] Creating a new column from bits of existing column

Discuss the spreadsheet application
Post Reply
DavidATK
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am

[Solved] Creating a new column from bits of existing column

Post by DavidATK »

Hi,

I'm trying to create a column using the Nth (actually 10th) cell in a very long existing column.

Say using Col D to hold every 10th value in 1000 cell long Col A.

What is the formula to do this ?

Being trying for days but no success so far.

David.
Last edited by DavidATK on Sat Feb 15, 2020 8:48 pm, edited 3 times in total.
OpenOffice 4.1.2

Dell INSPIRON running XP
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Creating a new column from bits of existing column.

Post by MrProgrammer »

Hi, and welcome to the forum.
DavidATK wrote:I'm trying to create a column using the Nth (actually 10th) cell in a very long existing column. Say using Col D to hold every 10th value in 1000 cell long Col A.
If you don't have a column header and data begins in A1:        =OFFSET($A$1;10*(ROW()-1);0) In D1, fill the formula down the column.
If you have a column header in Cell A1 and data begins in A2: =OFFSET($A$2;10*(ROW()-2);0) in D2, fill the formula down the column.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
DavidATK
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am

Re: Creating a new column from bits of existing column.

Post by DavidATK »

Thanks for that.

I should have joined here ages ago.

David.

:)
OpenOffice 4.1.2

Dell INSPIRON running XP
DavidATK
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am

Re: [Solved] Creating a new column from bits of existing col

Post by DavidATK »

Working well so far.

To extend this a bit, What is the formula if I wish to include, say, four adjacent columns ?

I.e same as above but copying every tenth item in columns A1,B1,C1,D1 to columns G,H,I,K


I notice that the OpenOffice Calc 'HELP' isn't very helpful with it's notes on OFFSET. No reference at all to using the '$' sign.

It may be helpful to others as well as my self if the above equation was explained in detail. This is something that isn't covered very well in many tutorials.
It is clear that ($A$1) locks the cell A1 as the rererence cell, and everything thereafter comes from this. But the other bits are not so plain.


Many thanks,

David.
OpenOffice 4.1.2

Dell INSPIRON running XP
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Creating a new column from bits of existing col

Post by MrProgrammer »

DavidATK wrote:I notice that the OpenOffice Calc 'HELP' isn't very helpful with it's notes on OFFSET. No reference at all to using the '$' sign.
This is because the $ can be used in all Calc formulas, not just with OFFSET. To learn about this feature read section 8. Using formulas and cell references in the tutorial I previously gave you. I hope this will explain how to use the concept for your situation even though that section was not written with OFFSET in mind. This tutorial has important, fundamental material which you should understand before using spreadsheets. Please study it before asking additional questions, since it may answer them for you.
DavidATK wrote:To extend this a bit, What is the formula if I wish to include, say, four adjacent columns? I.e same as above but copying every tenth item in columns A1,B1,C1,D1 to columns G,H,I,K
Remove the $ preceeding A in $A$1 since you want to use a relative column reference; A is six columns before G. Leave the $ preceeding 1 so you will have an absolute row reference. Now you can copy the formula from G to H and I. If K was intended to be J, you can copy the formula to J also. If not, you will have to create a separate formula for K since it breaks the adjacent-column pattern.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
DavidATK
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am

Re: [Solved] Creating a new column from bits of existing col

Post by DavidATK »

Thanks,

The reason I asked directly is that it is often quicker to get to the bottom of a specific problem by asking than to delve through pages of explanatory text.
I should add that I have been using OpenOffice Calc for years, but never had to carry out the operation I ask about.

But your point is taken.

David.
OpenOffice 4.1.2

Dell INSPIRON running XP
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Creating a new column from bits of existing col

Post by robleyd »

You might also find [Tutorial] Absolute, relative and mixed references a useful resource.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
DavidATK
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am

Re: [Solved] Creating a new column from bits of existing col

Post by DavidATK »

Thanks for the link.
As I only use OpenOffice for one purpose (at the moment) I tend to read up on any new instruction I need as I need it.
It all helps though.
OpenOffice 4.1.2

Dell INSPIRON running XP
Post Reply