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]

Inserted given code by right clicking sheet - view code.

Formula in K2 gives error #NAME ?

What about I2 ?

 

[Sam_D_Ben]

Not view code. Its Developer tab -> Visual Basic -> Insert Module

If its F2, added an additional column.

 

[ialwayscapital]

Thanks for the help, Great Support.

Initially I did figure out a following formula for I2
=INDEX($A$2:$A$50,MATCH(MAX(IF($B$2:$B$50=$H2,$E$2:$E$50)),$E$2:$E$50,0))

But i realized that this formula gives wrong result if duplicate values found. So after lots of research and experiment, somehow I got a formula (given below) that works without any issues.

Formula for I2

=INDEX($A$2:$A$50,MATCH(MAX(IF($B$2:$B$50=$H2,$E$2:$E$50)),IF($B$2:$B$50=$H2,$E$2:$E$50),0))

This one works perfectly.

Thanks again

 

[ialwayscapital]

One last concern, Your VBA code doesnt have any range mentioned in it. I posted a sample (example) sheet for ease of understanding my query. My actual sheet has lots of column and rows. Will this VBA code work in actual sheet too ?

 

[Sam_D_Ben]

Yes, It will. You can modify the formula according to your requirement.

 

[ialwayscapital]

Code is fine but its not updating automatically as i change source data. Please make it auto-update.

 

[ialwayscapital]

What about TEXTJOIN function in K2, will that be feasible ?

 

[Sam_D_Ben]

Yes TEXTJOIN works. but my excel is not the updated version.

 

[ialwayscapital]

Update :
While experimenting, i found below formula for K2,

=TEXTJOIN(" | ",,IF(($B$2:$B$11=H2)*($F$2:$F$11<>"")=1,$F$2:$F$11,""))

its an array formula, so ctrl + shift + enter, I dont know if this one is perfect or not, am still experimenting.

 

[Sam_D_Ben]

Great, Progressive.