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

Discuss the spreadsheet application

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

Postby DavidATK » Thu Feb 13, 2020 1:04 am

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
DavidATK
 
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am

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

Postby MrProgrammer » Thu Feb 13, 2020 1:18 am

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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3964
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby DavidATK » Fri Feb 14, 2020 12:13 am

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

Postby DavidATK » Sat Feb 15, 2020 6:59 pm

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
DavidATK
 
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am

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

Postby MrProgrammer » Sat Feb 15, 2020 7:32 pm

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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3964
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby DavidATK » Sat Feb 15, 2020 8:44 pm

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
DavidATK
 
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am

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

Postby robleyd » Sun Feb 16, 2020 12:52 am

You might also find [Tutorial] Absolute, relative and mixed references a useful resource.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3463
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Postby DavidATK » Sun Feb 16, 2020 11:12 pm

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
DavidATK
 
Posts: 5
Joined: Thu Feb 13, 2020 12:22 am


Return to Calc

Who is online

Users browsing this forum: FJCC and 17 guests