Home |
Search |
Today's Posts |
#1
|
|||
|
|||
New column based on row cell format
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. -- peteracain ------------------------------------------------------------------------ peteracain's Profile: http://www.excelforum.com/member.php...o&userid=26026 View this thread: http://www.excelforum.com/showthread...hreadid=393773 |
#2
|
|||
|
|||
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 peteracain Wrote: 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. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=393773 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
Can I format a cell in excel based on a list of about 20 items? | Excel Discussion (Misc queries) | |||
generate multiple rows based on cell value | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Conditional Format based on contents of cell. | Excel Worksheet Functions |