If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > PivotTable formatting issue: trying to remove (blank) cells

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2007
Posts: 15
your getting something thing like this right? for the array?
Attached Files
File Type: doc test1.doc (29.5 KB, 481 views)
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2011
Posts: 6
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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 )
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Feb 2011
Posts: 6
Hi bcrudo, Thanks so much for the prompt response. This was indeed simple and quick to understand and useful as well. I appreciate it.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Feb 2011
Posts: 6
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
File Type: zip Example.zip (57.6 KB, 35 views)
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Feb 2011
Posts: 6
Hi,

If anyone can help me with my query posted yesterday.

Regards

Prayank
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Feb 2011
Posts: 6
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
File Type: zip Book2.zip (5.1 KB, 12 views)
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On