Determining New Records In a Dataset

[mlamb08]

Hello,

I'm trying to determine from a large set of data, new records that have entered a data set over the course of a quarter.

I have one column with dates. All of the dates in this column are either 9/30/2013 or 6/30/2013. The next column includes an ID number. If an ID number is showing for 9/30/2013, but not 6/30/2013, I know that is a new ID number and would like to flag it as "NEW." If there is an ID listed for 6/30/2013, but not 9/30/2013, I want to flag it as "EXPIRED." If there is an ID number on both 9/30/2013 AND 6/30/2013 line, I would like to flag it as "EXISTING."

Is there a formula I can enter in another column to determine this? The same ID can be listed on multiple lines with the same date.

Lastly, I'm currently working in 2003 excel and would like to avoid using a pivot.

Thanks,

ML

 

[shift-del]

Hi

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
[TD="colspan: 4, align: center"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]Date[/TD]
[TD="align: left"]ID[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]30.06.2013[/TD]
[TD="align: right"]789[/TD]
[TD="align: left"]EXISTING[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]30.06.2013[/TD]
[TD="align: right"]789[/TD]
[TD="align: left"]EXISTING[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]30.09.2013[/TD]
[TD="align: right"]789[/TD]
[TD="align: left"]EXISTING[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]30.06.2013[/TD]
[TD="align: right"]456[/TD]
[TD="align: left"]EXPIRED[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]30.06.2013[/TD]
[TD="align: right"]456[/TD]
[TD="align: left"]EXPIRED[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]30.09.2013[/TD]
[TD="align: right"]123[/TD]
[TD="align: left"]NEW[/TD]

</tbody>

Zelle	Formel
C2	=IF(SUMPRODUCT(($A$2:$A$7=--"30.06.2013")*($B$2:$B$7=B2)),IF(SUMPRODUCT(($A$2:$A$7=--"30.09.2013")*($B$2:$B$7=B2)),"EXISTING","EXPIRED"),"NEW")

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>