Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling blanks (Excel 2003)

    For my pivot table report to work, I need to fill Columns B and C on the attached sample with the Project Name and Manager for the Job in Column A. I thought about the GOTO Special Blanks with the formula = above cell, but that would not be correct if the project number changed. Also, some project numbers do not yet have a Project Manager or Description in the database, so those should remain blank. What Iím trying to do is get a macro that would save the clerical work involved in the preparation of the data. Assume Col A is sorted so all Jobs are grouped. The goal is to allow the pt to group the invoice and other information columns to the right by Job and PM. Blank Project or Mgr fields in the pt force each job to have two lines when the PT by Job report is created-one for the line with the project mgr and project row filled in and one for the ĎblankĒ project manager and project fields. Thatís what Iím trying to fix. TYIA
    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: Filling blanks (Excel 2003)

    Why not create a lookup table for the job, Project and Mgr? Then in Columns B and C lookup the job from Col A to fill in the numbers. Then create the pivot table based on this data.

    Steve
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling blanks (Excel 2003)

    The report comes out from Oracle in txt format that we use a text parsing program to create the type of list you see. So it's a new workbook each time. I am trying to avoid having to go through the lookup formula setup every time we get this thing out, especially when the data is sitting in the cell above, it's not going to change, and I'd rather skip the whole lookup/range value set of steps if at all possible. If the template workbook has a macro to do the job in seconds, that's much more efficient. Thanks for your thoughts.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Filling blanks (Excel 2003)

    Not sure what you want. Can you send an example of PT with the problem. The reason I ask is because PT has a whole bunch of tools that can be customized which may eliminate your problem
    One such tool is the field subtotal option which can be turned on and off. If turned off it may remedy your problem of PM and Blanks. Another tool is Grouping that may also remedy the problem.
    In other words, by customizing the report perhaps you could skip the issue of filling in the blanks and just show the data with some having data and other blanks.

    Regards,

    Tom Duthie

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling blanks (Excel 2003)

    I've created the situation in the sample workbook. For the report to have the appropriate information, it needs the Project name and manager, so I can't get rid of those fields.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Filling blanks (Excel 2003)

    Thanks

    Per the attached you no longer have the blanks.
    What I did was select the drop down box next to Project and instead of the default of showing all the box for the blank selection was unchecked. The PT then revised to eliminate this data and appears as you see it.



    Regards,

    Tom Duthie
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling blanks (Excel 2003)

    Here's where this is heading: this sample goes a little farther into the actual report. The invoices with blank Description and Manager need to be included in report totals...simply hiding the blanks throws off the totals, of course.
    Attached Files Attached Files

  8. #8
    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: Filling blanks (Excel 2003)

    It seems to me the root of the problem is the oracle report. Why not fix the oracle report so that it does not leave those rows "blank"? It is already putting a null string in cols B and C so they are not really blank(The output for col D is blank so the oracle program does differentiate between what is in B/C and what is in D. If instead of putting a null string in, it filled in the data, it would save a step.

    But if you want a macro, you can try this

    Steve

    <pre>Option Explicit
    Sub FillInProjMgr()
    Dim lRow As Long
    Dim lLastRow As Long
    Dim rCell As Range
    Dim rMatch As Range
    Dim wks As Worksheet
    Set wks = ActiveSheet
    With wks
    lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rMatch = .Range(.Cells(2, 1), .Cells(lLastRow, 2))
    End With
    For lRow = 2 To lLastRow
    Set rCell = wks.Cells(lRow, 2)
    With rCell
    If Trim(.Value) = "" Then
    If Trim(Application.WorksheetFunction. _
    VLookup(.Offset(0, -1), rMatch, 2, 0)) <> "" Then
    .Value = .Offset(-1, 0).Value
    .Offset(0, 1).Value = .Offset(-1, 1).Value
    End If
    End If
    End With
    Next
    Set wks = Nothing
    Set rCell = Nothing
    Set rMatch = Nothing
    End Sub</pre>


  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling blanks (Excel 2003)

    That's great! Thanks for your hel.

Posting Permissions

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