New column based on row cell format

P

peteracain

Please help me with this problem. I have data in the following format
(millions of rows!!)

date (but if text format) 11/11/2001 - thursday
procedure code, operator code
procedure code, operator code
etc. (number of rows variable for each day)
date 12/11/2001 - friday
procedure code, operator code
procedure code, operator code
etc. (number of rows variable for each day)

I need to transpose the date (in date format) from the first row to a
new column cell for all the following rows of that day. Then detect a
new day and do the same,.. Hope that makes sense and thanks ahead of
time.
 
B

Bryan Hessey

Your estimate of "millions of rows" might mean that Excel is too limited
for you, but assuming that your data is on a Worksheet . . .

You need to determine what will identify the first row of a set,
without a rule to do that you could struggle a little. Put this as
'condition-for-first-row' in C2 below.

Perhaps =IF(RIGHT(D1,3)="day"

Insert 3 new columns, now A, B and C, the date, where applicable, is
now in column D.

In B1 put =D1
In C1 put 1

in C2 put =if(condition-for-first-row,1,C1+1)
or
in C2 put =if(RIGHT(D1,3)="day",1,C1+1)

in B2 put =if(C2=1,D2,B1)

Formula-drag B2 and C2 to the end of your data.

Check that you have the results required. (I suggest you save the
file here).

Copy Column B and Paste Special, Formats, then Paste Special, Values
into cell A1

You can then delete column B and C leaving the data as requested.

Hope this helps.
l
 
Top