Request for help with EXCEL 2003

[Guest]

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.

 

[PY & Associates]

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

 

[Mike Fogleman]

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" & 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