251414_en-US

liliconcom

Search the string in Column and copy the numeric value in 2nd column

I have an Excel Worksheet consisting of two columns, one columns has strings with person name and other column contain age of the person. I have almost a list of 500 person. Is there any way I can automate it?Like using script to assign age based on the person name ?

 

 

 

ScriptingRe: Search the string in Column and copy the numeric value in 2nd column

Here is a functioning example to learn from:

 

Names Default to Here( 1 );

// open reference data table
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// make container for lookup
lookup  = Associative Array();

// get names and ages for lookup
For Each Row(
 Insert Into( lookup, dt:name, dt:age );
);

Close( dt, No Save );

// open new table (use Big Class again, delete existing age column)
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Delete Columns( :age );

// make new age column and populate it using lookup table
dt << New Column( "new age", "Numeric", "Continuous" );
For Each Row(
 :new age  = lookup[ :name ];
);

Re: Search the string in Column and copy the numeric value in 2nd column

While @Mark_Bailey suggestion of using an Associative Array, will work, I believe that a simple Join will probably solve the problem.

1. Open the JMP table with 10,000 rows
2. Open in JMP, the Excel file
3. Using the JMP table from Excel as the base table, use
   1. Tables==>Update      to match the names and add in the ages to the base table
4. Save the updated table back as an Excel file

I suggest you take the time to read the documentation  "Discovering JMP" and "Using JMP"

     Help==>JMP Documentation Library

Re: Search the string in Column and copy the numeric value in 2nd columnHi mark,

Thanks for the suggestions. I am new to the JMP could you please suggest how I can start ?

Re: Search the string in Column and copy the numeric value in 2nd column

This case would be a good time to import the Excel workbook with the names and ages and then load them into an associative array for lookup.

Re: Search the string in Column and copy the numeric value in 2nd column

Hi Txnelson,

 

The JMP file has 10,000 rows and 2 columns. The 1st column name is Person and the 2nd is age. Yes it can be used to find the data table that has the age for a given name in the Excel spreadsheet.

 

The JMP data file has 10,000 rows having different names and ages (Strings and numbers)

 

Both the person name and age are listed in the JMP data file.

 

I have a excel file which has two columns and 500 rows, the 1st column has the persons name and the 2nd contains ages. I am trying to write a script which will look for all 500 data (person name) from excel from in the JMP data file and copy the age value corresponding to the person's name in the excel file.

 

 

Re: Search the string in Column and copy the numeric value in 2nd column

You have 10,000 JMP data tables....correct?  What are the data table names?  Can that be used to determine how to find the data table that has the age for a given name in the Excel spreadsheet?

 

Can you provide information about what data are in the 10,000 JMP data tables, and how do you get the age from the data table, once you find the correct JMP data table?

 

More specifics are really needed before one can attempt to solve your problem.

Re: Search the string in Column and copy the numeric value in 2nd column

Yes that's the easiest approach, but the problem is JMP data file has 10000 data sets of different names and the excel sheet has random 500 names from 10000 data sets of jmp file. So I want something which can search those particular name in the excel and past the age from jmp data file 

Re: Search the string in Column and copy the numeric value in 2nd column

Assuming the list of 500 names is in a JMP data table, and you have that csv file with the Names and Ages, you can easily input the csv file, and then use

     Tables==>Join

to match the name/age data table to the table with the list of 500 names.