Excel Data Consolidation

ialwayscapital

New Member
Joined
Aug 18, 2009
Messages
18
I am working on a salary sheet and stuck on this situation.
Sheet has a to z columns with these headings.

ScTlbSk5B8pcgtJEqy_Dlpy4HQaC2KqTFXr_QAHCwZQ2Sa4BCjyPhSi6aXq_cdUwplUqXfLYyqlcFA=w1920-h925


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 :)
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
You mean something like this.


Excel 2013/2016
ABCDEFGHIJKLMN
1Emp NameEmp CodeDutyWagesPFESIPTGross SalaryAdvanceUniformAdd MoneyTake HomeBlank ChequeCash In hand
2PooJa Yadev123452597506503001608650400046403820
3Sunny Deol11225897336332831438633398346233803
4PooJa Yadev123454097656653151758665401546553835
5
6
7WORKKSHEET
8Emp NameEmp CodeDutyWagesPFESIPTGross SalaryAdvanceUniformAdd MoneyTake HomeBlank ChequeCash In hand
9PooJa Yadev123456519515131561533517315801592957655
10Sunny Deol11225897336332831438633398346233803
Sheet3
Cell Formulas
RangeFormula
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.

uc


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.
 
Upvote 0
will this work.

Excel 2013/2016
ABCDEFGHIJKL
Tata SS 10, SureshMHTata SS 10
Ambani SS 10
Tata SG 25, RameshGujTata SG 25
Birla SS 24, PareshMPBirla SS 24
Infy SG 6
Birla SO 15, RahulMPBirla SO 15
Infy SO 10
XYZ SG 5
Ambani SG 11, VijayUPAmbani 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.

Code:
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
 
Last edited:
Upvote 0
will this work.

Excel 2013/2016
ABCDEFGHIJKL

<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)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top