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.
 

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
I don't get what you meant. Maybe can show how the result suppose to look like?
As I have mentioned in the sample data, I want to have only the Months which are present in my source data. My sample data above has only January, February and March hence code should only include these three months after last column and pull the respective numbers.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,127
Office Version
  1. 2016
Platform
  1. Windows
As I have mentioned in the sample data, I want to have only the Months which are present in my source data. My sample data above has only January, February and March hence code should only include these three months after last column and pull the respective numbers.
By saying that you meant just list whatever month found. Perhaps
1) Jan, Feb, Mac
2) Jan, Jun Sep

It can be skipped months like above? If I need to keep in order, then need to scan all the months existed first and sort. Otherwise you would have Mac, Jan, Oct, Feb if months are discovered while going down the list
 

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
By saying that you meant just list whatever month found. Perhaps
1) Jan, Feb, Mac
2) Jan, Jun Sep

It can be skipped months like above? If I need to keep in order, then need to scan all the months existed first and sort. Otherwise you would have Mac, Jan, Oct, Feb if months are discovered while going down the list
or can we just remove the months which don't have any value at all at the last?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,127
Office Version
  1. 2016
Platform
  1. Windows
or can we just remove the months which don't have any value at all at the last?
Yes, You are right. I thought about it on my way home. Here it is:
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
' Remove empty month colum
n = 1
Do While Not Range("W1").Offset(0, n) = ""
    If Application.CountA(Range(Range("W2").Offset(0, n), Range("W" & eRow).Offset(0, n))) = 0 Then
        Range("W1").Offset(0, n).EntireColumn.Delete
        n = n - 1
    Else
        n = n + 1
    End If
Loop

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,265
Messages
5,635,158
Members
416,844
Latest member
ryanangus496

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