Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Pivot tables - split cell in column headings

Hi,

I have an a table in sheet 1 which contains a list of pipelines. The column headings are as follows (simplified)

PipeNumber, PipeDescription, Boundary

The boundary field can contain a number of values e.g. 33, 8, 40. This tells the user which boundaries the pipe crosses.

I want to create a pivot table which sumarizes which boundaries each pipe crosses. The problem is the boundary cell could contain a single number (1 boundary) or numerous numbers (numerous boundaries). Is there anyway that each number in the boundaries cell could be recognised as a column heading?

It is hard to explain but hopefully you get what I mean.

Any help would be much appreciated.

Thanks
Tim


Post your answer or comment

comments powered by Disqus
I came across Ozgrid's vba code to create a pivot table. I have 19 columns of data so I am trying to modify this code:


	VB:
	
 MakeTable() 
    Dim Pt As PivotTable 
    Dim strField As String 
     
     'Pass heading to a String variable
    strField = Selection.Cells(1, 19).Text 
     
     'Name the list range
    Range(Selection, Selection.End(xlDown)).Name = "Items" 
     
     'Create the Pivot Table based off our named list range.
     'TableDestination:="" will force it onto a new sheet
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ 
    SourceData:="=Items").CreatePivotTable TableDestination:="", _ 
    TableName:="ItemList" 
     
     'Set a Pivot Table variable to our new Pivot Table
    Set Pt = ActiveSheet.PivotTables("ItemList") 
     
     'Place the Pivot Table to start from A3 on the new sheet
    ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1) 
     
     'Move the list heading to the Row Field
    Pt.AddFields RowFields:=strField 
     'Move the list heading to the Data Field
    Pt.PivotFields(strField).Orientation = xlDataField 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas out there? I really need some help with this.

Kurt

I have i have pivot table set up in report format with various fields from a CRM database.
I want to clear blank data in the cells when the user click in the pivot table cell to open a certain cell to view the data that is whtiin the pivot table. it creates a new sheet with all the data in but i only want it show the data the user wants to see as it shows me so many blank data fields!
here is screenshot of the pivot table:

new sheet that is created when the user click the pivit table cell they want to see:

i have tried to use a macro to clear the cells in the new sheet the pivot tables creates but the data range can vary accoridng to the users selection of data from the pivot table is there any way to sort this out ?
here is the code for the macro i have used:

	VB:
	
 ClearCells() 
   
Sheets(1).Range("A1-U1""A2-U2""A3-U3""A4-U4""A5-U5""A6-U6""A7-U7""A8-U8""A9-U9""A11-U11""A12-U12""A13-U13""A14-U14""A15-U15""A17-U17""A18-U18").Clear

End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
could i use vba to solve this ? or is a macro best for this?

question is it possible to create a macro that can rid of these range of fields ? as the range will differ according to what cell the user opens in the pivot table as the data us pulled and refreshed from the datasource.

In Excel 2000 (I can't recall it in XL97), when you create a pivot table the row and column headings automatically appear with drop-down boxes to allow selection to hide/display the individual items.

Is there any way to turn off this functionality so that the drop-downs don't appear?

Regards,
Batman.

Hi, Is it possible to add a column for % calculations when the % column is
not in original data? To clarify, my original data is as follows:

Produt Sales Returns Date
A 5 June
B 6 June
A 1 July
A 1 September
B 1 November

When I run the pivot table, one of the columns I'm then looking to get is a
total % of returns over sales , but I cant see how to include in a Pivot
table. I can add it outside of the table, but that has problems when the
table is refreshed etc, as it frequently needs to be.

Thanks for any help

I have a Pivot Table which is the "parent" to several other Pivot Tables. In the source data for the pivot table, 12 of the column heading contain the Month and Year to effectively represent a rolling 12 month cycle (Example Sept 2007 through August 2008). Each month the data range feeding the table is updated to a new/current rolling 12 months - I do not move any of the data, I ONLY change the column headings. Unfortunately in doing the update, the column heading within the Pivot Table do NOT reflect the update in the source data range. Due to this I have to rebuild the Pivot Tabe from scratch each time and then update the "children" Pivot's.

Remember the actual locations of the data did NOT change, just the column headings. Any suggestions on how to get these changes to propogate into the piviot table?

Hi all,

I have tried searching for a solution to this on Google, and tried to put together/mash-up different solutions to similar problems to try and solve mine, but no luck as I'm not that advance with macro. I hope someone can help me out.

I am running a marketing campaign with advertising text that contains starting from rates in different currencies. Below is what my workbook is made up of.

Workbook contains 2 sheets

Sheet1Sheet name: Ratescontains a table (name= Table3) of different currency conversions columns represent different currencies. Each column heading is a 3 letter currency code (e.g. USD or GBP) rows represent different products or product locations (e.g. London or Dubai)
Sheet2Sheet name: DataSheetcontains a list of different ad textcolumn A contains the name of the target (e.g. Hotels London - Target USA)column B contains a string (not relevant here)column C contains the 1st line of advertising text (e.g. London Summer Suite Offer) which can sometimes contain a starting from rate (e.g. London Hotels from USD1234)column D contains the 2nd line of advertising text which can also contain a starting from rate (e.g. Book now for only USD1234)column E contains the 3rd line of advertising text which can also contain a starting from rate (e.g. Book now for only USD1234)
The thing about this sheet is that the rates change often and need updated regularly, and having more than 3000 rows of different ads targeting different markets makes it very time consuming to do manually.

What I would like to do is to have a set of code do the following: check if any of the cells in the sheet "DataSheet" contain a certain string value (perhaps a pre-defined value of based on another cells value, lets say USD1234)
if the above is true, i want the macro to check the value of the cell in column A on the same row of the above cell and correspond it to a currency. E.g. if cell in column A contains string USA, currency = USD. Or if cell in column A contains UK, currency = GBP
i would then want to replace the string "USD1234" with the lowest value in the column with the heading USD which i have updated in the sheet "Rates"
I am not sure if I have manage to make any sense but i sure hope someone will be able to help me. Please let me know if there's anything i can clarify or if my above request is just way too complicated.

Many thanks for your time/help in advance,

Amro

Hi,

I was wondering if there is a possible way to create a pivot table, using two different columns of data. Some how referencing in my case, B3 and G3 (coupling the two). I want these cells to be in it: B3:B4200 and G3:G4200. Where each line references the B and G together? So B3 and G3, B4 and G4 etc down to B4200 and G4200?

Any ideas?

Thanks in advance!

Macro Gurus,

I am a complete Novice and ask for assistance. I am hoping what I ask is fairly easy for the pros here to tackle.

Running Excel 2010, but would like any macro to be compatible with previous versions if possible.

I need a Macro that will check the contents of each cell in Column A (Approver ID) (starting at A2 because of column headings). If the cell has data (anything but blank/empty), then populate the corresponding cell in Column O (Upld Date) with the current date (ex: 5/8/2012).

Once a blank/empty cell is detected in Column A, end the routine. If it is also possible to have the macro auto save and close the Excel sheet once the end of file is reached, that would be great.

Example: Cell A2 is not empty - Populate date in O2, check next cell
Cell A3 is not empty - Populate date in O3, etc
Cell A287 is empty - End routine (auto save and close Excel sheet if possible)

The columns above (A and O) are the actual columns I am dealing with in the Excel sheet.

Thank you in advance to all who help.

I am running this macro or some variation of it, depending on the column I need the time entered into:

Sub MacroD()
Dim LR As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
ActiveSheet.Unprotect
Range("D18:D" & LR).Value = Now
ActiveCell.Offset(1, 0).Select
UserForm1.ListBox1.Text = "Time"
UserForm1.ListBox1.SetFocus
' UserForm1.Show
End Sub

When I need to run the same macro again (say I've run Macro D once, and now I need to run it again to get the next time), the forumla overrides the previous timestamp. I need the macro to find the next empty cell in column D and enter the timestamp there.

Can anyone help? Thanks!!

Sheet1

*ABCDEFGHIJKLMN15Major EventMajor Event Clock TimePUSH#########SPREAD########OUT FWD########REV########OUT REV########Delay########1617Start10:55:09*10:55:27 *10:56:24 *10:56:26 *10:56:19 *10:56:28 ####10:55:59 18W-U10:55:180.40010:56:23 0.01710:56:24 0.03310:56:26 0.01710:56:27 0.01710:56:28 0.03310:56:30 19MTT10:55:26######*0.000*0.000*0.000*0.000*0.000*20**0.000*0.000*0.000*0.000*0.000*0.000*21****0.000*0.000*0.000*0.000*0.000*
> Excel Jeanie HTML 4

If I have a pivot table that has a column that counts the number of accounts, and I add a second instance of that column and change the Field Settings to show data as % of Total, I get 2 columns one with the count, and another with the percentage for each item in my column header (let's say months of the year to keep it simple). I also have the Total Column to the right which displays 2 columns also: total count and then a column with % of Total, just like the data columns.

Is there a way to keep the 2 columns displaying in the Total area, but only display one total with the counts for the data area?

Example (numbers aren't real):

Jan Feb Mar Total
# Accts % # Accts % # Accts % # Accts %
North 5 .02 8 .04 6 .03 19 .40
South 5 .02 8 .04 6 .03 19 .40
East 5 .02 8 .04 6 .03 19 .40
West 5 .02 8 .04 6 .03 19 .40
Total 20 .07 32 .16 24 .11 76 .85

Get it to look like this:

Jan Feb Mar Total
# Accts # Accts # Accts # Accts %
North 5 8 6 19 .40
South 5 8 6 19 .40
East 5 8 6 19 .40
West 5 8 6 19 .40
Total 20 32 24 76 .85

Is it possible? What do I need to do?

Thanks in advance for any help.

***Sorry, the formatting changed when the post was submitted, but hopefully you can get the idea.

Hi all

I have a pivot table, and every time I refresh the data, it automatically adjusts the column widths to allow for the longest text strings in the table.

I know that one way round this is to change the source data, so that the strings are shorter, but is there another way ?

Ideally I'd like to be able to manually set the width of the columns that the pivot table sits in, and then refreshing the pivot table would leave these column widths unchanged, no matter what data was returned by the pivot table.

Thanks in advance !

I have a data table with a column of data called "durations" with value
from 1 to 14, but some are missing (there's no 6 or 13). On my pivot
table I want the column headings to be the numbers 1 to 14 without the
missing figures. I know I could add these as dummies to the data
table, but can I somehow force them to appear on the pivot table
directly?

I created a pivot table and in the data area I have 4 months of sales date
Jan, Mar, Jun, and Dec.. When I pull down the down arrow for this data I see
all 4 months and I see box for each month and a box for all. When I look at
Jan, and Mar and not all I loose the other months. What is wrong with the
table?

"Debra Dalgleish" wrote:

> AFAIK, the Data source option is only enabled for OLAP cubes. For pivot
> tables built from Excel data, the items are listed in Ascending order
> when the pivot table is created. If you set sort to Manual, you can drag
> the items to any position in the list.
>
> You could also create a custom list, and base the sort order on that.
>
> In a cell on a blank worksheet, type your list, then select it
> Choose Tools>Options
> Select the Custom Lists tab
> Click the Import button, click OK
>
> When you create a new pivot table, items should be in the order of the
> custom list.
>
> To sort the items in an existing pivot table, select the field
> button, and choose Data>Sort
> Click the Options button, and choose your custom list.
>
>
> Mike wrote:
> > Hi.. thanks for the response. The ADVANCED option under
> > FIELD SETTINGS gives AUTOSORT options of:
> >
> > Manual
> > Ascending
> > Descending
> > Data Source Order
> >
> > It's set at Manual, but the data is in Ascending order
> > even though the data in the original sheet is not sorted.
> > And, the "data source order" option is greyed out.
> >
> > I still cannot get the data in the same order as the
> > original sheet. Any reason why the Data Source Order
> > would be greyed out?
> >
> > Thanks again. Mike
> >
> >>-----Original Message-----
> >>If you right-click on a field button, choose Field
> >
> > Settings, and click
> >
> >>Advanced, that field should appear in the field list for
> >
> > sort order.
> >
> >>Mike wrote:
> >>
> >>>I cannot get some data fields in pivot tables to
> >>
> > appear
> >
> >>>in the "data source order". This option under advanced
> >>
> > is
> >
> >>>greyed out for some reason. So everything gets sorted.
> >>>Any suggestions?
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>.
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

I am trying to come up with a formula that will accomplish the following.....

I have 2 columns that compare the current year weekly data to prior year's
weekly data. I need a formula that will sum Column D to same week in Column
B. Each week, the next row will be updated in Column B and the cells summed
in Column D need to change to include the next week's data too. So for the
table below, I would come up with the figure of 350 and the next week when B4
has data input, the total for Column D would be 500. I am trying to compare
total inventory each week. The cells in Column B with no data are blank.
Any ideas?
A B C
D
1 week ending 1-6-07 150 wek ending 1-7-06 200
2 week ending 1-13-07 100 week ending 1-14-06 100
3 week ending 1-20-07 75 week ending 1-21-06 50
4 week ending 1-27-07 week ending 1-28-06 150
5 week ending 2-3-07 week ending 2-4-06 225
6
--
Sharon

Hello,

I am attaching an excel sheet, where i want all the blank cells in columns A, B, C or D to be deleted.

Column A , B is one table and Column C, D is one table
Ex., if in Row 2, the cell in Column B is empty, I want the Row 2 to be hidden/deleted for Column A

If if in Row 2, the cell in Column E is empty, hide or delete Row 2 contents in Column D

In Excel 2007, I have noticed that pivot tables may have a column heading of values or data. What determines which is displayed and how can it be changed?

I have a template where I paste the pivot table to calculate results, but the results don't display properly when the column heading appears as values.

Thanks for your help.

I'm trying to get a VBA macro put together that would delete all the rows, throughout every sheet, and shift up wherever the cell in column A = "Delete Row" in a workbook I've made. In the below copy of the workbook you can see that a lot of the information from all the tables is linked to the topmost table on the 'Mix Detail Sheet'.

If the the Cell in column A is blank on that first table, its corresponding cells in tables throughout the workbook displays the words "Delete Row". I need the macro to delete the rows and shift everything up where this occurs.

Ideally I'd also like the macro to then delete the rows in that first table where the column A cells are blank.

Let me know if I need to be more clear of if there are any questions. And thank you to anyone who can find the time to help me out. I've tried to adapt many of the other 'Delete Row' macros on this site to my situation, but I have not had very much success and I thought it was time to ask.

Hi All,

I have a pivot table in excel 2007.

I'd like to use copy-paste special-values over the top of it to get rid of the pivot table and data link, but keep the formatting, before sending the workbook in an email.

When I do this though, excel strips out all of the pivot tables formatting like bolded column headings and colors, and lines delineating the sections of the table.

Is there a way to turn a pivot table into just values in a workbook but keep the formatting provided by the pivot table?

Thanks,

Dave

Hey all,

I'm trying to run down through a very long/variable Column Y and add a SUM formula of the cells in Columns J:X to all blank cells in which the row also has an account number in Column A. (I've attached a truncated example of the spreadsheet). Basically, if there is a blank spot in the totals column that also has an account in column A, I need to sum a bunch of the values in between.

Here's a code I've written so far. I tried to add an active cell formula to this to allow the macro to work on any column but had even worse results. Any help would be greatly appreciated.


	VB:
	
 FillEmpty() 
    Dim rCell, rColumn As Range 
    Dim lLastRow As Long 
     
    lLastRow = Range("Y65536").End(xlUp).Offset(0, 0).Row 
     
    Set rColumn = Range("Y1:Y" & lLastRow) 
     
    For Each rCell In rColumn 
        If IsEmpty(rCell) Then 
            If rCell.Offset(0, -25).Value  Empty Then 
                rCell.Formula = "=SUM(J:X,rCell.row)" 
            Else 
                 
            End If 
        Else 
             
        End If 
    Next rCell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you,

Marcus

Hello all,

I have looked over the forums to try and assist with the issue that I am coming across, but have not found the answer (maybe I'm not understanding correctly). In my workbook, I have sheet 4 that has existing date in it that is constantly updated via a formula to pull from another sheet in column A, the macro that I have currently opens a file and copies certain data and pastes into column B. This macro will also run a data compare to compare column B to column A and pull out any new data and insert it into column D.

What I would like to have happen is that once the macro puts the information into column D it copies the data in column D starting with cell D2 and pastes it into the first available cell in column D on Sheet 1. Below is the current code that I have:


	VB:
	
, x 
a = Range("a1").CurrentRegion.Resize(, 2).Value 
With CreateObject("Scripting.Dictionary") 
    .CompareMode = vbTextCompare 
    For i = 1 To UBound(a, 1) 
        If (Not IsEmpty(a(i, 1))) * (Not .exists(a(i, 1))) Then .Add a(i, 1), Nothing 
    Next 
    Redim b(1 To UBound(a, 1), 1 To 1) 
    For i = 1 To UBound(a, 1) 
        If Not IsEmpty(a(i, 2)) Then 
            If Not .exists(a(i, 2)) Then 
                n = n + 1: b(n, 1) = a(i, 2) 
            Else 
                .Remove a(i, 2) 
            End If 
        End If 
    Next 
    x = .keys 
End With 
With Range("d1") 
    .CurrentRegion.ClearContents 
    .Resize(, 2).Value = [{"Not in main list", ""}] 
    With .Offset(1) 
        If n > 0 Then .Resize(n).Value = b 
    End With 
    On Error Resume Next 
    .Offset(1, 1).Resize(.Count).Value = Application.Transpose(x) 
End With 
 'Copy results from comparison to Enhanced Integration sheet
Range("D2:D9999").Select 
Selection.Copy 
Sheets("Sheet1").Select 
Range("D26").Select 
ActiveSheet.Paste 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As you can see with the code, I had to specify a cell to past the data into. I would like to have instead look for the first available cell in column D on Sheet1 and paste.

Any help with this would be greatly appreciated

is there a code i can write that will go to the first blank cell in column A after say A5

Many thanks des

Hi

MATCH(3, A:A, 0) will return the row number with the first number '3' in column A.

I need to search for the first empty cell in column A.

Is this possible?

Thanks

Adam

I am using the first part of the code to select the last cell in a column.
I wish to enter that cell into a InputBox, rather that the default "A3" shown below. How do I modify the second part of the code?


	VB:
	
 
Sheets("Prices").Select 
 
 'Find last cell in column
Range("A65536").End(xlUp).Select 
ActiveCell.Offset(1, 0).Select 
Set wSheetStart = ActiveSheet 
 
 'Paste clipboard contents into last cell
strName = InputBox(Prompt:="Type in column A cell to paste component into", _ 
Title:="Price List Paste", Default:="A3") 
Range(strName).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 
Application.CutCopyMode = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks, Sandy

I have a multi-sheet Excel workbook and I need help please to develop a macro based control that, with a single click of a Button in a sheet named REPORT, will take me directly to the last empty cell in column A of a sheet named REGISTER in the same workbook. Any ideas please?


No luck finding an answer? You could always try Google.