Formula For Current Sequence - Reversal Required

[glerwell]

Hi

I have a formula which returns a current sequence. I want to change the format so that the most recent data will be added the end of my data and not to the begining which I am currently doing.

Here is the data showing the old and new format and the formula that I currently have:

Excel Workbook
	B	C	D	E	F	G	H	I	J
2	OLD FORMAT	*	*	*	*	NEW FORMAT	*	*	*
3	*	*	*	*	*	*	*	*	*
4	DATE	RESULT	*	CURRENT SEQUENCE	*	DATE	RESULT	*	CURRENT SEQUENCE
5	07 - 07 - 2011	WON	*	WON 1	*	02 - 07 - 2011	WON	*	WON 1
6	06 - 07 - 2011	LOST	*	*	*	02 - 07 - 2011	WON	*	FORMULA NEEDED TO GET THE ABOVE RESULT
7	06 - 07 - 2011	WON	*	*	*	02 - 07 - 2011	WON	*	*
8	05 - 07 - 2011	LOST	*	*	*	04 - 07 - 2011	LOST	*	*
9	05 - 07 - 2011	WON	*	*	*	05 - 07 - 2011	LOST	*	*
10	05 - 07 - 2011	LOST	*	*	*	05 - 07 - 2011	WON	*	*
11	04 - 07 - 2011	LOST	*	*	*	05 - 07 - 2011	LOST	*	*
12	02 - 07 - 2011	WON	*	*	*	06 - 07 - 2011	WON	*	*
13	02 - 07 - 2011	WON	*	*	*	06 - 07 - 2011	LOST	*	*
14	02 - 07 - 2011	WON	*	*	*	07 - 07 - 2011	WON	*	*
Sheet1

I have tried changing the indexes from C6:C14 to C14:C6 and C5:C14 to C14:C5 but excel reverts them back.

Any ideas?

Thanks

 

[Aladin Akyurek]

Care to explain how E5 and J5 are obtained in words?

 

[jasonb75]

Array confirmed with Shift Ctrl Enter

=IFERROR(LOOKUP("ZZZ",H5:H14)&" "&ABS(MAX(IF(H5:H14="WON",ROW(H5:H14)))-MAX(IF(H5:H14="LOST",ROW(H5:H14)))),"")

 

[glerwell]

Thanks Jason, seems to be working!