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

- JMP User Community
- :
- Discussions
- :
- Formula Column for "N" Instances of Column 1 per value of Column 2

- 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

May 6, 2020 1:01 PM
(636 views)

I apologize that this type of question has probably already been asked in the Discussions, but I cannot find a solution that works for the application I need. The search terms of "Sum" "Count" etc. are too generic and make it difficult to find a solution precisely for what I am trying to do.

I want to create a column with a formula of how many instances of "Column 1" occur per a given value of "Column 2"

Tabulate function can do this very easily by simply stating "N" for a given "Week" column, but I need it to be a Column Formula for the application.

I have created an example table with Apples, Oranges, and Bananas to demonstrate. I need a formula for the third column "Instances of this Fruit this Week" to list how many instances there are of "Fruit of the Day" for a given "Week". For example, the "Instances of Fruit per Week" column would list [5,2,5,5,5,5,2...] Because Apple occurs 5 times in Week=1, and Banana occurs 2 times in Week=1.

Thanks for the continued support with this great software, JMP community.

New Table( "Fruit Table",

Add Rows( 28 ),

New Column( "Fruit of the Day",

Character( 8 ),

"Nominal",

Set Values(

{"Apple", "Banana", "Apple", "Apple", "Apple", "Apple", "Banana",

"Orange", "Apple", "Banana", "Apple", "Banana", "Apple", "Apple",

"Apple", "Banana", "Apple", "Apple", "Apple", "Apple", "Banana", "Apple",

"Banana", "Apple", "Apple", "Apple", "Apple", "Banana"}

)

),

New Column( "Week",

Numeric,

"Nominal",

Format( "Best", 12 ),

Set Values(

[1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4,

4, 4, 4, 4]

)

),

New Column( "Instances of this Fruit this Week",

Numeric,

"Continuous",

Format( "Best", 12 ),

Set Values(

[., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .,

., ., ., .]

)

)

)

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

Here's a formula that will work. The Col Number() function counts the number of non-missing rows.

`Col Number( :Week, :Fruit of the Day, :Week )`

-Jeff

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: Formula Column for "N" Instances of Column 1 per value of Column 2

Created:
May 6, 2020 1:55 PM
| Last Modified: May 6, 2020 2:37 PM
(630 views)
| Posted in reply to message from cheese_stats 05-06-2020

This formula will work, but see @Jeff_Perkinson response below for the better answer

```
curFruit = :Fruit of the Day;
N Rows( Current Data Table() << get rows where( :Fruit of the Day == curFruit ) );
```

Jim

Highlighted

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

Here's a formula that will work. The Col Number() function counts the number of non-missing rows.

`Col Number( :Week, :Fruit of the Day, :Week )`

-Jeff

Highlighted
##

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

Re: Formula Column for "N" Instances of Column 1 per value of Column 2

OK......so everybody use Jeff's answer......it is the correct one...…..I don't know what I was thinking with my response:-)

Jim

Highlighted
##

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

Re: Formula Column for "N" Instances of Column 1 per value of Column 2

Yes, this works perfectly. I have created a script for the new column using the formula:

`Col Number()`

Thanks very much, everyone @Jeff_Perkinson @txnelson

Article Labels

There are no labels assigned to this post.