# Thread: Excel Data Consolidation Thanks: 0 Likes: 0

1. ## Excel Data Consolidation

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)

2. ## Re: Excel Data Consolidation

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

3. ## Re: Excel Data Consolidation

You mean something like this.

Excel 2013/2016
ABCDEFGHIJKLMN
3Sunny Deol11225897336332831438633398346233803
5
6
7WORKKSHEET
10Sunny Deol11225897336332831438633398346233803

Sheet3

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

Muz

4. ## Re: Excel Data Consolidation

Thanks for the quick reply, but few questions rised.
C9 =SUMIF(\$A2:\$A4,\$A9,C2:C4)

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.

Muz

6. ## Re: Excel Data Consolidation

Originally Posted by Muzama Christo

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

7. ## Re: Excel Data Consolidation

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

8. ## Re: Excel Data Consolidation

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.

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.

9. ## Re: Excel Data Consolidation

will this work.

Excel 2013/2016
A B C D E F G H I J K L
1 Area Name Unit Post Duty Text Join Name Area Total Duties Description (Unit-Post-Duties) Description (Unit-Post-Duties)
2 Guj Suresh Tata SS 10 Tata SS 10, Suresh MH 25 Tata SS 10, Ambani SS 15, Tata SS 10
Ambani SS 10
3 Guj Ramesh Tata SG 25 Tata SG 25, Ramesh Guj 25 Tata SG 25, Tata SG 25
4 MP Paresh Birla SS 24 Birla SS 24, Paresh MP 30 Birla SS 24, Infy SG 6, Birla SS 24
Infy SG 6
5 MP Rahul Birla SO 15 Birla SO 15, Rahul MP 30 Birla SO 15, Infy SO 10, XYZ SG 5, Birla SO 15
Infy SO 10
XYZ SG 5
6 MH Vijay Ambani SG 11 Ambani SG 11, Vijay UP 30 Ambani SG 11, XYZ SG 19, Ambani SG 11
XYZ SG 19
Sheet1

Worksheet Formulas
Cell Formula
F2 =C2&" "&D2&" "&E2&", "

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```

10. ## Re: Excel Data Consolidation

Originally Posted by Sam_D_Ben
will this work.

Excel 2013/2016
A B C D E F G H I J K L
1 Area Name Unit Post Duty Text Join Name Area Total Duties Description (Unit-Post-Duties) Description (Unit-Post-Duties)

[/Code]
In K2:K11 Formula is =MYVLOOKUP(H2,\$B\$2:\$B\$11,5)