VBA code to align data monthwise/Horizantally

[Balajibenz]

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.

Number	Name	MONTH	COUNT
121	Ram	February	40
132	Carey	February	32
125	Salim	February	40
127	Kumar	February	40
127	Kumar	January	12
121	Ram	January	16
132	Carey	January	20
125	Salim	January	40
132	Carey	March	32
125	Salim	March	40

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

Number	Name	MONTH	January	February	March	COUNT
121	Ram	February		40		40
132	Carey	February		32		32
125	Salim	February		40		40
127	Kumar	February		40		40
127	Kumar	January	12			12
121	Ram	January	16			16
132	Carey	January	20			20
125	Salim	January	40			40
132	Carey	March			32	32
125	Salim	March			40	40

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.

 

[Zot]

You wanted the result on other sheet or on same sheet?
If same sheet, there is not enough column between MONTH and COUNT

 

[Balajibenz]

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]

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]

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]

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

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]

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

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]

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

 

[Balajibenz]

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]

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?