Reformatting Data In A Table Into Headers

Jan 24, 2009

I have a table with three headers:

Types: close to 4,000 total cells in the column with multiple repeats
Amounts: Obvious
Names: Only 6 available names (i.e. Tom, Bill, Fred, Richard, Sam, Alex)

It looks like this:

Type Amount Name
Type 1 | $$$$ | Tom
Type 1 | $$$$ | Bill
Type 2 | $$$$ | Fred
Type 3 | $$$$ | Richard
Type 3 | $$$$ | Tom
Type 3 | $$$$ | Sam
Type 3 | $$$$ | Alex
Type 4 | $$$$ | Fred

What I want to do is create a table with the parameters using the information contained in the previous table:

Type Tom Bill Fred Richard Sam Alex
Type 1 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 2 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 3 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 4 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$

Is there any way to convert the first table to the second table? I'm using Mac OS/X

View 9 Replies


Reconstruct Data Table So That Column Headers Become Values In Table

Jul 15, 2014

I have a large table that I want to reconstruct. For simplicity sake, let's just says it's 3 rows (excluding headers) by 3 columns.

Item Description

Cheese Burgers

Hot Dogs


I want to reconstruct it so that the column headers become values in the table. The table headers are dates, in this case, if that gives clearer picture. So the new table would have 9 rows, (3 rows of data, excluding the header times four columns).

Item Description

Cheese Burgers

Hot Dogs

[Code] ....

The above example is sorted by date but I would be indifferent if it's sorted by the Item Description.

Is there an easy way to do this? Pivot possibly? Again, my data table is large: 36 rows x 181 columns. Using the copy/paste/transpose feature is pretty impractical.

View 3 Replies View Related

Pivot Table Not Showing Data :: Only Headers Coming

Dec 24, 2009

I have one excel sheet where I write a macro to create pivot table.

It was successfully ran and created the pivot table but there is no data in that table. Only headers are coming.

View 7 Replies View Related

Reformatting Source Data

Dec 22, 2008

I need to translate my Source Data into a new format as follows: ...

View 9 Replies View Related

Reformatting Data Positions

Apr 30, 2007

I have a global dataset where column A represents longitude, B represents latitude, C is a spinup value which can be ignored (even deleted) and columns D to DH are runoff values for the years 1901 to 2002. The rows extend down to 6510 (including the column headers, 6509 without). I've been trying to create a reformatted table based on this data, where longitude values are listed along row 1 and latitude values are listed in column A, with the corresponding runoff values for each year correctly repositioned into the spreadsheet according to this new 'format'; there will be 102 grids created (one for each year). The longitude values are currently not ordered and will need to be sorted (low to high) into position along row 1, without causing the runoff data to become unassigned to its associated longitude and latitude coordinates.

The second part is to then perform a linear interpolation of the reformatted data for each year's table so that the data is again reformatted' from 1.5-degrees to a 5-minute spatial resolution. This involves creating tables for each runoff year as before, but this time creating new values via application of a linear interpolation between each adjacent pair of existing values (in both the x and y direction - as the data are to be imagined as being on a geographical grid with coordinates). I've currently been trying to achieve this using Fortran, but have been getting nowhere as I'm completely new to this programming language, so I thought I'd try using Excel as I'm more familiar with this, although not sufficiently so to solve this alone!

View 7 Replies View Related

Reformatting Data Across Columns Down Rows

Sep 27, 2006

I have train departure times stored in rows for each date as follows:

Each time/date is in its own cell.

I have hundreds of entries like this and what I want to do is restructure them as follows (on a new sheet). For each date and time couple I want the date in one cell and the time in the the cell to the right. Also please note that not all days have the same amount of dept. times and this also needs to be considered.

View 8 Replies View Related

Macro For Reformatting Citrix Data

Jan 9, 2007

I have a report presented in the format such as the one in the attached file.

Current Format.xls

Is there a macro that copies and pastes the data from the original report to a new worksheet with the format such as in the attached file below.

New Format.xls

The reason why i would rather not use Pivot Tables for this purpose is because the pivottable is limiting me from expanding the report to include other looked up data with the AutoFilter option.

View 4 Replies View Related

Reformatting Data - Subtotaling Brings Numbers Down To One Line

Apr 2, 2013

I have several thousands lines of data....much of the data is the same, 2-5 rows per person, but at the last two columns is different numbers/totals....I'd like to get those all into their own column so that each person has one row with all the data...I USUALLY would sit and sort by each total, shift them over to the right into their own columns, then sit and shift them all up to one line...but thats a carpal tunnel project and i know there must be an easier way....subtotalling brings the numbers down to one line once I have shifted each one over, but not the rest of the data....

Pic Attached: Excel problem pic.JPG

View 8 Replies View Related

Table Headers Not On All Pages?

Sep 15, 2014

I have a long growing statistical report(18 pages + more), and 14 of the pages will use report table headers. I went into the page set up and selected the row to repeat on each page. I do not want the table headers on any pages after 14. The rest of the report is formatted differently and does not use the row headers. but all of the information is related. Is there a way to only show the row headers up to page 14? I can't use headers and footers, of course. The report will be set in one place and the user will print it out once a week. It seems as if this cannot be done but I this is my last resort.

View 4 Replies View Related

Pivot Table Headers

Jan 29, 2008

I have a pivot that links to another tab, which has items categorised by Date ranges i.e. Date Group 1, Date Group 2, Date Group 3 and Date Group 4.

Sometimes none of the items will fall into a date group i.e. there is no date group 1's for that period, but my pivot simply removes the whoel date group 1 column when refreshed whereas I would like the pivot to always have the 4 headers and quote 0 if there is none in that category.

View 9 Replies View Related

Table Headers Down Side Of Worksheet Instead Of Across

Dec 18, 2013

Is it possible to have table headers that are down the side instead of going across the top?

I have searched but have not found an answer so far.

View 1 Replies View Related

Pivot Table With Repeating Headers?

Dec 29, 2012

I've built a table that is meant to track the quality scores of multiple teams of agents. The agents are assigned to leads who are doing the quality monitoring. The goal is to track and trend the date the monitor is completed, the score of the monitor (scale of 1 - 12), who completed the monitor (initials in Lead column), and the running average score. The Score and Lead column are drop down validations.

This is being one of my first exploits into excel, so I am not sure if I built this correctly structurally, or what. When I go to create a pivot table off the data I have created, it's not calculating the values correctly. As an example if I want to count the number of 'score' for CCR1 it's always 1. From what I have been able to research is that this is because I have essentially repeated the headers multiple times. Below is what the table looks like so far.



View 1 Replies View Related

Remove Headers From Array / Table

May 29, 2014

I want to convert an array in excel back to normal cells. That is, I want to remove the header row / or undo the "format as table". how to do that?

View 3 Replies View Related

Pivot Table And Column Headers

May 21, 2006

I want to include columns in my Pivot Table where there is no data for that column. For example, I want to show 12 columns, one for each month, but my data only has 9 months of values.

View 3 Replies View Related

Assigning Headers To Table According To File Name And Location

Aug 12, 2014

I need to assign headers to a table according to the file name and location. I have attached the example spreadsheet. Sheet1 contains the table with the data and Sheet2 assigns the headers to each file and location. The code is skipping headers and I can not figure it out.

Macro Example.xlsm‎

View 4 Replies View Related

Pulling Column Headers Based On Table Values (not Max Or Min)

Jun 12, 2014

I've done some searching and cannot find the right answer for my question. I need to create a formula that will pull column header text data based on cells within a table that contain a numeric value not equal to zero. The catch is that I cannot use any one specific numeric value as the basis for a Lookup.

I've attached an example spreadsheet for reference. Basically, I need two formulas:

1. Formula to pull column header (Receiver name) based on Sender. This is a necessity.
2. Formula to pull individual lines for those senders that have multiple receivers (Division D in my example).

My actual data file has 80+ senders and 100+ receivers.

View 6 Replies View Related

Stop Auto-Formatting Headers In Table Upon Row Insert

Jan 3, 2013

I decided to format my data as a table so that excel will auto-fill my formulas when inserting new rows which works quite well. I have one snag though, when trying to insert a new row at the very top (i.e. inbetween the header and first row) and choosing Format As Below, it also applies Data Validation and Conditional Formatting to the header. The inserted row, however, is actually formatted fine and works well.

How do I stop this from happening, and why would Excel do this anyway (as it is effectively applying formatting to TWO rows when only one row is being inserted)?

View 3 Replies View Related

Excel 2013 :: Table Formatting Including Headers

Aug 28, 2013

I have a set of data that I formatted as a table, including headers. It seems that the columns are numbered, and after the column header is a number. i.e., Sales 2, GP Freight 3, etc.

I have spent some time researching this and came up empty. Is there a way to disable this part of table formatting?

View 2 Replies View Related

Excel 2010 :: Dates As Headers In Table Not Recognized As Dates For Charting?

Nov 8, 2012

I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.

If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.

I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.

View 1 Replies View Related

Data Analysis That Has Headers With Subheaders

Nov 20, 2009

I have student demographic data, then I have to track how each student did on each question, but each question also corresponds to a reading standard and I need to pull two pieces of info from that. How they scored on the assessment (# of answers correct), and if they missed a certain standard twice or more (There are 4 instances of StandardA in the test and they had wrong answers on 2 or more of them). I have the solution to the first part. I entered the results of each question as a 1 for correct and 2 for incorrect then did a countif and only counted the 1s. I am stumped on getting an analysis of how each student did on the indicators. Since the indicators can occur more than once, generating a pviot table doesn't work in this instance, it'll count of 1s and 2s for StandardA, StandardA2, StandardA3, etc. and not the counts of 1s and 2 for all instances of StandardA.


What I want:

For student Bob - Number of times he answered incorrectly StandardA, StandardB, StandardC, StandardD, etc.............

View 3 Replies View Related

Listbox With Column Headers As Specific Sheet Column Headers?

Jun 7, 2014

I have a userform with two listboxes in them, i have set the columns in the listbox to 9 and would like to populate the column headers in the listboxes with the column header of one of the sheets.

View 5 Replies View Related

Combining Column Data When There Are Duplicate Row Headers?

Apr 10, 2014

I currently have a worksheet filled with data from other worksheets. I want to combine data from different columns when they share an identical row header and don't know how. For example:


Would go to:


Another specific issue is that some row headers are bold and need to be considered a separate header from the non-bold alternative.

View 4 Replies View Related

VBA To Search Headers And Transfer Data To Another Worksheet

Jan 23, 2014

I have uploaded a worksheet that has a macro attached to button 4,

I want the macro to search the header in "All Data" worksheet and transfer the data under them to under the same headers in "Quote" worksheet.

The data in "All Data" could be upto 60 rows of data.

quote test 1.xlsx‎

View 6 Replies View Related

Move Data From 1 Sheet To The Other Matching Headers?

Feb 7, 2014

I have a DATA sheet that is all of the information reported on a monthly basis. This format will never change, but the data will. I have a second sheet called MMUSAGE.

On the MMUSAGE tab, I have identified which headers (in red) from the DATA tab that correspond with the headers in the MMUSAGE tab. Question is: how do I move the data from the DATA tab over to the MMUSAGE tab under the appropriate headers?

Example: IF Product Number on Worksheet(DATA) = Product Code on Worksheet(MMUSAGE) then move all data to Column L.

View 4 Replies View Related

Delete Filtered Data, Leave Headers

Apr 30, 2009

I have used the snippet of code below many times to delete rows of filtered data. For example, if I want to delete all rows containing "Y" in column F, this code would filter to that criteria, offset it to save the column headings, resize it to avoid deleting the row below the range, then delete the rows. This works beautifully, until I ran into an instance where there were NO "Y"'s in column F. Then it deleted the column headings.

How would I modify the code below to leave the headings in row 1, but delete any other filtered data? (Also, if there's a better way to delete filtered rows but leave headings, I would be interested.)

View 4 Replies View Related

Reformatting Addresses

Apr 21, 2007

I have a few hundred addresses in excel in the following format


Is it possible for me to change that into


View 10 Replies View Related

Reformatting Numbers

Apr 6, 2009

I have some data which is exported into excel by a package I can't amend. The information comes into excel in the format

00:00:04 (representing hh:mm:ss) but if I look at the formatting of the cell it says the formatting is general. I want to be able to cut and paste this into another spreadsheet and then apply conditional formatting to colour the text if the value is above 00:00:09, but try as I might, I can't! When I cut and paste (even using paste special) it still retains its previous format, and I still can't amend it

Any suggestions?

View 6 Replies View Related

Date Reformatting

Apr 16, 2008

I have several non-consistent/non-sequential columns with supposed dates formatted as:

09.04.08 (day/month/year). I'd like a macro where a popup window will allow me to type in columns to execute on (ie. a,g,m,t) & then it will go through & change the dates to REAL date format 04/09/2008 (month/day/year).

If this easy enuf that any of you gurus could write a few lines?

View 9 Replies View Related

Counting Filtered Data And Matching To Specific Monthly Headers

Apr 2, 2014

Essentially I want to show how many loans funded in October 2013, from ABC Lending, and I want that result to show in cell K9 of the Sample Output sheet. However, since I'm dealing with filtered data, I cannot figure out the correct combination of formulas to make this happen (e.g., SUBTOTAL, VLOOKUP).

Specifically, I'm stuck with the combination of a) counting the results that meet the monthly date criteria, combined with b) the filtered results. The single cell that I want to solve is K9, on the attached "Sample Output" sheet. Once I see how this cell works, I can figure out the rest. This cell is looking for the number of loans that funded in October 2013, from ABC Lending.

The filtered results are located on the "SCORE Data" sheet. The filter is already configured to only show results for ABC Lending, and the relevant column is labeled "Funded" (column "I"). Since the "Sample Output" sheet is looking for loans that funded in that month, and not a specific date, I created column "U" on the "SCORE Data" sheet, to convert specific day-dates to month-dates.

View 3 Replies View Related

Reformatting Matrix Into Single Column?

May 27, 2014

I have a matrix that looks like this:





And would like to rearrange this data to:


In actuality it's a 110000 x 3 matrix that should become a 330000 x 1 column.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved