Comparing text values in two cells and adding a "1" to the appropriate result column.

[Tim5099]

Hello all,

I'm hoping someone can help me out. I have an excel file with some data in it. I have two columns with one of four words in it. For simplicity I'll call them RED, GREEN, BLUE, and Yellow. A few columns over I have columns "Same" "Good" and "Bad". I'm trying to figure out a way to get excel to put a 1 in the appropriate column, if cell A1 is Red I need excel to look at the word in cell B2 and put a 1 in the "same" column if A1 and B1 are the same word. If the word in B2 is Yellow I need excel to put a 1 in the "Good" column. If the word in B1 is GREEN or BLUE I need it to put a 1 in the bad column. The tricky part is each of the four words RED, GREEN, BLUE, and Yellow have a different set of words that will be called "good" or "bad". So if the word in cell A1 if Green for example it will have a different set of good and bad color words.

So it seems I need four different functions depending on what word is in A1. In the column next to these lists of words I have the date which I'll need to use to give me the sums of same, good, and bad for each day. But first things first. I think I need to use a combination of IF functions and MATCH function, but I can't figure out anything that works. Below is what I'm hoping it will look like when I've got the right formulas.

Any help is very much appreciated!

First column	Second column	Date		Same	Good	Bad
RED	RED	4/1/2013		1
RED	YELLOW	4/1/2013			1
RED	GREEN	4/1/2013				1
BLUE	YELLOW	4/1/2013
YELLOW		4/1/2013
GREEN		4/1/2013

<tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

Your question is a little confusing, at least to me.

You refer several times to cells B1 and B2, but from the rest of the description, I suspect you meant B1 each time, or else when you first mentioned A1, perhaps you meant A2 instead? In any case, please clarify carefully.

Secondly, for each of the four colours in column A, list the other colours that can occur in column B and the Same/Good/Bad result they should give.
You attempted to do that for Red in column A, but we'd need the others to consider possible approaches.

Hello all,

I'm hoping someone can help me out. I have an excel file with some data in it. I have two columns with one of four words in it. For simplicity I'll call them RED, GREEN, BLUE, and Yellow. A few columns over I have columns "Same" "Good" and "Bad". I'm trying to figure out a way to get excel to put a 1 in the appropriate column, if cell A1 is Red I need excel to look at the word in cell B2 and put a 1 in the "same" column if A1 and B1 are the same word. If the word in B2 is Yellow I need excel to put a 1 in the "Good" column. If the word in B1 is GREEN or BLUE I need it to put a 1 in the bad column. The tricky part is each of the four words RED, GREEN, BLUE, and Yellow have a different set of words that will be called "good" or "bad". So if the word in cell A1 if Green for example it will have a different set of good and bad color words.

Also, what result do you want (if any) in the right hand columns if column B is blank like the last 2 rows of your sample table?

 

[Tim5099]

You are right, I should have said A2 and B2. I have the column names in A1 and B1.

When the color in the A column is RED: a value of RED in the B column should result with a "1" in the Same column. YELLOW should result "1" in the Good column. And a BLUE or GREEN should result with a "1" in the Bad column.

When the color in the A column is GREEN: a value of GREEN in the B column should result with a "1" in the Same column. YELLOW should result "1" in the Good column. And a BLUE or RED should result with a "1" in the Bad column.

When the color in the A column is BLUE: a value of BLUE in the B column should result with a "1" in the Same column. YELLOW OR RED OR GREEN should result "1" in the Good column. There are no values that are Bad when BLUE is in the A column.

When the color in the A column is YELLOW: a value of YELLOW in the B column should result with a "1" in the Same column. A value of RED OR GREEN OR BLUE should result "1" in the Bad column. There will be no values in the Good column when YELLOW is in the A column.


I shouldn't have left those last two rows in column B blank, both column A and B will always have one of the four colors (words). Thanks for any help you can provide. I have literally thousands of rows like this and finding out the correct way to do this will save me many many hours.

 

[Rick Rothstein]

Give this macro a try...

Sub Colors()
  Dim X As Long, LastRow As Long
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Cells(StartRow, "C").Resize(LastRow - StartRow + 1, 3).Clear
  For X = StartRow To LastRow
    If Cells(X, "A").Value = Cells(X, "B").Value Then
      Cells(X, "C").Value = 1
    Else
      Select Case Cells(X, "A").Value
        Case "RED", "GREEN"
          Select Case Cells(X, "B").Value
            Case "YELLOW": Cells(X, "D").Value = 1
            Case Else: Cells(X, "E").Value = 1
          End Select
        Case "BLUE": Cells(X, "D").Value = 1
        Case "YELLOW": Cells(X, "E").Value = 1
      End Select
    End If
  Next
End Sub

 

[Tim5099]

Thanks! That populated the "same" column correctly. The good and bad columns are empty though. I've never used macros before so I'm have trouble seeing the problem. One of the words or colors in columns A and B is the letter A, could that be the cause of the trouble?

 

[Aladin Akyurek]

Why don'y you create a 2 column table, called ColorTable, showing the combinations and the value associated with like below:

REDRED; Same
REDYELLOW; Good

etc. so that you can invoke a VLOOKUP formula?

Example:

D1: Same, E1: Good, F1: Bad

In D2 invoked and copied across:

=IF(VLOOKUP(A2&B2,ColorTable,2,0)=E$1,1,"")

 

[Rick Rothstein]

Thanks! That populated the "same" column correctly. The good and bad columns are empty though. I've never used macros before so I'm have trouble seeing the problem. One of the words or colors in columns A and B is the letter A, could that be the cause of the trouble?

It is hard to say... code is designed to do something exactly, so I don't know how you attempted to change things to accomodate your actual words. Instead of feeding us some simplified version of your actual needs, why don't you show use the real word list and the outcomes you want for them.

 

[Tim5099]

The real words in the data are A=red, B=green, AA=yellow, O=blue. They correspond to four types of items being used. The goal is to find out how often an appropriate, inappropriate, or same item combination is being used. I switched them out with colors because typing A and AA's in the A column was giving me a headache, but I think I shot myself in the foot with that.

I've made a colortable with the values of column A and B, then the name of the column in which the "1" should be put.

REDRED; SAME
REDYELLOW; GOOD
REDBLUE; BAD
REDGREEN; BAD

GREENGREEN; SAME
GREENYELLOW; GOOD
GREENBLUE; BAD
GREENRED; BAD

BLUEBLUE; SAME
BLUEYELLOW; GOOD
BLUERED; GOOD
BLUEGREEN; GOOD

YELLOWYELLOW; SAME
YELLOWRED; BAD
YELLOWGREEN; BAD
YELLOWBLUE; BAD

I know I didn't ask this as clearly as I should have. My excel skills are very basic and I wasn't sure how to ask this.Thanks for everyones help so far, I'm going to see if I can get VLOOKUP to do its magic.

 

[Aladin Akyurek]

The real words in the data are A=red, B=green, AA=yellow, O=blue. They correspond to four types of items being used. The goal is to find out how often an appropriate, inappropriate, or same item combination is being used. I switched them out with colors because typing A and AA's in the A column was giving me a headache, but I think I shot myself in the foot with that.

I've made a colortable with the values of column A and B, then the name of the column in which the "1" should be put.

REDRED; SAME
REDYELLOW; GOOD
REDBLUE; BAD
REDGREEN; BAD

GREENGREEN; SAME
GREENYELLOW; GOOD
GREENBLUE; BAD
GREENRED; BAD

BLUEBLUE; SAME
BLUEYELLOW; GOOD
BLUERED; GOOD
BLUEGREEN; GOOD

YELLOWYELLOW; SAME
YELLOWRED; BAD
YELLOWGREEN; BAD
YELLOWBLUE; BAD

I know I didn't ask this as clearly as I should have. My excel skills are very basic and I wasn't sure how to ask this.Thanks for everyones help so far, I'm going to see if I can get VLOOKUP to do its magic.

Put the 2-column table you have:

REDRED	SAME
REDYELLOW	GOOD
REDBLUE	BAD
REDGREEN	BAD
GREENGREEN	SAME
GREENYELLOW	GOOD
GREENBLUE	BAD
GREENRED	BAD
BLUEBLUE	SAME
BLUEYELLOW	GOOD
BLUERED	GOOD
BLUEGREEN	GOOD
YELLOWYELLOW	SAME
YELLOWRED	BAD
YELLOWGREEN	BAD
YELLOWBLUE	BAD

<colgroup><col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3811;" width="107"> <col style="width: 48pt;" width="64"> <tbody>
</tbody>

say in A2:B20 on a sheet called Admin. Select the range and name it ColorTable uisng the Name Box on the Formula Bar.

Let's look at the data in A:G on Sheet1...

X	Y	Date		Same	Good	Bad
RED	RED	4/1/2013		1
RED	YELLOW	4/1/2013			1
RED	GREEN	4/1/2013				1
BLUE	YELLOW	4/1/2013			1
YELLOW		4/1/2013
GREEN		4/1/2013

<colgroup><col style="width: 83pt; mso-width-source: userset; mso-width-alt: 3953;" width="111"> <col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3640;" width="102"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;" width="76"> <col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

E2, just enter, copy across, and downn:

=IFERROR(IF(VLOOKUP($A2&$B2,ColorTable,2,0)=E$1,1,#N/A),"")

 

[Tim5099]

Thank you! That works great on replacement words I used in the example. When I switch in the letters from the data sheet I'll be all set. Thanks again.