topic Re: Having Match Search A Full Column in Discussions

Having Match Search A Full Column

bml951 — Thu, 19 Dec 2013 17:52:03 GMT

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!

Re: Having Match Search A Full Column

Jeff_Perkinson — Thu, 19 Dec 2013 22:17:02 GMT

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

Re: Having Match Search A Full Column

bml951 — Fri, 20 Dec 2013 17:13:26 GMT

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
Cl123	CL123	25%
CL124	CL124	36%
Cl125	CL125	45%
CL212	CL126	N/A
	Cl212

Re: Having Match Search A Full Column

pmroz — Fri, 20 Dec 2013 20:56:38 GMT

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?

Re: Having Match Search A Full Column

Jeff_Perkinson — Tue, 18 Oct 2016 20:31:11 GMT

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.

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.

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.

You should get a table like this.

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