Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Pull fields from an Excel report and import them into another worksheet.

Hello,

I have a report of about 1000 employees - the attached sample report has 5 employees.  Is it possible to massage the document to something like the attached target report?  

Please note that the "Accural Balance and Projections" line appears multiple times in the report in a irregular sequence.  

I think this one is very tough.  Any advise or feedback is greatly appreciated.  

Many Thanks.
sample-report.xls
target-report.xls
0
nav2567
Asked:
nav2567
  • 2
  • 2
1 Solution
 
dlmilleCommented:
As requested.  Parse source sheet, create Target sheet and output.

code:
Option Explicit
Const strMatch = "Carryover Vacation,Lifetime PTO,Personal,Sick,Vacation"
Sub generateTargetReport()
Dim wkb As Workbook
Dim wksSrc As Worksheet
Dim wksTgt As Worksheet
Dim rng As Range
Dim r As Range
Dim lastRow As Long
Dim rOutput As Range
Dim bFound As Boolean

    Set wkb = ThisWorkbook
    Set wksSrc = wkb.ActiveSheet
    
    On Error Resume Next
    Set wksTgt = wkb.Worksheets("Target")
    If Err.Number <> 0 Then
        Set wksTgt = wkb.Worksheets.Add(after:=wkb.Worksheets(wkb.Worksheets.Count))
        wksTgt.Name = "Target"
    End If
    On Error GoTo 0
    
    wksTgt.Cells.Clear
    
    Set rOutput = wksTgt.Range("A2")
    
    lastRow = wksSrc.Range("B" & wksSrc.Rows.Count).End(xlUp).Row
    
    Set rng = wksSrc.Range("B1:B" & lastRow)
    
    For Each r In rng
        If r.Value = "Carryover" Then Stop
        If r.Value <> vbNullString Then
            If InStr(r.Value, ",") <> 0 Then 'found a person
                If bFound Then Set rOutput = rOutput.Offset(2, 0)
                rOutput.Resize(1, 2).Font.Bold = True
                rOutput.Value = r.Value
                rOutput.Offset(, 1).Value = r.Offset(0, 7).Value
                rOutput.Offset(, 15).Value = r.Offset(0, 7).Value
                rOutput.Offset(, 15).Font.Bold = True
                bFound = True
                Set rOutput = rOutput.Offset(1, 0)
            ElseIf bFound And InStr(strMatch, r.Value) <> 0 Then 'processing people and matches
                If r.Value = "Carryover Vacation" Then
                    rOutput.Value = "Carryover"
                Else
                    rOutput.Value = r.Value
                End If
                rOutput.Offset(, 1).Value = r.Offset(, 6).Value
                Set rOutput = rOutput.Offset(1, 0)
            End If
            
        End If
    Next r
End Sub

Open in new window


See attached demonstration workbook.

Dave
sample-report.xls
0
 
nav2567Author Commented:
Hi Dlmille, thank you very much.  From what I have seen, this is doable.  Unfortunately, I am not an advance user in Excel.  Would you just give me some high level of steps I need to do to customize my original spreadsheet, and I probably will have to do my own research to understand them.  

Thank you again.
0
 
nav2567Author Commented:
Thanks for your help.
0
 
dlmilleCommented:
As long as the report is in the exact same format, all you need to do is hit ALT-F11 and add a public module to your workbook (in the VBA editor, see VBAProject(your workbook name - right click and INSERT-Module) then copy/paste the code in.  To run the macro (which runs on the active sheet) just hit Tools->Macros->generateTargetReport()->Run, or DeveloperRibbon->Macros->generateTargetReport()->Run.

An alternative would be to copy/paste your report into the uploaded workbook that already has the macros in it, then run the macro as described, above.

Cheers,

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now