1

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

2 Answers 2

0

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.

1
0

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.

7
  • 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. Oct 12, 2013 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). Oct 12, 2013 at 0:46
  • So you’re suggesting that the OP should set cells D2, D4, D7, D9, D17, etc…, manually? Oct 12, 2013 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. Oct 12, 2013 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, 2013 at 18:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .