Using arrayformula to autofill formulas for new entries- comparing cells in the same column

9
votes

Relative beginner to using formulas in excel/google sheets, and I'm having some trouble figuring out how I can use an array formula to compare cells in different rows but the same column in google sheets (in my case, how I can do =IF(A3=A2,dothis,elsedothis) as an array formula. I'd also be interested if anyone has a different solution other than array formula to autofill formulas in.

The specifics:

I have a list of participants who've done an experiment, and some of them have done it multiple times. With a long list of participants and multiple entries for some participants, it's difficult to see where one participant's info ends and the next begins. So what I want is to alternate the rows in gray and white shading based off of the participant's number (ie. all of 001's entries in gray, all of 002's entries in white, 003's in gray, and so on). To do this, I put in a column on the right using a formula that checks if the participant number in the row above it is the same and, if not, it adds one (I'd like to use this to get a participant count later on). This is what the dataset looks like, and I've included the formula on the right.

A       B      ...  ...  X

001   9/1/16   ...  ...  1  (1)  
001   10/1/16  ...  ...  1  (=IF(A3=A2,X2,X2+1))
001   11/1/16  ...  ...  1  (=IF(A4=A3,X3,X3+1))
002   9/2/16   ...  ...  2  (=IF(A5=A4,X4,X4+1))
002   10/2/16  ...  ...  2  (=IF(A6=A5,X5,X5+1))
003   10/5/16  ...  ...  3  (=IF(A7=A6,X6,X6+1))
...

All this is working fine and dandy, but the problem is, when I enter a new row, it doesn't automatically fill in the formula, and so the shading doesn't adjust. I suppose I could redrag down the formula every time I enter in new participant info, but that's tedious and I'm not the only one using it, so it's going to get messed up pretty quickly. From what I looked up, arrayformula is what I should be using. But you have to refer to the column as a whole if you use arrayformula. Anyone have any ideas?

3
google-sheetsarray-formulas
Welcome to Stack Overflow. Please take the tour and checkout How to Ask. – Rubén

3 Answers

0
votes

One way to achieve the desired result:

To alternate the rows in gray and white shading based on the participant's number do the following:

  1. Under the Format Menu choose conditional formatting

Enteries for the Conditional format rules dialog

  1. In apply to range select the range e.g. A2:A16 (it may include blank cells)
  2. For Format cell if choose Custom formula is
  3. Type =isodd(A2:A16) in the text box below
  4. For Formatting style choose Custom
  5. Choose gray color from the fill button
  6. Finally hit Done

To get a participant count you can use the formula =countunique(A2:A16)

0
votes

You can do this alltogether without an additional helper column at all, just using conditional formatting:

The custom function to add into the conditional formatting option is this:

=isodd($A:$A)=TRUE

enter image description here

At the end if you still want your participant count, you can use this: 

=COUNTA(UNIQUE(A:A))
0
votes

Assuming you got the conditional formatting covered, I believe the formula(s) you use can be turned into one single array formula so that the output auto-expands when new rows are added. Example:

={1; ArrayFormula(IF(LEN(A3:A), offset(A2,0,0,ROWS(A3:A),1) + IF(A3:A = offset(A2,0,0,ROWS(A3:A),1), 0, 1),))}

See if that would work for you?

NOTE: the custom formula in conditional formatting used in the spreadsheet is

=AND($X1<>"", ISODD($X1))