Display 1 or 0 Based on the time a number shows up.

I need help on a formula and I'm not sure how to even start. I want to compile a list of all open PO's but the issue I'm running into my data contains several line items from each open PO. So I cannot do a standard sumif based on PO number. I'm thinking the best way is to add a column to the query which will display a 1 for the first time a PO number shows up in the query then a 0 for each subsequent time it shows. Then I can do a sum if base don that column. Does anyone have an idea on the based way to build this formula. I've showing a very condensed version of data below for display purposes.



1 PO Line Date Val
2 P36 1 17-Jan
3 P36 2 17-Feb
4 P36 3 17-Mar
5 P36 4 17-Mar
6 P37 1 17-Feb
7 P37 2 17-Feb
8 P38 1 17-Mar
9 P39 1 17-Mar
10 P40 1 17-Apr
11 P41 1 17-Feb
12 P41 2 17-Jan
13 P41 3 17-Feb
14 P41 4 17-Jan

Originally Posted by ChemistB

So the first date is the date the PO is opened. Is the next date a closure and then the 3rd date is reopened and so forth?

No the PO could last up to a year and have several difference services each with their own line number. The dates are when the services for that line was rendered.

Originally Posted by leelnich

Assuming PO's in column A, paste this in D2 and copy down:
=--(COUNTIF($A$1:$A1,$A2)=0)

The double-dash is known as a double unary operator. It provides a mathematical operation so Excel shows 1/0 instead of TRUE/FALSE.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

This seems to have worked!

Originally Posted by ChemistB

If you are just looking to count unique PO numbers try

=SUMPRODUCT(1/COUNTIF($A$2:$A$100))

Is that what you are after? From your data, there's no way to tell if a PO is open or closed because you don't know how many line items it had on it.

Sorry I should have been more clear. The SQL pulling from the database is set to only pull open PO's. This is a tool to investigate old open PO's.

Hi ChemistB - OP did state:

...I want to compile a list of all open PO's...