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

[#1]

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 ?

[#2]

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.

[#3]

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 

[#4]

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.

[#5]

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.

[#6]

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.

[#7]

Hi mark,

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

[#8]

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

[#9 · Accepted solution]

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 ];
);

[#10]

Hi Mark,

I have a similar task, but my "key" is a continuous data type.

In Excel it is very easy to create a look-up table and use it to transform highly un-linear characteristics.

In this case it is battery voltage data to estimates of state-of-charge (SoC) and vice versa. So, I would like to convert e.g. a data table with measured voltage data to SoC estimates. One challenge is that my look-up table may/will not have all the many voltage levels existing in the data, so I need the SoC corresponding to the nearest voltage level. E.g. voltage data is 3.801 V and look-up table has 3.800 V as the nearest and a corresponding SoC of 51%.

Sometimes my task is to convert "backwards" (SoC => voltage), but hopefully I could create that solution on my own given good inspiration

BR, Kim.