Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Need Data to Pull From Multiple Rows (excel 2007)

    I have a spreadsheet that contains multiple rows for each employee. The multiple rows contains all the different salaries that the individual had over a number of years. What I need to do is bring all the salaries over to a master spreadsheet but only as 1 row of data for each employee.

    Please see my example attached.

    Any help will be greatly appreciated. I am completely stuck.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need Data to Pull From Multiple Rows (excel 2007)

    1) The easiest way would be to add a columns of 1s and 2s in the multiple rows list (to indicate whether it is first or second) then create a pivot table with

    the row fields: SSNSIN, FirstName, LastName
    Column Field: New Col of 1/2
    Data: Sum of SalRat, sum of SalEffDt

    Hide the "totals" and drag the data from row format to column format.

    2) Another relatively simple way is to use a function.

    Add this UDF to a general module (this is part of a multi-lookup functions I created in <post:=395,235>post 395,235</post:>

    <pre>Option Explicit
    Function VLIndex(vValue, rngAll As Range, iCol As Integer, lIndex As Long)
    Dim x As Long
    Dim lCount As Long
    Dim vArray() As Variant
    Dim rng As Range
    On Error GoTo ErrHandler

    Set rng = Intersect(rngAll, rngAll.Columns(1))
    ReDim vArray(1 To rng.Rows.Count)
    lCount = 0
    For x = 1 To rng.Rows.Count
    If rng.Cells(x).Value = vValue Then
    lCount = lCount + 1
    vArray(lCount) = rng.Cells(x).Offset(0, iCol).Value
    End If
    Next x

    ReDim Preserve vArray(1 To lCount)
    If lCount = 0 Then
    VLIndex = CVErr(xlErrNA)
    ElseIf lIndex > lCount Then
    VLIndex = CVErr(xlErrNum)
    Else
    VLIndex = vArray(lIndex)
    End If
    ErrHandler:
    If Err.Number <> 0 Then VLIndex = CVErr(xlErrValue)
    End Function</pre>


    Use Advanced filter to extract a list of unique SSNs in Column A of the Master (adjust the ranges as needed)
    In B2:
    =VLOOKUP($A2,'Multiple rows'!$A$1:$C$5,COLUMN(),0)
    Copy B2 to C2

    In D2:
    =vlindex($A2,'Multiple rows'!$A$2:$A$5,COLUMN()-1,1)
    Copy D2 to E2

    In F2:
    =vlindex($A2,'Multiple rows'!$A$2:$A$5,COLUMN()-3,2)
    Copy F2 to G2

    Format as desired.

    Then Copy/autofill row2 down the columns...

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Data to Pull From Multiple Rows (excel 2007)

    Just as an excercise, the attached text file contains another macro that will populate the master sheet.
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Need Data to Pull From Multiple Rows (excel 2007)

    Thank you it worked!!! You just saved me 40 hours of work.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Need Data to Pull From Multiple Rows (excel 2007)

    Thank you Steve! This also worked.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Need Data to Pull From Multiple Rows (excel 2007)

    HansV developed an excellent macro for me several months ago. I now have an additonal column called "Basic Pay Reason" I would like to add the column to the macro. I took a stab at trying to update the macro but was only able to pull the last Basic Pay Reason for employees. What else do i need to update in the macro? I am attaching the VB code in case anyone can help.
    My data looks like this: I need each of the basic pay reasons to appear for each basic pay.
    Empid|Firstname|LastName|Salary|Effective Date| Basic Pay Reason|
    980085|Sabina|Acker|$90,000.00|5/21/2008|adj
    980085|Sabina|Acker|$86,000.00|5/21/2007|Merit
    980085|Sabina|Acker|$81,000.00|5/22/2006|NH

    Any help is appreciated
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Data to Pull From Multiple Rows (excel 2007)

    You didn't get the logic quite right. See the attached code.
    Attached Files Attached Files

Posting Permissions

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