Excel Data Consolidation

[ialwayscapital]

I am working on a salary sheet and stuck on this situation.
Sheet has a to z columns with these headings.

What i need is, if employee name and employee code repeat several times, then i get sum of his duty, wages, pf, esi, pt, gross salary, advance, uniform, add money, take home, bank cheque, bank cash & cash in hand in ANOTHER WORKSHEET.
For example,
Pooja Yadav, 25 (duty), 9750 (wages), 650 (pf), 300 (esi), 160 (pt), 8640 (gross salary), 4000 (advance), blank (uniform), blank (add money), 4640 (take home), 3820 (bank cheque), blank (cash in hand.
Things written in brackets are just for understanding sake (consider it as column heading)

Please help me out, Thanks

 

[ialwayscapital]

Please NOTE that not all the employees have employee code, So for temporary employee, the employee code remains blank.

 

[Sam_D_Ben]

You mean something like this.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Emp Name	Emp Code	Duty	Wages	PF	ESI	PT	Gross Salary	Advance	Uniform	Add Money	Take Home	Blank Cheque	Cash In hand
2	PooJa Yadev	12345	25	9750	650	300	160	8650	4000			4640	3820
3	Sunny Deol	11225	8	9733	633	283	143	8633	3983			4623	3803
4	PooJa Yadev	12345	40	9765	665	315	175	8665	4015			4655	3835
5
6
7	WORKKSHEET
8	Emp Name	Emp Code	Duty	Wages	PF	ESI	PT	Gross Salary	Advance	Uniform	Add Money	Take Home	Blank Cheque	Cash In hand
9	PooJa Yadev	12345	65	19515	1315	615	335	17315	8015			9295	7655
10	Sunny Deol	11225	8	9733	633	283	143	8633	3983			4623	3803
Sheet3

Cell Formulas
Range	Formula
C9	=SUMIF($A2:$A4,$A9,C2:C4)
C10	=SUMIF($A3:$A5,$A10,C3:C5)
D9	=SUMIF($A2:$A4,$A9,D2:D4)
D10	=SUMIF($A3:$A5,$A10,D3:D5)
E9	=SUMIF($A2:$A4,$A9,E2:E4)
E10	=SUMIF($A3:$A5,$A10,E3:E5)
F9	=SUMIF($A2:$A4,$A9,F2:F4)
F10	=SUMIF($A3:$A5,$A10,F3:F5)
G9	=SUMIF($A2:$A4,$A9,G2:G4)
G10	=SUMIF($A3:$A5,$A10,G3:G5)
H9	=SUMIF($A2:$A4,$A9,H2:H4)
H10	=SUMIF($A3:$A5,$A10,H3:H5)
I9	=SUMIF($A2:$A4,$A9,I2:I4)
I10	=SUMIF($A3:$A5,$A10,I3:I5)
L9	=SUMIF($A2:$A4,$A9,L2:L4)
L10	=SUMIF($A3:$A5,$A10,L3:L5)
M9	=SUMIF($A2:$A4,$A9,M2:M4)
M10	=SUMIF($A3:$A5,$A10,M3:M5)

Muz

 

[intradayteam]

Thanks for the quick reply, but few questions rised.
[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]C9[/TH]
[TD]=SUMIF($A2:$A4,$A9,C2:C4)
[/TD]
[/TR]
</tbody>[/TABLE]

In above formula, value of A9 has to be written manually, how can i pull unique values to another worksheet, Please note that some temporary employees dont have employee code, so i have to pull unique values considering both.

 

[Sam_D_Ben]

Please try PIVOT.

Muz

 

[ialwayscapital]

Please try PIVOT.

Muz

I did, it does the SUM job, but doesnt copy (pull) other data like name & emp. code.

 

[Sam_D_Ben]

Pivot table is a data summarization tool that is used in the context of data processing. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns. It allows grouping by any data field.

And I dont think it can copy data as you said @ Ialwayscapital

Thanks Muz

 

[ialwayscapital]

ok guys, somehow i managed to pull unique data from the list, but i am stuck again. I just created a sample file for easy understanding.

if image doesnt load automatically, pls download it from here.

Download this sample EXCEL FILE HERE

Column A to E has data entered manually.

Formula used in H2 is =IFERROR(INDEX($B$2:$B$11,MATCH(0,COUNTIF($H$1:H1,$B$2:$B$11),0)),"") This one is array formula which must be entered by pressing Ctrl+Shift+Enter

So now, coloumn H has unique values from list B2:B11

What I need now,

In Column I, I2 must show area where highest duties performed. Like suresh did 10 duties in Guj & 15 duties in MH, then I2 must be MH

Column J should show the total number of duties done by that person, so I used formula in J2 =SUMIF(B2:B11,$H2,E2:E11)

Column K, should show data in the format shown in image, i.e. Unit-post-duties then enter charachter &CHAR(10)& or comma would do the job. THIS ONE IS TRICKY, I have no idea how to achieve this.

I am on Office 2019 Pro Plus Version 1909 (Build 12001.20000 Click-to-Run), I cant afford Office 365 (So no dynamic array and no unique function)

Thanks for all the support.

 

[Sam_D_Ben]

will this work.

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L
Tata SS 10,	Suresh	MH	Tata SS 10 Ambani SS 10
Tata SG 25,	Ramesh	Guj	Tata SG 25
Birla SS 24,	Paresh	MP	Birla SS 24 Infy SG 6
Birla SO 15,	Rahul	MP	Birla SO 15 Infy SO 10 XYZ SG 5
Ambani SG 11,	Vijay	UP	Ambani SG 11 XYZ SG 19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Area[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Unit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Post[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Duty[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Text Join[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Area[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Total Duties[/TD]
[TD="bgcolor: #D9E1F2, align: center"]Description (Unit-Post-Duties)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=305496]#305496[/URL] , align: center"]Description (Unit-Post-Duties)[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Guj[/TD]
[TD="align: center"]Suresh[/TD]
[TD="align: center"]Tata[/TD]
[TD="align: center"]SS[/TD]
[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"]25[/TD]
[TD="bgcolor: #D9E1F2"] Tata SS 10, Ambani SS 15, [/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Guj[/TD]
[TD="align: center"]Ramesh[/TD]
[TD="align: center"]Tata[/TD]
[TD="align: center"]SG[/TD]
[TD="align: center"]25[/TD]

[TD="align: right"][/TD]

[TD="align: right"]25[/TD]
[TD="bgcolor: #D9E1F2"] Tata SG 25, [/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]MP[/TD]
[TD="align: center"]Paresh[/TD]
[TD="align: center"]Birla[/TD]
[TD="align: center"]SS[/TD]
[TD="align: center"]24[/TD]

[TD="align: right"][/TD]

[TD="align: right"]30[/TD]
[TD="bgcolor: #D9E1F2"] Birla SS 24, Infy SG 6, [/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]MP[/TD]
[TD="align: center"]Rahul[/TD]
[TD="align: center"]Birla[/TD]
[TD="align: center"]SO[/TD]
[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: right"]30[/TD]
[TD="bgcolor: #D9E1F2"] Birla SO 15, Infy SO 10, XYZ SG 5, [/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]MH[/TD]
[TD="align: center"]Vijay[/TD]
[TD="align: center"]Ambani[/TD]
[TD="align: center"]SG[/TD]
[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: right"]30[/TD]
[TD="bgcolor: #D9E1F2"] Ambani SG 11, XYZ SG 19, [/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=C2&" "&D2&" "&E2&", "[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Please insert this code in your file.

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
    If rng = pValue Then
        xResult = xResult & " " & rng.Offset(0, pIndex - 1)
    End If
Next
MYVLOOKUP = xResult
End Function

 

[Sam_D_Ben]

will this work.

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Area[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]Post[/TD]
[TD="align: center"]Duty[/TD]
[TD="align: center"]Text Join[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Area[/TD]
[TD="align: center"]Total Duties[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Description (Unit-Post-Duties)[/TD]
[TD="align: center"]Description (Unit-Post-Duties)[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]

</tbody>

[/Code]

In K2:K11 Formula is =MYVLOOKUP(H2,$B$2:$B$11,5)