1

WA90950 question first example

Update2: Changing all commas (,) to semicolons (;) gives me "Could not find a matrix value."

Update1: The formula that Rubén suggests:

=IF(COUNTIF($A$2:A&$B$2:B&$C$2:$C,A2&B2&C2)+COUNTIF($A$2:A&$D$2:D&$E$2:D,A2&D2&E2)>0,1,0)

Gets me this error. Any suggestions?

WA90950 question seond example

I have a Google sheet with 100 rows (individuals) and the following columns:

NAME (A) BIRTHDAY (B) BIRTHPLACE (C) TIME OF DEATH (D) PLACE OF DEATH (E)

I would like to create a formula in a new column (F) that outputs the value 1 if a person has the same NAME, BIRTHDAY and BIRTHPLACE or NAME, TIME OF DEATH and PLACE OF DEATH. Otherwise the output value should be 0.

Is this possible and if yes, how?

3

Assuming that the first row is used for column headers, add the following formula to F2

=ArrayFormula(    
  IF(
    OR(
      COUNTIF($A$2:A&$B$2:B&$C$2:C,A2&B2&C2)>1,
      COUNTIF($A$2:A&$D$2:D&$E$2:E,A2&D2&E2)>1
    )
  ,1
  ,0
 )
)

Then fill down the above formula as necessary

|improve this answer|||||
  • Thank you Rubén!! Will try it as soon as I'm at a computer again. One question: will this formula check all rows in my document (no matter how many names i have, marking the ones that matches any with the value 1 in column F? I new to this but from what I can see the formula only points to row 2? Sorry if it's a dumb question. – user1151640 Mar 16 '16 at 12:19
  • Sorry, I forgot to include $ in the initial cell of the range references to make them fix references. I corrected the formula. References without $ are "auto-adjustable" when you fill down or fill right the adjacent cells with them. You should try with a simple formulas like =A2 and =$A$2 to see how this works . – Rubén Mar 16 '16 at 12:36
  • Thanks! But the formula is not working, it seems to be selecting the correct columns/ranges but gives me the standard error message "errors in formula". Any suggestions? – user1151640 Mar 16 '16 at 18:15
  • Sorry again. I updated the answer. – Rubén Mar 16 '16 at 19:11
  • Thanks again for you patience and kindness! Unfortunately it's still not working: "Formula error", se updated status – user1151640 Mar 16 '16 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.