data transpose horizontally in a cell

[Hudson Andrew]

Hi all,

Is there a way we can do dates transpose for same values Horizontally . for example if we look at the below table

I want dates to organized horizontally in one cell like this (Aug-9-2017, Aug-10-2017 , Aug-11-2017) for the ID 41661
[TABLE="width: 315"]
<tbody>[TR]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
FacilityID BusDate
41661 8/9/2017
41661 8/10/2017
41661 8/11/2017

attached is the file for your reference.

 

[MickG]

Try this for Results starting "D1".

Sub MG14Aug28()
Dim Rng As Range, Dn As Range, n As Long, Dic As Object

Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

Set Dic = CreateObject("scripting.dictionary")

Dic.CompareMode = vbTextCompare

For Each Dn In Rng

    Dic(Dn.Value) = Dic(Dn.Value) & IIf(Dic(Dn.Value) _

    = "", Format(Dn.Offset(, 1), "mmm-dd-yyyy"), ", " & Format(Dn.Offset(, 1), "mmm-dd-yyyy"))

Next

With Range("D1").Resize(Dic.Count, 2)

    .Value = Application.Transpose(Array(Dic.Keys, Dic.items))

    .Columns.AutoFit

End With

End Sub

Regards Mick

 

[Mike Szczesny]

Can you use this? Assume your date range is B1:B3. The formula is =concatenate(transpose(A1:A4&",")). Before you hit enter, you need to highlight the transpose(.......) part of your formula with your mouse. Hit F9 and remove the { and}. Hit enter.

[TABLE="width: 199"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]41661[/TD]
[TD="align: right"]8/9/2017[/TD]
[/TR]
[TR]
[TD="align: right"]41661[/TD]
[TD="align: right"]8/10/2017[/TD]
[/TR]
[TR]
[TD="align: right"]41661[/TD]
[TD="align: right"]8/11/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]41661[/TD]
[TD]42956 ,42957 ,42958 ,[/TD]
[/TR]
</tbody>[/TABLE]