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

1. ## 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.

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

2. ## 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. ## 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.

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

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

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

Thank you Steve! This also worked.

6. ## 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|\$86,000.00|5/21/2007|Merit
980085|Sabina|Acker|\$81,000.00|5/22/2006|NH

Any help is appreciated

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

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

#### Posting Permissions

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