Working with Numeric Information in String

Art Schneiderheinze

In attempt to reduce the number of references to another sheet, I am looking to combine the cell values across 4 columns into a combined string:

Sheet 2

COURSE | STATUS | TERM DEV | ENROLLMENT | COMBINED
ABC 123 | Master | 202110 | 24 | ABC123#Master&20110?24*

In Sheet 1, I'd like to collect all of the combined strings, if the TERMDEV is above a certain number. In other words, I'd like to FIND the TERMDEV in the combined string (number between the & and ?), and only collect those if that number is greater than a number in Sheet 1

Sheet 1

TERM | NUMBER OF INSTANCES | ENROLLMENT TOTAL
202060 | 3 | 132

If I enter 202060, in Sheet 1m it would (1) count the number of instances in Sheet 2 of the TERMDEV being equal to or greater than the 202060, and (2) SUM the enrollment across those instances. In this example, there would be three instances in Sheet 2 when TERMDEV is equal to or greater than 202060 (the number between the & and ? symbols), and the total enrollment of students across those three instances is 132 (the number between the ? and * symbols).

Any thoughts on how to work with numeric data within a combined string by referencing another sheet?

Paul Newcome

Honestly... Working in the combined string in this case isn't really going to save you in the way of references as you will still need to reference the individual number columns for the sum portions. After that the only thing left is counting the number of rows that have a Term Dev higher than x_number which is easier to do when referencing the Term Dev column as opposed to the combined string column.

What is the end goal for these metrics? Have you looked into pulling a report and then using summary fields for the counts/sums?

Art Schneiderheinze

Currently, this is what I do. I reference Sheet 2 and do COUNTIF the TERM DEV is greater. There are 20,000+ rows in Sheet 2. It could be more, but I had to limit the data for the past two years. Actually, I reference not only the TERM DEV column, but all of the columns in Sheet 2 in separate columns in Sheet 1.

Basically, Sheet 2 is a list of required and elective courses in a program. The Program Leaders chooses their program from a drop-down, and the rows on the sheet update to show the required and elective courses in the program. It also shows if there is a "course master" (or template) for the course when it was last developed (from Sheet 3) and the percentage of sections that have been on the schedule (since the course master was last developed) that had that course master copied into it. (Some programs allow instructors to use something other than the course master.)

As a quick example, a Program Leader may see there are 10 required courses in a program. It shows of those 10 which have course masters (let's say 8) and when each was last developed. For ABC 123, it would show the master was last developed in 202110 (this is a term code in our system), as this is tracked in Sheet 3. Then, from Sheet 2, it would show that that course has been on the schedule, say 3 times (of the 20,000 rows, it counted 3 instances of sections, with enrollment greater than 0, that were on the schedule after 202110), and the total enroll of students in those 3 sections is 132.

I know how to do the individual references. However, since there is 20,000 rows of data, and I really want Sheet 2 to be based off of a drop-down of programs, I am already just about at 100,000, which means I can't do very much more providing information about courses to program leaders, and I have to restrict the # of sections in Sheet 2 to the last two years. (Course masters could be up to 5 years old.)