Is there an Excel formula to auto-fill a column based on other columns-CodePudding

Let say I have this table in Google Spreadsheet :

| x | y | z |

|---|---|---|

| a | 1 |blue|

| a | 1 | *i*  |

I'm looking for a formula that would fill the i value with "blue" if column x and y are the same as another line in my table.

CodePudding user response：

I would use a helper column. In W1 enter:

=X1 & CHAR(1) & Y1

and copy downward. Then in Z1 use:

=IF(COUNTIF(W:W,W1)>1,"blue","")

and copy downward

CodePudding user response：

If adding an additional column isnt a big issue you can just concatenate the values from x and y and the do a simple if statement in z.

So i column d put:

=A2&B2

And then in column c put:

=IF(SUMPRODUCT(COUNTIF(D2:D3,D2:D3)-1)>0, "blue")

This will count the number of times the value appears in the range for the D column, so adjust the range according to your column length. When greater than appearing once it will place "blue" in the z column.

Just drag and drop the formula for all the rows.