Merging multiple records in excel

[xplo5iv]

I have an excel source with the following headings

Staff Number
Name
Allowance
Amount

Each employee appears one or more times, as they can have more than one allowance. The list is sorted by staff number, so the records are together for each staff member.

I need to create a document per person which includes Staff Number & Name once, followed by a list of allowances and amounts e.g.

Staff Number: - 123456
Name: - Fred

Allowances Amounts
Basic Pay 25750
Management 1650
Shift 21750

Skip helpfully told me the following

I'd do it all in Excel. You'll have to record and modify a simple macro.

You will need a unique list of your employees from your source data. This can be done using the Advanced filter - UNIQUE values, no criteria. Copy this unique list to a new, separate sheet.

Use the AutoFilter in your source data, to select an employee. Turn on your macro recorder and record selecting the employee.

Post back with your recorded code in forum707: VBA Visual Basic for Applications (Microsoft) for help cusomizing.

So here is the code in question...

Sub Macro1()

    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="John"
    
End Sub

Thanks for the help


Jonathan

 

[SkipVought]

Hi,

The NEW sheet (I call it Sheet2) contains your unique list of employees, starting in A2

Sub Macro1()
    Dim r As Range
    
    With Sheets("Sheet2")   'the sheet with your unique employee list in column A
        For Each r In .Range(.[A1], .[A1].End(xlDown))
            With Sheets("Sheet1")
                If Not .AutoFilterMode Then
                    .Range("A1").AutoFilter
                End If
                .[A1].AutoFilter _
                    Field:=2, _
                    Criteria1:=r.Value[b]
                'WHAT do you want to do, once the employee is selected?[/b]
            End With
        Next
    End With

End Sub

I ask the question, what do you want to do, once each employee is selected?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi xplo5iv,

You don't need vba for this. You can do it all with a Director/Catalogue merge, as I've said in my reply to your other post (

http://www.tek-tips.com/viewthread.cfm?qid=1571370&page=1).

Cheers
[MS MVP - Word]