Subscribe Bookmark RSS Feed

Having Match Search A Full Column

bml951

Community Trekker

Joined:

Dec 19, 2013

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
Solution

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
4 REPLIES
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Dec 19, 2013

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

Joined:

Jun 23, 2011

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?

Solution

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