VBA Code for transpose for Large Data Set

[suresh ullanki]

Dear All,

Can you please help me to transpose data. I have three columns "Emp_ID, Compensation Plan and Amount. I want Compensation Plan to be transposed to Basic salary, HRA, Bonus and Commuting Allowance by Columns. I have 4000 employees list which has more than 18k rows. Please help

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]EMP_ID[/TD]
[TD]Compensation Plan[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]152[/TD]
[TD]Basic Salary[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]152[/TD]
[TD]HRA[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]152[/TD]
[TD]Bonus[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]152[/TD]
[TD]Commuting Allowance[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]153[/TD]
[TD]Basic Salary[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]153[/TD]
[TD]HRA[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]153[/TD]
[TD]Bonus[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]153[/TD]
[TD]Commuting Allowance[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[strooman]

I know you ask VBA solution but that is not my specialty. Therefore a formula mapproach to get you going and to clarify.
First, is this the layout of your dataset?
EMP_ID in Column A, Compensation Plan in Column B, Amount in Column C ?


Excel 2016 Professional (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#C9E7A7]EMP_ID[/td][td=bgcolor:#C9E7A7]Compensation Plan[/td][td=bgcolor:#C9E7A7]Amount[/td][td][/td][td=bgcolor:#0070C0]EMP_ID[/td][td=bgcolor:#0070C0]Basic Salary[/td][td=bgcolor:#0070C0]HRA[/td][td=bgcolor:#0070C0]Bonus[/td][td=bgcolor:#0070C0]Commuting Allowance[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

154​

[/td][td]Basic Salary[/td][td]

€ 30,00​

[/td][td][/td][td]

154​

[/td][td]

€ 30,00​

[/td][td]

€ 33,25​

[/td][td]

€ 13,00​

[/td][td] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

157​

[/td][td]HRA[/td][td]

€ 40,00​

[/td][td][/td][td]

157​

[/td][td]

€ 40,00​

[/td][td]

€ 22,00​

[/td][td]

€ 38,00​

[/td][td]

€ 17,45​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

152​

[/td][td]Bonus[/td][td]

€ 31,00​

[/td][td][/td][td]

152​

[/td][td]

€ 31,00​

[/td][td]

€ 9,00​

[/td][td]

€ 17,00​

[/td][td]

€ 7,75​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

159​

[/td][td]Commuting Allowance[/td][td]

€ 38,00​

[/td][td][/td][td]

159​

[/td][td]

€ 38,00​

[/td][td]

€ 6,00​

[/td][td] [/td][td] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

159​

[/td][td]Basic Salary[/td][td]

€ 6,00​

[/td][td][/td][td]

160​

[/td][td]

€ 23,25​

[/td][td]

€ 18,00​

[/td][td]

€ 9,65​

[/td][td]

€ 15,00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

160​

[/td][td]HRA[/td][td]

€ 23,25​

[/td][td][/td][td]

153​

[/td][td]

€ 17,45​

[/td][td]

€ 24,00​

[/td][td]

€ 34,00​

[/td][td]

€ 25,00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

153​

[/td][td]Bonus[/td][td]

€ 17,45​

[/td][td][/td][td]

155​

[/td][td]

€ 81,00​

[/td][td]

€ 32,00​

[/td][td]

€ 12,00​

[/td][td]

€ 81,00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

155​

[/td][td]Commuting Allowance[/td][td]

€ 81,00​

[/td][td][/td][td]

156​

[/td][td]

€ 7,00​

[/td][td]

€ 30,00​

[/td][td]

€ 31,00​

[/td][td]

€ 6,00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

152​

[/td][td]Basic Salary[/td][td]

€ 9,00​

[/td][td][/td][td]

158​

[/td][td]

€ 23,25​

[/td][td] [/td][td] [/td][td] [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

155​

[/td][td]HRA[/td][td]

€ 32,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

160​

[/td][td]Bonus[/td][td]

€ 18,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

160​

[/td][td]Commuting Allowance[/td][td]

€ 9,65​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

155​

[/td][td]Basic Salary[/td][td]

€ 12,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

156​

[/td][td]HRA[/td][td]

€ 7,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

153​

[/td][td]Bonus[/td][td]

€ 24,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

153​

[/td][td]Commuting Allowance[/td][td]

€ 34,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

154​

[/td][td]Basic Salary[/td][td]

€ 33,25​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

152​

[/td][td]HRA[/td][td]

€ 17,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

160​

[/td][td]Bonus[/td][td]

€ 15,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]

152​

[/td][td]Commuting Allowance[/td][td]

€ 7,75​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td]

154​

[/td][td]Basic Salary[/td][td]

€ 13,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td]

160​

[/td][td]HRA[/td][td]

€ 19,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td]

160​

[/td][td]Bonus[/td][td]

€ 10,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td]

157​

[/td][td]Commuting Allowance[/td][td]

€ 22,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26​

[/td][td]

153​

[/td][td]Basic Salary[/td][td]

€ 25,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

27​

[/td][td]

156​

[/td][td]HRA[/td][td]

€ 30,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

28​

[/td][td]

152​

[/td][td]Bonus[/td][td]

€ 40,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

29​

[/td][td]

156​

[/td][td]Commuting Allowance[/td][td]

€ 31,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

30​

[/td][td]

157​

[/td][td]Basic Salary[/td][td]

€ 38,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

31​

[/td][td]

156​

[/td][td]HRA[/td][td]

€ 6,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

32​

[/td][td]

158​

[/td][td]Bonus[/td][td]

€ 23,25​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

33​

[/td][td]

157​

[/td][td]Commuting Allowance[/td][td]

€ 17,45​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

34​

[/td][td]

155​

[/td][td]Basic Salary[/td][td]

€ 81,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

35​

[/td][td]

155​

[/td][td]HRA[/td][td]

€ 9,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

36​

[/td][td]

157​

[/td][td]Bonus[/td][td]

€ 32,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Formula's:
E2 =IFERROR(INDEX($A$2:$A$1000; MATCH(0;COUNTIF($E$1:E1; $A$2:$A$1000);0));””)
Insert with Control+Shift+Enter (Not just Enter) and drag down.

F2 =IFERROR(INDEX($C$2:$C$1000;SMALL(IF($A$2:$A$1000=$E2;ROW(A$2:A$1000)-ROW(A$2)+1);COLUMNS($F$2:F2)));"")
Insert with Control+Shift+Enter (Not just Enter) and drag from left to right and down.

For 18k rows you will indeed better of with a VBA solution.

 

[strooman]

I'm sorry . . . wrong formula in F2. Please replace with:

F2 =SUMPRODUCT((($A$2:$A$1000=$E2)*($B$2:$B$1000=F$1));$C$2:$C$1000)

Insert with Enter and drag from left to right and down.

 

[JoeMo]

Here's a VBA solution. Assumes data are in Columns A:C, with EMP_ID header in A1, and produces an output in Cols E:I starting in E1.

Sub suresh()
'assumes data starts with header EMP_ID in cell A1 and is sorted by EMP_ID
Dim R As Range, Vin As Variant, Hdrs As Variant, Vid As Variant, ct As Long, Vout As Variant
Dim i As Long, j As Long
Set R = Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
Vin = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count).Value
Application.ScreenUpdating = False
Range("E:J").ClearContents
With R.Columns(1)
    .AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("E1"), unique:=True
End With
Hdrs = Array("Basic Salary", "HRA", "Bonus", "Commuting Allowance")
Vid = Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row).Value
Range("F1:I1").Value = Hdrs
ReDim Vout(1 To UBound(Vin, 1), 1 To 4)
For i = 1 To UBound(Vid, 1)
    For j = 1 To UBound(Vin, 1)
        If Vid(i, 1) = Vin(j, 1) Then
            ct = ct + 1
            Vout(i, ct) = Vin(j, 3)
            If ct = 4 Then
                ct = 0
                Exit For
            End If
        End If
    Next j
Next i
With Range("F2:I" & 1 + UBound(Vid, 1))
    .Value = Vout
    .EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub