cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bml951
Level I

Having Match Search A Full Column

I'm currently trying to use the Match conditional in order to compare two columns of data to identify where they overlap. At the moment, the formula I entered seems to be working properly however it is only applied to the row it occurs in. Given that the column I'm searching is a few thousand rows longer, is there any way I can avoid a manual sort and modify my formula in order to get the results I need? Thanks so much for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Having Match Search A Full Column

I think I understand the confusion.

The Match() function won't do what you're looking for but I'm pretty sure we can get you what you want using Tables->Join on the original tables.

4661_Tables-Join.png

This will combine the two tables by joining rows that have the same value for given columns.

I assume you have tables that look like this.

4663_Course_Grades_and_Course_Enrollment_jmp-2.png

A table with Courses and Enrollments and another table with Courses and Grades.


Then choose Tables->Join.

Select the table you want to join with.

Then select the columns you want to match and click the "Match" button under the Match Columns section.

Then click OK.

4666_Join Dialog.png

You should get a table like this.

4667_untitled_10_and_Course_Grades_and_Course_Enrollment_jmp.png

I think that's what you're looking for. It is confusing to think about matching columns and having a Match() function.

You can learn more about the Match() function here.

-Jeff

-Jeff

View solution in original post

4 REPLIES 4
Jeff_Perkinson
Community Manager Community Manager

Re: Having Match Search A Full Column

I don't think I'm understanding what you're trying to do.

Can you post an example of the data table that you've got and an example of the result you'd like in each row?

-Jeff

-Jeff
bml951
Level I

Re: Having Match Search A Full Column

I'm looking at college course enrollment, and the average grades given. However, my enrollment list is more comprehensive with over double the number of courses from a separate spreadsheet. I thought I could combine them, then use the 'Match' conditional to identify the courses that I have both grade reports and enrollment reports for. This small table illustrates my issue. When there's an extra course in one of the columns, it causes the courses that are in both data sets to displace preventing the Match conditional from working. The data sets I'm using are fairly large (a few thousand courses) so is there anyway to tweak the formula so this won't occur? I'd like to identify the courses that I have both a grade and enrollment report for without having to do so manually.

Course(1)Course(2)Enrollment
Cl123CL123

25%

CL124CL12436%
Cl125CL12545%
CL212CL126N/A
Cl212
pmroz
Super User

Re: Having Match Search A Full Column

It seems like you would have better luck with the JOIN or UPDATE function (under the TABLES menu)

But, I'm still not entirely clear as to what you're trying to do.  Can you post before and after datasets?

Jeff_Perkinson
Community Manager Community Manager

Re: Having Match Search A Full Column

I think I understand the confusion.

The Match() function won't do what you're looking for but I'm pretty sure we can get you what you want using Tables->Join on the original tables.

4661_Tables-Join.png

This will combine the two tables by joining rows that have the same value for given columns.

I assume you have tables that look like this.

4663_Course_Grades_and_Course_Enrollment_jmp-2.png

A table with Courses and Enrollments and another table with Courses and Grades.


Then choose Tables->Join.

Select the table you want to join with.

Then select the columns you want to match and click the "Match" button under the Match Columns section.

Then click OK.

4666_Join Dialog.png

You should get a table like this.

4667_untitled_10_and_Course_Grades_and_Course_Enrollment_jmp.png

I think that's what you're looking for. It is confusing to think about matching columns and having a Match() function.

You can learn more about the Match() function here.

-Jeff

-Jeff