VBA code to align data monthwise/Horizantally

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
80
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

Can someone help me with below requirement.

I have data sheet1 where there is "Month" header is column "I" and "COUNT" header in column "M". Sample data looks like below.


NumberNameMONTHCOUNT
121RamFebruary40
132CareyFebruary32
125SalimFebruary40
127KumarFebruary40
127KumarJanuary12
121RamJanuary16
132CareyJanuary20
125SalimJanuary40
132CareyMarch32
125SalimMarch40

what I am looking for is to re-arrange the month and count as shown below.


NumberNameMONTHJanuaryFebruaryMarchCOUNT
121RamFebruary4040
132CareyFebruary3232
125SalimFebruary4040
127KumarFebruary4040
127KumarJanuary1212
121RamJanuary1616
132CareyJanuary2020
125SalimJanuary4040
132CareyMarch3232
125SalimMarch4040

Code has to looks for the months present in "MONTH" column and need to insert each column for each month next to "MONTH" column itself.

Once columns are inserted respective count needs to be placed under that month and other cells must be left blank. Thank you in advance.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You wanted the result on other sheet or on same sheet?
If same sheet, there is not enough column between MONTH and COUNT :)
 
Upvote 0
You wanted the result on other sheet or on same sheet?
If same sheet, there is not enough column between MONTH and COUNT :)
If we cannot have it in same sheet then I am fine to have it in new worksheet in same workbook. Please be informed that the sheet has data from A-W columns I have only shown the sample above to get an idea and ouput must retain all the existing columns and data.
 
Upvote 0
If we cannot have it in same sheet then I am fine to have it in new worksheet in same workbook. Please be informed that the sheet has data from A-W columns I have only shown the sample above to get an idea and ouput must retain all the existing columns and data.
It can be on the same sheet but the COUNT cannot be in column M anymore to accommodate all the months from Jan to Dec. Otherwise VBA cannot refer to column M as COUNT since it is shifting when inserting month columns in between.
 
Upvote 0
It can be on the same sheet but the COUNT cannot be in column M anymore to accommodate all the months from Jan to Dec. Otherwise VBA cannot refer to column M as COUNT since it is shifting when inserting month columns in between.
In that case can you insert all the "MONTH" columns after the colum "W" or after the last column that has data.
 
Upvote 0
In that case here is the code. I'm just assuming your list starts from row 2 down. Column X would be Jan, Y Feb and so on

VBA Code:
Sub AllocateInMonthColumn()

Dim n As Long, eRow As Long, Mth As Long

eRow = Range("A1").End(xlDown).Row
For n = 2 To eRow
    Mth = Month(Range("I" & n) & " 1, 2021")
    Range("W" & n).Offset(0, Mth) = Range("M" & n)
Next

End Sub
 
Upvote 0
In that case here is the code. I'm just assuming your list starts from row 2 down. Column X would be Jan, Y Feb and so on

VBA Code:
Sub AllocateInMonthColumn()

Dim n As Long, eRow As Long, Mth As Long

eRow = Range("A1").End(xlDown).Row
For n = 2 To eRow
    Mth = Month(Range("I" & n) & " 1, 2021")
    Range("W" & n).Offset(0, Mth) = Range("M" & n)
Next

End Sub
that works like a gem man. thank you. can you help me to have headers in X (Jan), Y(Feb) etc.
 
Upvote 0
VBA Code:
Sub AllocateInMonthColumn()

Dim n As Long, eRow As Long, Mth As Long

eRow = Range("A1").End(xlDown).Row
For n = 1 To 12
    With Range("W1").Offset(0, n)
        .Value = MonthName(n, True)
        .HorizontalAlignment = xlCenter
    End With
Next
For n = 2 To eRow
    Mth = Month(Range("I" & n) & " 1, 2021")
    Range("W" & n).Offset(0, Mth) = Range("M" & n)
Next

End Sub
 
Upvote 0
Solution
VBA Code:
Sub AllocateInMonthColumn()

Dim n As Long, eRow As Long, Mth As Long

eRow = Range("A1").End(xlDown).Row
For n = 1 To 12
    With Range("W1").Offset(0, n)
        .Value = MonthName(n, True)
        .HorizontalAlignment = xlCenter
    End With
Next
For n = 2 To eRow
    Mth = Month(Range("I" & n) & " 1, 2021")
    Range("W" & n).Offset(0, Mth) = Range("M" & n)
Next

End Sub
thanks mate, is there a way to only have the headers for the months that are in "MONTH" Column rather than all the months.
 
Upvote 0
thanks mate, is there a way to only have the headers for the months that are in "MONTH" Column rather than all the months.
I don't get what you meant. Maybe can show how the result suppose to look like?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top