CODEWITHSUNDEEP
vbaexcelexcel-formulaformulas
K.A.
K.A.

Reputation: 1

Copy a cell unless it contains the same value as a previous cell

On one worksheet I have 4 cells (e.g., A1 through A4) that can each contain one of a handful of text options (e.g., red, blue, green, or yellow). So on one worksheet these four cells may be filled in as: A1=Red, A2=Green, A3=Red, A4=Red.

On a second worksheet, I want to fill in a single cell with the types of color chosen for those four cells. In the given example, this cell on the second worksheet would read "Red/Green."

I'm looking for a formula I can put into the cell on the second sheet that will check the four cells and pull only one of each that contains a unique text. Is there a formula that would, for example, copy A1, then copy A2 only if it is different from A1, then copy A3 only if it is different from both A1 and A2, then copy A4 only if it is different from A1, A2, and A3?

I hope that made sense. Thank you so much for your help!

Upvotes: 0

Views: 265

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

You can produce a distinct list with this formula. But it cannot be entered into the first row of the second sheet. Row two is fine:

=IFERROR(""&INDEX(Sheet1!$A$1:$A$999,MATCH(,INDEX(COUNTIF($a$1:a3,Sheet1!$A$1:$A$999),,),)),"")

Notice the lowercase characters of "a". There are two of them. Change those to whichever column you are using for the distinct list on the second sheet.

Additionally, you can change the 999's to scan as many rows from the first sheet as you need.

Upvotes: 1

Related Questions