CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Dr.Queso
Dr.Queso

Reputation: 791

Can I show the results of COUNTIF in one column only if there is data in another column?

I have a spreadsheet with multiple sheets.

Sheet A is generated from a template, and it is where users will enter in data to be formatted into a report; it will be deleted after that report is submitted.

As people enter info into Column A of Sheet A, they don't have to enter client information if we already have them entered in this spreadsheet, but otherwise they need to go over to Sheet B and enter it in.

To make it easier for them (so they don't have to check every time, or go back and enter info later after getting an error generating the report), I have used conditional formatting. The way it works is, column Z is set up as a helper column, and uses a "COUNTIF" function to check if the client ID in Sheet A, Column A is found in Sheet B, Column C. Sheet A Column Z returns 0 or 1 (or, theoretically, more than one if we had duplicates), and then Sheet A column A has conditional formatting based on Column Z's value-- if the client is already in, the cell for client ID turns green after they type it; if not, it turns red.

It works great! However, I am adding scripts to these sheets, and looping through them. This helper column is filled from Z1 to Z1000, which means I can't use sheet.maxRow() to get the last row.

I see plenty of workarounds on the script side, but I was wondering if anyone has a clever way to input a value into the helper column Z ONLY IF the corresponding cell (same row) in column A has a value using spreadsheet formulas.

I suspect that an array formula with a filter might do it, but I have little experience with either and can't get anything to work out.

Thanks for your help!

Upvotes: 0

Views: 49

Answers (1)

Martín
Martín

Reputation: 10187

You can use this expression with INDEX, MAX and ROW to make an ARRAYFORMULA only expandable until the last cell with value in column:

A2:INDEX(A2:A,MAX(ROW(A2:A)*(A2:A<>"")))

It will go from A2 to the maximum number of row in A in which A is different than null (that's why both conditions are multiplied).

Then you can set a formula like this in Z2 (check the ranges in case something is not right from reading your text, and delete all other formulas in Z too in case you weren't using an arrayformula already):

=BYROW(A2:INDEX(A2:A,MAX(ROW(A2:A)*(A2:A<>""))),LAMBDA(each,IF(each="","",COUNTIF('Sheet B'!C:C,each))))

Upvotes: 2

Related Questions