Search code examples
if-statementgoogle-sheetsfiltergoogle-sheets-formuladistinct-values

Formula to return value if all non-empty values are same, else return empty?

I have a Google Sheet with many (many, many) cases of the following situation:

  A    B    C    D    E
1 a    b1             e1
2 a                   
3      b2        d    e2
4 a                   e2

Basically each row lists content (information about research papers) scraped from a different location; theoretically, the values in each row should be the same, but since some locations lacked some information, and sometimes the information differs in some minor (but possibly important) way, there isn't 100% agreement throughout.

I'd like for each cell below such a group to display one value if all the non-empty values in that column are the same and to display nothing at all if there's some disparity between the non-empty values. See row 5 below:

  A    B    C    D    E
1 a    b1             e1
2 a                   
3      b2        d    e2
4 a                   e2

5 a              d

This is basically a first programmatical clean-up to assist further manual labor (which is unavoidable).

There's an example sheet available here - the real thing would have about 18 sets of values (title, authors, ISBN, publication, URL, keywords, etc), and 270 columns (each for another publication). The orange rows at the bottom are just pasted in manually but show the values I would like to get in the blue rows via formulas.

I realize this can be done with a massive string of IFs, but... surely there must be a way to write a formula that will extract all the non-empty values from an array or group of cells, compare them with each other, and return a single value if they're all equal?

Unfortunately, I'm drawing a blank...

Solution 
  • =IFERROR(IF(COUNTA(UNIQUE(FILTER(B2:B5, B2:B5<>"", B2:B5<>"#N/A")))>1, ,
 UNIQUE(FILTER(B2:B5, B2:B5<>"", B2:B5<>"#N/A"))))

    0

    or shorter:

    =IF(COUNTUNIQUE(FILTER(D2:D5, D2:D5<>"", D2:D5<>"#N/A"))>1, ,
 UNIQUE(FILTER(D2:D5, D2:D5<>"", D2:D5<>"#N/A")))