Continious Data

  • Thread starter Thread starter TFMR
  • Start date Start date
T

TFMR

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$10))),"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
 
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.


Hope this will clear the question.

Thanx

Hassan
 
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",IF(AND(A2="Stop",A1="start",A3="stop"),"Stop",""))
 
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","")))
 
Back
Top