Create PivotTable With Two Columns Of Data For The Same Column Header?
Dec 24, 2012
Can I create a PivotTable with two columns of data for the same Column header?
I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.
View 2 Replies
ADVERTISEMENT
Apr 11, 2014
I am a macro newbie and I think this is beyond me.
I've been trying all morning with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.
So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in cell A4 or B4 or C4 etc. with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database.
There must be a way to do this that is easier than searching the internet all afternoon again
View 3 Replies
View Related
Jun 18, 2008
I have a worksheet of data collected from on online list of names and addresses all in one column. I'd like to pivot the data so I can sort it, etc. Unfortunately, the entries are not the same length (meaning some have five rows some have six or seven, etc. I have created an only file so everyone can see the issue.
a href=[url]
View 4 Replies
View Related
Mar 12, 2014
I have 2 columns full of information feeding from 2 different sources on our internet database
I would like to collect this information into 1 column to create a drop down list
As these are expanding tables feeding from the internet I don't think copy and paste into one column will work (I'm not sure about this but I would prefer a formula to avoid any issues).
View 4 Replies
View Related
Mar 3, 2012
Is this possible? If
A B C
0 0 0
0 0 0
1 4 5
2 4 8
0 0 0
0 0 0
0 0 0
2 6 9
8 9 3
3 5 6
0 0 0
In the raw data, the 0s split up the data into different steps. I am trying to create a function that selects the data from the ABC column and puts it into new columns for each step. I.e:
New Columns with:
1 4 5
2 4 8
and
2 6 9
8 9 3
3 5 6
In the raw data files, there will be 10 steps, with a varying length of data.
View 2 Replies
View Related
May 15, 2014
So I want to add some VBA Code that deletes specified columns and then insert Bolded Column headers to the remaining columns. The columns I want deleted are D,F,I,J,K. For simplicity purposes, the column headers would be A,B,C,D,E,F,G.
I tried using Columns("D,F,I,J,K").delete but I kept on getting 13 error.
[Code] ......
Attached File : Test VBA File.xlsx
View 3 Replies
View Related
Sep 24, 2009
I have been creating pivot tables (from the active workbook) with ADO for a while and its working great.
I just tried to switch to DAO and adapted my code accordingly but the new code fail at:
Code: ....
View 9 Replies
View Related
Jun 28, 2013
I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).
Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.
Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer
[code]....
View 3 Replies
View Related
Apr 3, 2014
I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.
i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2
(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4
test.bmp
View 2 Replies
View Related
Sep 18, 2012
I need a formula to return column header if there is data in the column, any data.But I also need to return the second, third, fourth header with data too
In column A row 2, I have =IF(M8"",$M$1,IF(N8"",$N$1,IF(O8"",$O$1,IF(P8"",$P$1,IF(Q8"",$Q$1,IF(R8"",$R$1,IF(S8"",$S$1,IF(T8"",$T$1,""))))))))
This returns the row 1 header for the first instance of data in row 2 columns M thru T But in B2, I need the second header with data in the column.
I need the results in yellow (1st, 2nd, 3rd, 4th) from the array to it's left
1St match2nd 3rd 4thColumn MColumn NColumn OColumn PColumn QColumn R
Column MColumn PColumn QData data2Data3
Column NColumn OColumn PColumn R Data Data2 data3 data4
Would use match/index but can't increment it to the second or 3rd match
View 2 Replies
View Related
Apr 20, 2012
I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.
I'm using Excel 2007
The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)
The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .
It's just six columns of data. The short list is in AY from AY2 : AY15
The long list is in BB from BB2:BB505
The column header "AL" is in AY1 and NL is in AZ1
View 2 Replies
View Related
Jun 19, 2007
I have a data worksheet, which contains about 20,000 lines of journal entry info. There is a separate column for debit and credit, not one "amount" column. On another worksheet, I am aggregating the data to review the impact for this series of journal entries over a time period (12 months). Thus, my pivottable contains "Accounts" in the "ROW" and "Period" in the "COLUMN" sections of the PivotTable Layout. The "Debit" and "Credit" fields are then summed in the "DATA" portion of the PivotTable.
My issue revolves around getting the actual impact for each account for each time period. Essentially, I need an extra column inserted between each time period that sums the debits and credits for that period. Do I need a macro for this (not any experience with macros)? My file is way to big to share, so let me know if a pared-down sample would work.
View 5 Replies
View Related
Jul 17, 2007
Hide values in Column layout on pivot table i only want to show th total value as its based on quarter figures not per month and this way it looks like Quarterly totals are broken down per month and has to have 200 a mont whenits in fact 200 for the complete quarter, how can i had the 200 for each of the month and just have the total per country? This is what my pivot table looks like
county Month Quarterly totals
UK June 200
july 200
August 200
Uk Total 200
View 3 Replies
View Related
May 21, 2014
I would like to somehow select and print the used columns in my pivot table.
I currently have a code that prints TableRange2 but this also includes blank pivot tables columns which are not in use.
View 2 Replies
View Related
Aug 3, 2007
The code I'm using that results in the following error message: "Run-Time Error '1004':
The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field." Here's the code, I've seperated it into each sub hoping that will make it easier to read:
Option Explicit
Sub main_prog()
Call td_metrics_import
Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
Call create_graph
End Sub...............
View 8 Replies
View Related
May 13, 2014
I have a list of S/N with Pass/Fail result next to it. A specific S/N can appear several times in the list:
SN...Result
-------------
111...FAIL
111...FAIL
111...FAIL
111...PASS
222...PASS
333...FAIL
333...FAIL
When creating a pivot table of SN vs. Result (count) I get:
****|FAIL...PASS
-------------------
111..|...3.....1
222..|..........1
333..|...2
I want to get the FAIL count of SN in which PASS > 0 (First two rows)
BUT I can not figure out how I can filter based on the values of the pivot table itself (rather than the values of the original list). When I try to use 'Result' field as report filter, I can not get it as a Column label (and vice-versa). In this case I get:
I get:
****|PASS
------------
111 | ...1
222 | ...1
Desired:
.......|..FAIL..PASS
-------------------
111 |...3...1
222 |........1
Desired (alternative):
......|..FAIL
-------------
111 |...3
222 |...0
View 1 Replies
View Related
Nov 18, 2011
I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.
in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.
In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.
View 4 Replies
View Related
Jan 31, 2013
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
View 2 Replies
View Related
Jun 27, 2009
I have a spreadsheet with these columns:Group_Name
Contact Name
Company Name
Contact ID
Situation:
The Group Name Column has the name of the group that the Contact Name is a member of. The Contact Name could be a member of many Group Names and therefore there may be many rows of the same Contact Name one with each Group Name that they are a member of. I have manually created separate columns for each Group Name in the spreadsheet that is attached.
Goal:
I would like to have a macro that will look at the Group Name column and create a new column for each distinct group. I then would like the macro to go down the Contact Name column (I believe that I will have to have it sorted)and look at the Group Name that is listed in that row and put "True" in the Group Column that was created in the above step. Then, go to the next row, if the Contact Name is the same, look that the Group Name column and put "True" in the corresponding Group Column in the FIRST ROW OF THAT CONTACT NAME. Then Delete that row.
View 2 Replies
View Related
Oct 18, 2013
I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.
I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.
View 14 Replies
View Related
Nov 7, 2013
Columns A, B, C and D contan a list of Names, I want to be able to in column E list all the items in A,B,C and D with out duplicates. How would I do this?
View 1 Replies
View Related
Oct 17, 2008
I'm having trouble programming in VBA. Basically, I need a code which allows me to create a new worksheet, with headers. So far I have this
View 3 Replies
View Related
Jul 11, 2014
I've only done a small amount of macros with VBA.
I want to create a macro to insert 12 cells left of a column labeled "This Year" As the spreadsheet grows (by 12 columns @ year) the "This Year" column moves to the right. Thus I need to reference the range off of that column and then insert 12 columns directly to the left of it each year. Can I somehow reference the label "This Year"?
I then need to enter the month labels in the new columns row 8.
My problem is trying to reference off the "This Year" column.
View 8 Replies
View Related
Sep 27, 2011
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
View 2 Replies
View Related
Feb 25, 2009
I would like to create a list covering multiple columns.
I would like to have 5 columns which will contain the same value/ (word).
If I select,that value from the list, I would like to have each row the word is
found be displayed regardless of which row it is found.
View 7 Replies
View Related
Mar 29, 2007
I try to extract datas from a pivottable (so one data source) which could change.
The items of one field are changing (given by the user through Inputbox): "batch"
The items of the second field ("type plate") are at each time visible (the macro make them visible to have the complete information concerning the "batch")
So I want to extract values and labels from one particular pivottable (the one I display), selecting several batches. But the problem is that some of the items have no value, so disapear fron the table) even if they are visible. As I extract the value on each item (see code below) I get an error message saying "runtime error 1004 application-defined" when the macro read "valeur".
Dim pt1 As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pj As PivotItem
Dim i As Double
Dim valeur As String
I tried to go through this problem using "on error goto solution" but it works only one time, and after same error message
How coud I avoid to calculate "valeur" with the Items (pi) not displayed ?
View 9 Replies
View Related
May 26, 2014
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
View 12 Replies
View Related
Oct 14, 2009
I need a macro that can set up some named ranges using the text in the header row and the sheet name. The header row will always be in row one, but the number of columns will be dynamic. The amount of rows in the range will need to be dynamic also. To clarify, every used cell stating in row 2 in a column will be the named range with the sheetname+text in row one of the column the actual name.
View 4 Replies
View Related
Apr 13, 2006
I need to have a end user form that pulls information from a database and simplifies work for our substitue coordinator. Please be advised I have been working on this and can not get the desired results. I need a form that displays the current date and 5 columns of data the Data needs to be in seperate cells for example cell A1 titled TEACHERS and then the sub coordinator could start typing the name of the teacher in a2 and a predefined database of teachers name, class they teach, etc. MATH, ENGLISH and planning period would be pooled and automaticly fill the cell.
I also would need a second cell say cell B to be call Substitutes and b2- b100 to pull data from a database of substitute names and display their information.
View 9 Replies
View Related
May 8, 2014
I am connecting Excel to a database and there is a field called Data Created which is timestamp. Data comes out nicely in a Pivot Table
1. I would like to be able to group by month but the grouping fiction is grayed out
2. How can we convert the timestamp to only date format. I am able to select only Date for that column but it seems that it keeps the time, even though it only shows the date in the pivot table the bar above shows date and time.
View 1 Replies
View Related
