Grouping and Ungrouping of Data

[Riazq2]

The raw data is exported with names repeated in different rows and payment amount is in one column. I want the data of thousands of employees in a way that the payment amount appears in different columns against each wage type and against each employee name in one row

Actual - Problem

Employee Number	Name of employee	Wage Type Long Text	Amount	Payment Date
1	ABC	Basic Pay	644,471	31.12.2020
1	ABC	House Rent Allowance	354,459	31.12.2020
1	ABC	Utilities Allowance	109,560	31.12.2020
1	ABC	Cost of Medicine	96,671	31.12.2020
1	ABC	Security Guard Allowance	30,000	31.12.2020
1	ABC	Consultation Executives	77,337	31.12.2020
1	ABC	Basic Pay (A)	-
1	ABC	House Rent Allowance (A)	-
1	ABC	Utilities Allowance (A)	-
1	ABC	Cost Medicine (A)	-
1	ABC	PF Deduction (A)	-
1	ABC	Pension Fund (A)	-
1	ABC	Consultation (A)	-
1	ABC	Income Tax Deduction	(241,085)	31.12.2020
1	ABC	PF Deduction OG/Mgt	(64,447)	31.12.2020
1	ABC	St.Welfare Foundation	(50)	31.12.2020
1	ABC	Group Insurance	(5,115)	31.12.2020
1	ABC	Benevolent Fund E/O	(400)	31.12.2020
1	ABC	Pension Fund	116,005	31.12.2020
1	ABC	Benevolent Fund Bank E/O	400	31.12.2020
1	ABC	Payment Amount	790,870	31.12.2020
1	ABC	HBF Inst	150,100	31.12.2020
1	ABC	M/Car Inst	11,500	31.12.2020
1	ABC	Eid Advance Deduction	48,931	31.12.2020

Desired - Solution

Employee Number

Payment Date

Name of employee

Basic Pay

House Rent Allowance

Utilities Allowance

Cost of Medicine

Security Guard Allowance

Consultation Executives

Basic Pay (A)

House Rent Allowance (A)

Utilities Allowance (A)

Cost Medicine (A)

PF Deduction (A)

Pension Fund (A)

Consultation (A)

Income Tax Deduction

PF Deduction OG/Mgt

St.Welfare Foundation

Group Insurance

Benevolent Fund E/O

Pension Fund

Benevolent Fund Bank E/O

Payment Amount

HBF Inst

M/Car Inst

Eid Advance Deduction

1

31.12.2020

ABC

644,471.00​

354,459.00​

109,560.00​

96,671.00​

30,000.00​

77,337.00​

0​

0​

0​

0​

0​

0​

0​

-241,085.00​

-64,447.00​

-50​

-5,115.00​

-400​

116,004.78​

400​

790,869.67​

150,100.00​

11,500.00​

48,931.33​

 

[alansidman]

Pivot your data

 

[Riazq2]

the format is not correct

Sum of Amount

Wage Type Long Text

Employee Number

Name of employee

Payment Date

Basic Pay

Basic Pay (A)

Benevolent Fund Bank E/O

Benevolent Fund E/O

Consultation (A)

Consultation Executives

Cost Medicine (A)

Cost of Medicine

Eid Advance Deduction

Group Insurance

HBF Inst

House Rent Allowance

House Rent Allowance (A)

Income Tax Deduction

M/Car Inst

Payment Amount

Pension Fund

Pension Fund (A)

PF Deduction (A)

PF Deduction OG/Mgt

Security Guard Allowance

St.Welfare Foundation

Utilities Allowance

Utilities Allowance (A)

Grand Total

1​

ABC

31.12.2020

644471​

400​

-400​

77337​

96671​

48931.33​

-5115​

150100​

354459​

-241085​

11500​

790869.67​

116004.78​

-64447​

30000​

-50​

109560​

2119206.78​

(blank)

0​

0​

0​

0​

0​

0​

0​

0​

ABC Total

644471​

0​

400​

-400​

0​

77337​

0​

96671​

48931.33​

-5115​

150100​

354459​

0​

-241085​

11500​

790869.67​

116004.78​

0​

0​

-64447​

30000​

-50​

109560​

0​

2119206.78​

1 Total

644471​

0​

400​

-400​

0​

77337​

0​

96671​

48931.33​

-5115​

150100​

354459​

0​

-241085​

11500​

790869.67​

116004.78​

0​

0​

-64447​

30000​

-50​

109560​

0​

2119206.78​

Grand Total

644471​

0​

400​

-400​

0​

77337​

0​

96671​

48931.33​

-5115​

150100​

354459​

0​

-241085​

11500​

790869.67​

116004.78​

0​

0​

-64447​

30000​

-50​

109560​

0​

2119206.78​