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

Tim5099

New Member
Joined
Apr 14, 2013
Messages
5
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 columnSecond columnDateSameGoodBad
REDRED4/1/20131
REDYELLOW4/1/20131
REDGREEN4/1/20131
BLUEYELLOW4/1/2013
YELLOW4/1/2013
GREEN4/1/2013

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 14, 2013
Messages
5
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,674
Office Version
  1. 2010
Platform
  1. Windows
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
 

Tim5099

New Member
Joined
Apr 14, 2013
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,674
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 14, 2013
Messages
5
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.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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:

REDREDSAME
REDYELLOWGOOD
REDBLUEBAD
REDGREENBAD
GREENGREENSAME
GREENYELLOWGOOD
GREENBLUEBAD
GREENREDBAD
BLUEBLUESAME
BLUEYELLOWGOOD
BLUEREDGOOD
BLUEGREENGOOD
YELLOWYELLOWSAME
YELLOWREDBAD
YELLOWGREENBAD
YELLOWBLUEBAD

<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...

XYDate SameGoodBad
REDRED4/1/2013 1
REDYELLOW4/1/2013 1
REDGREEN4/1/2013 1
BLUEYELLOW4/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

New Member
Joined
Apr 14, 2013
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top