Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- How do I copy a column multiple times while shifting each time one row down?

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 22, 2019 5:53 AM
(2140 views)

Hi JMP Community, I am looking for a solution to facilate copying, in one go, a column a number times (in the example below I have chosen 6 times), but all data should move one row down with every new copied column that is created? Thank you for your help!

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You already have two good solutions (and one marked as the solution), but there is another quick interactive way to do this too, that people sometimes forget. Select the original column and right-click on the column header choosing New Column Formula > Row > Lag Multiple. In the dialog put in the First Lag as 1 and the Last Lag as 6.

Dan Obermiller

4 REPLIES 4

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I copy a column multiple times while shifting each time one row down?

Created:
Nov 22, 2019 6:26 AM
| Last Modified: Nov 24, 2019 5:45 PM
(2135 views)
| Posted in reply to message from WernerL 11-22-2019

Hi @WernerL ,

Below is some code to do that (writing either values, or column formulas), but might I ask, why do you need to create a table like this? If you need to perform some operations row-wise with a value in a previous row from a previous column, there are more efficient ways than explicitly creating those lag columns.

This form writes values:

```
dt = Open("$SAMPLE_DATA\Big Class.jmp");
baseCol = Column("height");
nCopies = 6;
For( i = 1, i <= nCopies, i++,
spacerMat = J( i, 1, . );
valuesMat = spacerMat |/ Eval( baseCol << Get Values );
New Column( "Copy " || char(i), Numeric, "Continuous", Set Values( valuesMat ) );
);
```

This form writes column formulas:

```
dt = Open("$SAMPLE_DATA\Big Class.jmp");
baseCol = Column("height");
nCopies = 6;
For( i = 1, i <= nCopies, i++,
Eval(Substitute( Expr( New Column( "Copy " || Char( i ), Numeric, "Continuous", Formula( Lag( Subscript(xxCol,row()), i ) ) ) ), Expr( i ), i, expr(xxCol), baseCol ))
);
```

I hope this helps get you started!

//Edit 11/24/19: made a correction to the column formula version to correctly substitute the column defined at the start of the script into the column formula

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I copy a column multiple times while shifting each time one row down?

I agree with @julian in that I don't understand why you have such a need, but below are the steps that would allow you to do this interactively.

- Copy all of the values from the column 1 into the paste buffer.
- Right click on one of the rows in the rowstate column(the column with the row numbers) and select "Add Rows"
- In the Add Rows dialog box, select 6 rows to be added at the end.
- Go to the empty column header next to the header for the first column and right click and select "New Columns"
- In the New Columns dialog box, specify that you want to add 6 columns
- Once the columns are created, go to the second cell in the first new column and paste the values from the paste buffer into the cell. It will copy all of the data starting at cell(row) 2.
- Repeat this for each new column, moving down one row for each new column.

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You already have two good solutions (and one marked as the solution), but there is another quick interactive way to do this too, that people sometimes forget. Select the original column and right-click on the column header choosing New Column Formula > Row > Lag Multiple. In the dialog put in the First Lag as 1 and the Last Lag as 6.

Dan Obermiller

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I copy a column multiple times while shifting each time one row down?

Many thanks Dan, this is an incredibly straightforward and simple solution. I am sure there are many more hidden such hidden treasures in JMP!

Article Labels

There are no labels assigned to this post.