Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I work with Excel 2003

I have a Teams (example: G50) and each team there are some Groups (example: 70-1, 70-2, etc.).

I want to set a condition that determined the following rule (pic example):

enter image description here

if G50 = 70-1 or 70-2 or 70-3 etc. (up to 5 Groups)

if G51 = 71-1 or 71-2 or 71-3 etc. (up to 5 Groups)

The answer should be: 1-TRUE , 0-FALSE

examples:

  1. G50=70-5 , 1-TRUE (because the GROUP 70-5 belongs to TEAM G50)
  2. G50=71-1 , 0-FALSE (because the GROUP 71-1 does NOT belongs to TEAM G50)
  3. G52=70-1 , 0-FALSE (because the TEAM G52 does NOT belongs to GROUP 70-1)
share|improve this question

2 Answers 2

up vote 0 down vote accepted

Try this Array Formula. You have to press CTRL+SHIFT+ENTER

=IF(SUMPRODUCT(($A$1:$A$11=A1)*(LEFT($B$1:$B$11,FIND("-",B1,1)-1)=LEFT(B1,FIND("-",B1,1)-1)))>1,1,0)

I created Row 10 and row 11 data to demonstrate that

  1. The formula will work even if your data is not sorted

  2. Different data format (Should have - though)

enter image description here

FOLLOWUP

Array Formulas: See THIS

Explaining the formula

FIND("-",B1,1)

FIND locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. In the above formula I am trying to find "-" in B1

I then use LEFT to return the characters from the beginning of the string up till -.

Then I match it with the rest of the column to find a match. I then use SUMPRODUCT to return the sum of all the matches.

To understand how each formula works independently, I would recommend checking Excel's inbuilt help which explains each function with examples.

HTH

share|improve this answer
    
Dear Siddharth Rout, I don't understand your answer & I tried it anyway and It doesn't work :( –  Etan Apr 21 '13 at 5:16
    
Which part you didn't understand? –  Siddharth Rout Apr 21 '13 at 6:33
    
I didn't understand the: ctrl+shift+enter , and your formula - it doesn't work :( –  Etan Apr 22 '13 at 6:43
    
But which part? –  Siddharth Rout Apr 22 '13 at 6:45
    
I didn't understand the: ctrl+shift+enter , and your formula - it doesn't work :( –  Etan Apr 22 '13 at 6:45

Assuming your data given in the picture starts from A2 and the result you expect in C2, use the function =IF(VALUE(RIGHT(A2,2))+20=VALUE(LEFT(B2,2)),1,0) Here is the explanation:

  1. RIGHT(A2,2))+20 - checks two characters from right in cell A2 which is 50 and adds 20 to it making it 70
  2. LEFT(B2,2) - checks two characters from left in cell B2 which is 70
  3. VALUE - The excel returns the numbers 50+20 = 70 of cell A2 and 70 of cell B2 as text. To convert text to number i use this function
  4. IF - If the two characters from right side in cell A2 + 20 is equal to 2 characters from left side of cell B2, then mark as "1", else mark as "0"
share|improve this answer
    
WOW thank you so much! It works great! BUT, I forgot to mention something: if my data isn't 70-1 but 070-1 , because it doesn't work in this situation :( Thanks again <3 –  Etan Apr 19 '13 at 12:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.