Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2007
    Posts
    14

    PivotTable formatting issue: trying to remove (blank) cells

    Hi,

    I'm working with MS Excel 2003 and MS Access 2003.
    I've got one database (db.mdb) and one excel file (consult.xls).

    In consult.xls, i've got an external data range, importing data from db.mdb. I've built a pivottable (in another worksheet) built on this external data range. It's working fine except from some formatting issue.

    The pivottable cells referring to NULL fields are displayed with the word "(blank)". I'd like these cells empty, that's to say, without "(blank)" or anything.

    I've tried to:
    -right click on my pivottable, select table options
    -check "for empty cells, show:" checkbox
    -erase all character in the "for empty cells, show:" inputbox.
    -refresh the pivottable.
    All this without any visible result. The "(blank)" fields remain...

    Any idea to remove those "(blank)"?

    deloffa5

  2. #2
    Join Date
    Nov 2007
    Posts
    15
    does the word "(blank)" show up in the data source file? can you make a pivot table with out the importing data from db.mdb and get good results? I know pivot table in access vs pivot table in excel have some differences.

    could you import the consult.xls into your db.mdb file and make a query then from that query make a pivot table inside access?

  3. #3
    Join Date
    Sep 2007
    Posts
    14
    The word "(blank)" does not appear in my data source.

    Let me show you an example:
    - I've got a table with three rows and three column
    - First row: NAME / AGE / POSITION
    - Second row: Peter / / Physician
    - Third row: John / 32 / Teacher

    If i build a pivottable based on this 3*3 array, with in the row fields "NAME", "AGE" and "POSITION", and the Pages fields clear, Column fields clear, Data itens clear. The pivot table will be:
    - First row: NAME / AGE / POSITION
    - Second row: Peter / (blank) / Physician
    - Third row: John / 32 / Teacher

    Datasource from an access database and datasource manually entered both give the same problem.

    deloffa5

  4. #4
    Join Date
    Nov 2007
    Posts
    15
    your getting something thing like this right? for the array?
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    When you pull the data, in the set up, does it give you the option about what to do with blanks? I can't check right now, but I remember something about that.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Aug 2009
    Posts
    2

    Remove (blank) from pivot tables

    Hey all,

    I was having the problem with the (blank) ruining my pivot tables and created a macro to take care of the issue. In a nutshell, my code will run through your pivot table and change the color of the cell containing the (blank) to white, making it invisible. Please make sure you run the script after every time you update your pivot table to ensure that cells that should be white are and cells that shouldn't aren't.

    Enjoy

    Sub Remove_Blank_From_PivotTable()

    ' The purpose of this macro is to remove the word (blank) from apprearing
    ' on any pivot table report. This code is completely generic and requires only
    ' the absolute minimum amount of intervention from the user

    ' Instructions: Copy and paste this code into the VBA editor from excel into a module attached
    ' to the spreadsheet that contains the pivot table you'd like to clean up
    ' On line 33 of the code there is a variable that is used to store the name of the spreadsheet
    ' that contains the pivot table. Simply change the name and run the macro

    ' Copyright: Benjamin Crudo, August 12, 2009
    ' Please send all feedback to benjamin.crudo@gmail.com
    ' This software is free to use and redistribute by anyone who wishes to do so

    ' If you require more help with excel or other programming needs
    ' at your place of business please contact me via email for my rates

    ' Copyright: Benjamin Crudo, August 12, 2009

    Dim counter As Integer ' setting up a counter to loop through the worksheet
    Dim lastRow As Integer, lastColumn As Integer ' creating variables to store the last
    ' rows and column that contain data on the worksheet to limit the loop

    Dim WorksheetWithPivot As String ' declaring a string to store
    ' the name of the sheet containing the pivot table
    Dim wb As Workbook

    Dim ws As Worksheet ' Declaring a Worksheet variable to use to
    ' access the worksheet with the pivot
    '_________________________________________________ __________________________________________________ _________________

    WorksheetWithPivot = "Master Pivot" ' enter the name of the worksheet that contains the pivot table here
    ' replace the words "Master Pivot" with the name fo your sheet with your pivot
    '_________________________________________________ __________________________________________________ _________________

    Set wb = ThisWorkbook ' setting the workbook object to this workbook
    ' note, if you wanted to run this script on another excel file
    ' you would change the workbook and worksheets paramters, or
    ' you could just copy and paste this code into another workbook VBA module

    Set ws = wb.Worksheets(WorksheetWithPivot) ' setting the Worksheet object

    i = 1
    j = 1

    lastRow = ws.UsedRange.Rows.Count ' locating the last row used on the sheet
    lastColumn = ws.UsedRange.Columns.Count ' locating the last column used on the sheet

    Do While i <= lastRow ' loop through each row that contains data
    Do While j <= lastColumn ' loop through all of the columns in each row
    If ws.Cells(i, j) = "(blank)" Then ' if there is a blank in the current cell make it's text white
    ws.Cells(i, j).Font.ColorIndex = 2 ' make the text white if the cell contains a (blank)
    Else
    ws.Cells(i, j).Font.ColorIndex = 1 ' Ensure that the text is Black if there is no (blank) in the cell
    End If ' end the if statement
    j = j + 1 ' increment the column
    Loop ' loop through the columns
    i = i + 1 ' increment the row
    j = 1 ' reset the column counter back to 1 (the beginning)
    Loop ' loop through the rows

    End Sub

  7. #7
    Join Date
    Feb 2011
    Posts
    7

    Converting Text to columns

    Hi There,

    Could someone help me with the below:

    This is pertaining to an MIS I prepare on a monthly basis.

    When I pull out the report from the system it gives me the below in one of the columns. where every three letter code is the city code and after that one letter is the class of booking. For e.g. DEL is the code for Delhi then q is the booking class then AMM is the code for Amman and so on..

    DELqAMMqTLVmAMMmDEL

    Currently what I am doing is going to converting text to columns. Selecting fixed width and converting the above to columns of every 3 letter code into a different columns. Then I decode the city code by using vlookup with my Airport & Airline Codes of the world (Excel file). Then finally concatenate them by inserting - in between. For e.g

    Delhi-Amman-Tel Aviv-Amman-Delhi.

    Is there any other way of doing this quicker, rather automatically?? I am not very well versed with Macros and VBA.

    Iam attaching a dummy with the data I use and the Airport & Airline Codes of the world.

  8. #8
    Join Date
    Aug 2009
    Posts
    2

    Use the sub string command to parse the data

    Let's say cell A1 = DELqAMMqTLVmAMMmDEL
    in Cell A2 type: =mid($A1, 1, 3)
    in Cell A3 type: =mid($A1, 3, 1)
    etc...

    this way you can easily create a table of new data...

    Here is how mid works: Mid( text, start_position, number_of_characters )

  9. #9
    Join Date
    Feb 2011
    Posts
    7
    Hi bcrudo, Thanks so much for the prompt response. This was indeed simple and quick to understand and useful as well. I appreciate it.

  10. #10
    Join Date
    Feb 2011
    Posts
    7
    Hi bcrudo,

    Need another help from you. I have two worksheets namely:

    1. Database Dom Volumes
    2. Database Dom-INT (This being the main sheet where I paste my data)

    In order to pick up values from the entire worksheet "Database Dom-INT" automatically to "Database Dom Volumes" if the condition matches that the Z2 cell in "Database Dom-INT" reads DOM, else leave a blank. For e.g. the formula is: =IF('Database Dom-INT'!$Z2="DOM", 'Database Dom-INT'!C2, "")

    I have copied the same formula to 399 rows so that it autopicks the values and I don't need to paste them. The formula is working well, however there is a small issue with it. I have a pivot table which summarizes the total spent and count the number of transactions (values in rows).

    Pivot Table
    Row Labels Sum of Nett Pay Count of Nett Pay
    Here it is blank Here it is Blank Here it is showing 399

    I am attaching the reference file, please go through and let me know if you have a way out.

    Kind regards

    Prayank
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2011
    Posts
    7
    Hi,

    If anyone can help me with my query posted yesterday.

    Regards

    Prayank

  12. #12
    Join Date
    Feb 2011
    Posts
    7

    Help with IF function with multiple scenarios

    Hi!

    Could someone help me with the IF function to use with multiple scenarios or which ever formula would work . For e.g.

    Airline Code Booking Classes
    9W
    BA
    CX
    S2
    KQ
    SJ

    In BA Following are Business Classes J,D,Z,I,C,R,K,W,E But remember in rest of the Airlines, R,K,W,E would be economy class.
    In rest of the airlines Following are Business Classes J,D,Z,I,C
    In all the Airlines (Incl BA) Following are First Classes A,F,P
    Rest whichever alphabet appears is economy class

    BA is British Airways

    All I want to do is that in column R which ever alphabet appears Column S gives me correct class.

    I am herewith attaching an excel sheet Book 2 in which column S is reflecting Economy Class which wrong as W in BA is Business Class.

    Regards

    Prayank
    +91 9910229289
    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
  •