VBA code to align data monthwise/Horizantally

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
You wanted the result on other sheet or on same sheet?
If same sheet, there is not enough column between MONTH and COUNT :)
 

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
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
 

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,144
Messages
5,640,388
Members
417,140
Latest member
whiteprose

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
Top