Excel Data Consolidation
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

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

  1. #1
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Please help me out, Thanks

  2. #2
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

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

  3. #3
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    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



    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
    Sam_D_Ben

  4. #4
    New Member
    Join Date
    Feb 2010
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  5. #5
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Please try PIVOT.

    Muz
    Sam_D_Ben

  6. #6
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Quote Originally Posted by Muzama Christo View Post
    Please try PIVOT.

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

  7. #7
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Sam_D_Ben

  8. #8
    New Member
    Join Date
    Aug 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.



    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.

  9. #9
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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&", "



    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 by Sam_D_Ben; Aug 13th, 2019 at 09:52 AM.
    Sam_D_Ben

  10. #10
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Data Consolidation

    Quote Originally Posted by Sam_D_Ben View Post
    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)
    Sam_D_Ben

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •