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

#### Tim5099

##### New Member
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>

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?

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.

Last edited:
Give this macro a try...
Code:
``````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``````

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?

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,"")

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.

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

GREENGREEN; SAME
GREENYELLOW; GOOD

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

YELLOWYELLOW; SAME

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.

Last edited:
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

GREENGREEN; SAME
GREENYELLOW; GOOD

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

YELLOWYELLOW; SAME

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:

<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),"")

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.

Replies
1
Views
255
Replies
4
Views
558
Replies
4
Views
166
Replies
9
Views
322
Replies
1
Views
190

1,217,332
Messages
6,135,945
Members
449,974
Latest member
riffburn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back