vba help in seperate data datawise

[Malleshg24]

Hi Team,

Need your help in VBA seperating Data Datewise.

Column B Contains Files Received Dates it will in ascending order
I want to see date wise file Received, from Vertically to horizontally. as shown expected output from Column E.

Dates are in text when checked with isnumber. Date column May Increased.


Attached input file and expected output from Column E.

Thanks in advance for your help!


Regards,
mg

 

[AlanSidman]

If the following presentation is acceptable, then the Mcode that follows will solve your issue. This is a Power Query Solution.

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]ABC File Name[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1/10/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1/15/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1/20/2020[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Capegemini zip[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Wipro zip[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Infosys rmt zip[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Reliance zip[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Infosys zip[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Wipro zip[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]capegemini.zip[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Wipro. zip[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Infosys.zip[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Reliance zip[/td]
[/tr]
[/table]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ABC File Name", type text}, {"Received Dates", type date}, {"XYZ File Name", type text}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Received Dates", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Received Dates", type text}}, "en-US")[#"Received Dates"]), "Received Dates", "XYZ File Name")
in
    #"Pivoted Column"

 

[Malleshg24]

Hi Alan,

Thanks for your help , but I am looking solution in vba I don't have idea of powerQuery yet.


Regards,
mg

 

[Marc L]

Hi !​

​

According to your attachment a beginner starter demonstration :​

Sub Demo1()
        Dim V, C&, F&, L&
        [E1].CurrentRegion.Clear
        Application.ScreenUpdating = False
    With [A1].CurrentRegion.Columns(2)
           .AdvancedFilter xlFilterCopy, , [E1], True
            V = Range("E2", [E1].End(xlDown)).Value2
            [E1].CurrentRegion.Clear
            [A1,C1].Copy [E2].Resize(, UBound(V) * 2)
        For C = 1 To UBound(V)
            F = .Find(V(C, 1)).Row
            L = .Find(V(C, 1), , , , , xlPrevious).Row
        With Cells(3 + C * 2).Resize(, 2)
             Cells(F, 2).Copy .Cells(1)
            .Cells(2).Interior.Color = .Cells(1).Interior.Color
            .HorizontalAlignment = xlCenterAcrossSelection
        End With
            Range("A" & F & ":A" & L & ",C" & F & ":C" & L).Copy Cells(3, 3 + C * 2)
        Next
    End With
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[AlanSidman]

Suggest you look at the link in my signature block. Additionally, you should buy the book. M is for (Data) Monkey by Ken Puls and Miguel Escobar. You don't want to be left not knowing this powerful Excel function that will make your life easier. It is part of the future of Excelling.

 

[Marc L]

And as we are in the VBA section and as another section for Power Query yet exists in this forum​

so it should be smarter to ask the OP before to post any Power Query solution​

and in that case a moderator can move the thread to the appropriate section,​

more content for anyone smart enough to search in the Power Query section …​

Thanks.​

​

 

[Malleshg24]

Hi Marc,

Your genius. It worked out perfectly. Thanks a ton for all your help.


Regards,
mg