IF than statement help

[beanyboy7]

Ok here's what I need:

I have a list of ID#'s Date's entered and $Value of Investment.

What I need to do is Eliminate the repeat ID#'s and keep only the most recent ones.

Any help that could be offered would be extremely helpful.

And for those of you celebrating. MERRY CHRISTMAS!

 

[santeria]

May or may not help :

http://www.mrexcel.com/board2/viewtopic.php?t=16876&highlight=eliminate+repeat+numbers

 

[beanyboy7]

Interesting thanks for the post link. However it's not exactly what I need. THen again, perhaps I need to clarify.

I need to eliminate the duplicate ID#'s without eliminating the most recent entry according to the date data.

You may need to explain the link you sent to me though.

Thanks again.

 

[Aladin Akyurek]

Post a sample of 10 rows of interest.

 

[santeria]

Just a ref in the archive here to some material that I thought could be used in deleting the IDs I thought you want deleted.
Plenty of material in the archives here about that task.

You may need to explain the link you sent to me though.
Thanks again.

 

[beanyboy7]

ID # I Value Code Date
1002 100202 9/3/2003 13:23
1002 100203 9/5/2003 11:44
1023 102302 6/25/2003 11:48
1023 102303 7/28/2003 12:48
1023 102304 10/15/2003 14:08
1023 102305 10/24/2003 12:40
1046 104602 6/5/2003 13:00
1046 104603 6/24/2003 14:55
1046 104604 6/24/2003 15:35

Here's a sample.

If you can help me clear this up I'd really appreciate it.

 

[Aladin Akyurek]

ID # I Value Code Date
1002 100202 9/3/2003 13:23
1002 100203 9/5/2003 11:44
1023 102302 6/25/2003 11:48
1023 102303 7/28/2003 12:48
1023 102304 10/15/2003 14:08
1023 102305 10/24/2003 12:40
1046 104602 6/5/2003 13:00
1046 104603 6/24/2003 14:55
1046 104604 6/24/2003 15:35

Here's a sample.

If you can help me clear this up I'd really appreciate it.

The last column appears to house date/time values. Right?

It seems I Value Code does not play a role in "clearing up". Right?

 

[beanyboy7]

That would be correct sir.
The I value code is the actual data I need to extract. However I need to use the ID# and the Date/time as the criterium.

I need to remove the duplicate ID#'s and keep only the most reccent records. THe most recent records will be determined by their date of entry. Which is what the date time colum is for.

Thanks, you guys rock. ray:

 

[Aladin Akyurek]

...
The I value code is the actual data I need to extract. However I need to use the ID# and the Date/time as the criterium...

In what follows it's expected that the data is sorted on the ID # column.

Book5
	A	B	C	D	E	F	G
1	ID#	IValueCode	Date	0			IValueCode
2	1002	100202	9/3/200313:23			3	100203
3	1002	100203	9/5/200311:44	1			102305
4	1023	102302	6/25/200311:48				104604
5	1023	102303	7/28/200312:48
6	1023	102304	10/15/200314:08
7	1023	102305	10/24/200312:40	2
8	1046	104602	6/5/200313:00
9	1046	104603	6/24/200314:55
10	1046	104604	6/24/200315:35	3
Sheet1

Formulas...

D1 must house a 0.

D2:

=IF(INDEX($C$2:$C$10,MATCH(A2,$A$2:$A$10))=C2,LOOKUP(9.99999999999999E+307,$D$1:D1)+1,"")

F2:

=LOOKUP(9.99999999999999E+307,D2:D10)

G2:

=IF(ROW()-ROW($G$2)+1<=$F$2,INDEX($B$2:$B$10,MATCH(ROW()-ROW($G$2)+1,$D$2:$D$10)),"")

 

[beanyboy7]

Thanks for the prompt reply: Mr. Aladin Akyurek

I just have one question though, regarding the formulas you posted.

List I contains about 3000+ entries and will be used for future entires. What I need to do is set it up so that the formula will continue to function after the first 10 entries and can be expanded upon in the future.

-THANKS AGAIN EXCEL GODS, visions of spreadsheets and funtions will dance in my head tonight.