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.

 

[Balajibenz]

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.

 

[Zot]

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]

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]

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:

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