Dear All,

I have file with data, I want to delete continious data in that report:
e.g.

Start
Stop
Start
Start
Start
Stop
Start
Stop
Stop
Stop

If continious Start then keep last and change previous Start with "null" and
if continious Stop then keep first Stop and remaining change with "null".

Thanks & Regards

Hassan

Source data as posted assumed in A1:A10

Place the below into B1 (all in one cell),
array-enter ie press CTRL+SHIFT+ENTER to confirm:
=IF(ROWS($1:1)=MAX(IF(A$1:A$10="Start",ROW(A$1:A$1 0))),"Start",
IF(ROWS($1:1)=MIN(IF(A$1:A$10="Stop",ROW(A$1:A$10) )),"Stop",
""))
then copy B1 down to B10 to return desired results.

Click YES below to celebrate success ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"TFMR" wrote:
I have file with data, I want to delete continuous data in that report:
e.g.

Start
Stop
Start
Start
Start
Stop
Start
Stop
Stop
Stop

If continuous Start then keep last and change previous Start with "null" and
if continuous Stop then keep first Stop and remaining change with "null".

Thnx Max for reply, but the thing is I am unable to get desired output.Here I
am explaining in briefly which should be there and which should be replaced
with null.

Start
Stop
Start null
Start null
Start
Stop
Start
Stop
Stop null
Stop null


Hope this will clear the question.

Thanx

Hassan


"Max" wrote:

Source data as posted assumed in A1:A10

Place the below into B1 (all in one cell),
array-enter ie press CTRL+SHIFT+ENTER to confirm:
=IF(ROWS($1:1)=MAX(IF(A$1:A$10="Start",ROW(A$1:A$1 0))),"Start",
IF(ROWS($1:1)=MIN(IF(A$1:A$10="Stop",ROW(A$1:A$10) )),"Stop",
""))
then copy B1 down to B10 to return desired results.

Click YES below to celebrate success ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"TFMR" wrote:
I have file with data, I want to delete continuous data in that report:
e.g.

Start
Stop
Start
Start
Start
Stop
Start
Stop
Stop
Stop

If continuous Start then keep last and change previous Start with "null" and
if continuous Stop then keep first Stop and remaining change with "null".

Yes, dismiss the earlier. My apologies. Mis-interp'd it.
I'm out for the time being. Hang around.
Others might step-in with something for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"TFMR" wrote in message
...
Thnx Max for reply, but the thing is I am unable to get desired
output.Here I
am explaining in briefly which should be there and which should be
replaced
with null.

Start
Stop
Start null
Start null
Start
Stop
Start
Stop
Stop null
Stop null


Hope this will clear the question.

For the following formula to work, your data CANNOT start in Row 1. I'll
assume it starts in A2. Put this formula in B2 and copy it down for as far
as you like (that means, it can be copied past the end of your current data
in anticipation of future data being added)...

=IF(A2="","",IF(A2="Start",IF(A2=A3,"null",""),IF( A1=A2,"null","")))

--
Rick (MVP - Excel)


"TFMR" wrote in message
...
Thnx Max for reply, but the thing is I am unable to get desired
output.Here I
am explaining in briefly which should be there and which should be
replaced
with null.

Start
Stop
Start null
Start null
Start
Stop
Start
Stop
Stop null
Stop null


Hope this will clear the question.

Thanx

Hassan


"Max" wrote:

Source data as posted assumed in A1:A10

Place the below into B1 (all in one cell),
array-enter ie press CTRL+SHIFT+ENTER to confirm:
=IF(ROWS($1:1)=MAX(IF(A$1:A$10="Start",ROW(A$1:A$1 0))),"Start",
IF(ROWS($1:1)=MIN(IF(A$1:A$10="Stop",ROW(A$1:A$10) )),"Stop",
""))
then copy B1 down to B10 to return desired results.

Click YES below to celebrate success ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"TFMR" wrote:
I have file with data, I want to delete continuous data in that report:
e.g.

Start
Stop
Start
Start
Start
Stop
Start
Stop
Stop
Stop

If continuous Start then keep last and change previous Start with
"null" and
if continuous Stop then keep first Stop and remaining change with
"null".

Hi,

I think this is what you might be looking for: Put the first formula in B2
and copy it down

=IF(AND(A2="start",A1="start",A3="stop"),"Start",I F(AND(A2="Stop",A1="start",A3="stop"),"Stop",""))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"TFMR" wrote:

Dear All,

I have file with data, I want to delete continious data in that report:
e.g.

Start
Stop
Start
Start
Start
Stop
Start
Stop
Stop
Stop

If continious Start then keep last and change previous Start with "null" and
if continious Stop then keep first Stop and remaining change with "null".

Thanks & Regards

Hassan