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 have a large dataset in an Excel sheet where:

  • column A: quote number
  • column B: opportunity number
  • column C: sales order number.

One quote number will have multiple opportunity numbers. A sales order number will be generated if an opportunity is closed. What I need is a formula that can flag a quote as "closed" status when all opportunities related to that quote have a sales order number. E.g.,

Column A | Column B | Column C
---------+----------+----------
  123    |  247689  | K123
  123    |  678889  | K456
  578    |  555678  | K889
  578    |  678999  |      

For the above example quote 123 should be flagged as "closed".

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

My answer is somewhat similar to Doktoro Reichard’s.  My answer assumes that your data are valid (no blank cells in Columns A or B) but not that they are sorted.

I’ll use 2 and 1337 to represent the first and last rows where you have data; adjust this to your situation.  In cell D2 enter

=SUMPRODUCT(--(A2=A$2:A$1337), --ISBLANK(C$2:C$1337))

Drag/fill this down to cell D1337.  This will calculate the number of rows that have the same quote number (Column A) as the current row and have a blank order number (Column C).  This will be 0 for closed quotes and positive for open quotes.

share|improve this answer
    
Thanks Scott!! Your formula is what I needed! –  Jasmine Oct 12 '13 at 19:07
    
@Jasmine: You’re welcome; I’m glad I could help. If you believe that my answer is the best one, please accept it –– click on the check mark to the left of it. When your reputation reaches 15, you will be able to vote “up” for all the good answers –– to your own question(s) and other peoples’. See What should I do when someone answers my question? in the Asking section of the Super User Help Center for more information. –  Scott Oct 12 '13 at 19:35
add comment

Use a helper column. I'll assume that your data is sorted, as it will make my work a little easier:

The formula for the 1st helper column (here designed as D) is the following:

=OR(IS.EMPTY(A1);IS.EMPTY(B1);IS.EMPTY(C1))

This will return FALSE if both the quote number, the opportunity number and the sales order number are filled. This will return TRUE if one is empty.

With this, you can now do an =OR on all helper cells that refer to the same quote number (this is where sorting helps). If all helper cells for the respective quote return FALSE, the following:

=IF(OR(D1:D2);"Open";"Closed")

will write "Closed" if all cells are FALSE.

share|improve this answer
    
Maybe I’m missing something, but it appears to me that this answer assumes that there are exactly two rows (opportunities) for each quote. Yes, that’s true for Jasmine’s example data, but the question says, “One quote number will have multiple opportunity numbers,” so I believe that you’re assuming too much. –  Scott Oct 12 '13 at 0:41
    
Just for the example. Unfortunately, the ranges would have to be set individually; as you state correctly, there might be countless rows (hence why I referred the ease that is sorting). –  Doktoro Reichard Oct 12 '13 at 0:46
    
So you’re suggesting that the OP should set cells D2, D4, D7, D9, D17, etc…, manually? –  Scott Oct 12 '13 at 1:07
    
No. The D column, as stated, contains the first formula. Using those values the OP would create an =IF for each quote number. The range in the last formula would be the cells that match a single quote number. If I had to choose, however, I would probably go with your answer @Scott, it seems less contrived than mine. –  Doktoro Reichard Oct 12 '13 at 13:53
    
Thanks Reichard! Your suggestion works but I will have to set the range manually. Due to huge data, it can be tedious. Any other suggestion? –  Jasmine Oct 12 '13 at 18:52
show 2 more comments

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.