• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to copy and paste data into categories, taking reference from a certain column

jchia

New Member
Hi,

I would need some help in copy and pasting data into categories using a macro. In my file, there are name, rate and date columns. The date column vary across various months and dates. In the pasted data, for example if the date indicates 5-Dec-24, I would need the data to be categorised under 'Dec-24' & 2-Jan-25 needs to be classified under header 'Jan-25' etc.. Cells B4:D10 consists of the raw data and cells J3:L13 consists of the desired data I would need after using the macro. Could I please get some help and expertise from the community? thank you!
 

Attachments

  • Jo.xlsx
    9.4 KB · Views: 7
Hi, according to your attachment a starter VBA demonstration :​
Code:
Sub Demo1()
        Const F = "mmm-yy"
        Dim R&, S$
   With [B3].CurrentRegion.Rows
       .Sort .Cells(3), 1, Header:=1
        R = Application.CountA(.Columns(3))
        If R < .Count Then .Item(R + 1 & ":" & .Count).Clear
    For R = R To 2 Step -1
        S = Format$(.Cells(R, 3), F)
        While Format$(.Cells(R - 1, 3), F) = S:  R = R - 1:  Wend
       .Item(R).Insert
       .Cells(R, 1) = S
    Next
   End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Thanks, really appreciate it!
Current code copies the data and pastes into the exact same location, how do I paste the whole data to say, cell G3 instead, without modifying the initial set of data? Also, the current output reads 24-Dec (clicking into the cell it reads 24/12/24) instead Dec-24. Is it an excel setting or can it be modified in the code?
 
No, my demonstration does not copy the data as seems silly to do that on the same worksheet,​
it just inserts some rows for the so called 'categories' …​
You can mod the code to include a Copy statement then just change the With codeline for the initial cell reference.​
 
Got it, sorry really a newbie here. The reason why I need the data to be pasted to another location because the actual dataset t I have from cells B4:D10 is formularised from cells in another sheet.
Hence, I don't want to modify the initial dataset but paste the 'results' into other cells instead.
 
So weird as my code does not modify any formula !​
As I can’t reproduce your issue with such attachment …​
 
In the attached a table at cell F3:

1732484366451.png

This is a Power Query table without its header row (see above pic). The dates in the first column are different from your expected results because you show 2024 dates under Feb-25 which I suspect you don't want to do. When you change the source data table you'll just need to refresh the results by right-clicking in the results table somewhere and choosing Refresh (the refresh operation could be done with a macro).
 

Attachments

  • 1732484098954.png
    1732484098954.png
    39 KB · Views: 0
  • Chandoo58006Jo.xlsx
    18.7 KB · Views: 2
Back
Top