Transpose dates in rows to columns

[Prakash K]

Dear Experts, I have random dates in E9:H100 (in odd rows like E9,E11,..,G9,G11,... & also some cellsblanks)... I need these dates in ascending order in 7th row from K7 (K7,L7,M7,N7,....) Please help me...

 

[Skyybot]

Try this. Remember to test on a COPY of your Workbook. Paste code into the Sheet code module of the Worksheet that you want to affect.

Option Explicit

Sub ExtractDates()

Dim sRng As Range, cell As Range, fDates(), i As Integer

Set sRng = Me.Range("E9:H100")

For Each cell In sRng
    If IsDate(cell.Value) Then
        ReDim Preserve fDates(i)
        fDates(i) = cell.Value
        i = i + 1
    End If
Next cell

For i = 0 To UBound(fDates)
    Me.Cells(i + 7, "K") = fDates(i)
Next i

With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("K7:K" & UBound(fDates) + 7)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

 

[HighAndWilder]

Dear Experts, I have random dates in E9:H100 (in odd rows like E9,E11,..,G9,G11,... & also some cellsblanks)... I need these dates in ascending order in 7th row from K7 (K7,L7,M7,N7,....) Please help me...

=LET(d,VSTACK($E$9:$E$100,$F$9:$F$100,$G$9:$G$100,$H$9:$H$100),TRANSPOSE(SORT(FILTER(d,INDEX(d,,1)<>""),1)))

 

[Skyybot]

Edit:

Option Explicit

Sub ExtractDates()

Dim sRng As Range, cell As Range, fDates(), i As Integer

Set sRng = Me.Range("E9:H100")

For Each cell In sRng
    If IsDate(cell.Value) Then
        ReDim Preserve fDates(i)
        fDates(i) = cell.Value
        i = i + 1
    End If
Next cell

For i = 0 To UBound(fDates)
    Me.Cells(i + 7, "K") = fDates(i)
Next i

With Me.Sort
        .SetRange Range("K7:K" & UBound(fDates) + 7)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

 

[Prakash K]

=LET(d,VSTACK($E$9:$E$100,$F$9:$F$100,$G$9:$G$100,$H$9:$H$100),TRANSPOSE(SORT(FILTER(d,INDEX(d,,1)<>""),1)))

#NAME ERROR

 

[HighAndWilder]

#NAME ERROR

What version of Excel are you using?

 

[HighAndWilder]

#NAME ERROR

Transpose dates in rows to columns.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
6
7							30/01/2024	03/02/2024	04/02/2024	28/02/2024	08/03/2024	09/03/2024	23/03/2024	02/04/2024	03/04/2024	25/04/2024	16/05/2024	04/07/2024	07/07/2024	21/07/2024	13/08/2024	06/12/2024	20/12/2024	31/12/2024
8
9			02/04/2024
10
11
12
13
14
15
16
17
18
19
20
21			30/01/2024
22
23	03/02/2024			25/04/2024
24
25
26
27
28
29
30
31
32
33
34
35	04/02/2024	20/12/2024
36
37
38
39
40
41
42
43
44
45
46
47				08/03/2024
48
49
50
51	03/04/2024
52
53
54
55	06/12/2024
56
57				09/03/2024
58
59
60
61
62
63	16/05/2024
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79	28/02/2024			04/07/2024
80
81	21/07/2024
82
83
84
85		23/03/2024
86
87		13/08/2024	31/12/2024
88
89
90
91
92
93
94
95
96
97
98
99	07/07/2024
100
101
102
Sheet2

Cell Formulas
Range	Formula
K7:AB7	K7	=LET(d,VSTACK($E$9:$E$100,$F$9:$F$100,$G$9:$G$100,$H$9:$H$100),TRANSPOSE(SORT(FILTER(d,INDEX(d,,1)<>""),1)))
Dynamic array formulas.

 

[Fluff]

Another possible option

=SORT(TOROW(E9:H100,1),,,1)

 

[Prakash K]

OFFICE 365

 

[Prakash K]

sir,

Edit:

Option Explicit

Sub ExtractDates()

Dim sRng As Range, cell As Range, fDates(), i As Integer

Set sRng = Me.Range("E9:H100")

For Each cell In sRng
    If IsDate(cell.Value) Then
        ReDim Preserve fDates(i)
        fDates(i) = cell.Value
        i = i + 1
    End If
Next cell

For i = 0 To UBound(fDates)
    Me.Cells(i + 7, "K") = fDates(i)
Next i

With Me.Sort
        .SetRange Range("K7:K" & UBound(fDates) + 7)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

working well... but i need dates in 7th row from K7,L7,M7.... & I NEED UNIQUE DATES ONLY