Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for help with EXCEL 2003
I have an EXCEL 2003 worksheet with four columns and about one thousand rows
containg this sample data: NAME BIRTHDATE ANNIVERSARY DEATH DATE John Smith June 1, 1920 July 15, 1940 August 1, 1980 Mary Jones January 1, 1930 September 15, 1955 October 1, 1995 I would like to create a worksheet that would sort all of the information by date. For instance, the worksheet would display the above data in one column like this: June 1, 1920 John Smith Birthdate January 1, 1930 Mary Jones Birthdate July 15, 1940 John Smith Anniversary September 15, 1955 Mary Jones Anniversary August 1, 1980 John Smith Death Date October 1, 1995 Mary Jones Death Date This may be impossible, of course, but I thought I would ask just in case anyone has an idea. I will appreciate advice. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for help with EXCEL 2003
Across the spreadsheet,
E2=A2 + " " + $B$1 F2=A2 + " " + $C$1 G2=A2 + " " + $D$1 copy down to last row Assuming data are in A2 to G1000 Rearrange data to read B2, E2 B3, E3 B4, E4 .... B1000, E1000 C2, F2 C3, F3 .... C1000, F1000 D2, G2 .... D1000, G1000 now sort by date. Close enough? Cheers "Robert Judge" wrote in message ... I have an EXCEL 2003 worksheet with four columns and about one thousand rows containg this sample data: NAME BIRTHDATE ANNIVERSARY DEATH DATE John Smith June 1, 1920 July 15, 1940 August 1, 1980 Mary Jones January 1, 1930 September 15, 1955 October 1, 1995 I would like to create a worksheet that would sort all of the information by date. For instance, the worksheet would display the above data in one column like this: June 1, 1920 John Smith Birthdate January 1, 1930 Mary Jones Birthdate July 15, 1940 John Smith Anniversary September 15, 1955 Mary Jones Anniversary August 1, 1980 John Smith Death Date October 1, 1995 Mary Jones Death Date This may be impossible, of course, but I thought I would ask just in case anyone has an idea. I will appreciate advice. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for help with EXCEL 2003
This will put the dates in column F and the Name and type of date in column
G. It then sorts by dates and combines the two columns into one column (F) and does some cleanup. Of course you realize that you are turning a 1000 row list into a 3000 row list. Don't be surprised if it takes a few seconds to complete it. Sub test() Dim rng As Range, c As Range Dim LRow As Long, i As Long LRow = Cells(Rows.Count, "A").End(xlUp).Row Set rng = Range("B2:D" & LRow) i = 2 Columns("F").NumberFormat = "mmmm d, yyyy" For Each c In rng Cells(i, 6) = c.Value Cells(i, 7) = Cells(c.Row, 1) & " " & Cells(1, c.Column) i = i + 1 Next Columns("F").AutoFit LRow = Cells(Rows.Count, "F").End(xlUp).Row Set rng = Range("F2:G" & LRow) rng.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Set rng = Range("F2:F" & LRow) For Each c In rng c = c.Text & " " & c.Offset(, 1).Text Next Columns("G").Delete Columns("F").AutoFit End Sub Mike F "Robert Judge" wrote in message ... I have an EXCEL 2003 worksheet with four columns and about one thousand rows containg this sample data: NAME BIRTHDATE ANNIVERSARY DEATH DATE John Smith June 1, 1920 July 15, 1940 August 1, 1980 Mary Jones January 1, 1930 September 15, 1955 October 1, 1995 I would like to create a worksheet that would sort all of the information by date. For instance, the worksheet would display the above data in one column like this: June 1, 1920 John Smith Birthdate January 1, 1930 Mary Jones Birthdate July 15, 1940 John Smith Anniversary September 15, 1955 Mary Jones Anniversary August 1, 1980 John Smith Death Date October 1, 1995 Mary Jones Death Date This may be impossible, of course, but I thought I would ask just in case anyone has an idea. I will appreciate advice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula request | Excel Worksheet Functions | |||
Using SQLRequest with Excel 2003 | Excel Discussion (Misc queries) | |||
Help request for vb .net in excel | Excel Programming | |||
SQL.REQUEST not working in EXCEL 2003 | Excel Worksheet Functions | |||
Excel help request | Excel Programming |