In the attached file you'll find an original table and the requested Table in its new layout. I thought to use TRANSPOSE in an Array- Function but it didn't work as expected. However, I managed to present the DATA (light green range) with the help of SumProduct. My Question goes like this: Is there a way to present the Upper and Left Headers (in red font) with Formulas ?! (I typed them by hand).
View 2 RepliesI have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.
Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.
My question is explained inside the workbook. Changing a Table layout
View 5 Replies View RelatedI've got a huge spreadsheet that's been imported from a web survey and it has thousands of variables in a single column (A) in excel. each of these variables are on a new cell row and begin with a value of 'V1=' up to 'V100=' or a few fixed values like :USERID:= (see below).
It has unique markers showing the start and end of each survey,so it's something like:
***START SURVEY DATA***
V33=5
V17=2
V18=
V35=4
V51=
V36=3
V52=
V19=1
V53=
V70=Mike J Evans
V37=4
V54=
Now each of these V numbers is an answer to a question on the survey, so I need them to all line up in a column, otherwise it makes no sense
So what I need is a way to scan down the column and convert each survey (from ***START SURVEY DATA*** to ***END SURVEY DATA***) so it's arranged in a row (with each value in a new cell in a column.
Does anyone have a clue how to do this?! (I've uploaded an example as an attachment that'll show a couple of surveys imported and a second tab that shows how I need them in rows - only without sorting the v values properly.)
I would like to change the data layout from the "Original Table" to the "Final Table" as in attached file. Belinda_June-25-07.xls. How do I use VBA to do the change? In my original table, I may have several hundred rows and more than 36 columns for the time periods.
View 3 Replies View RelatedI have a bar chart that monitors month over month activity. I use Layout 5, which allows for the numbers data to neatly be organized in a table below the bar chart. However, i recently started using the data on a secondary axis to accurately reflect trend lines. It works great, but now the data table below displays redundant information. see attachment.
example.jpg
I just want that data in the data table to be displayed once, completely leaving of the axis legend.
I have an excel file, attached. I have a system that output all the stock in the format of sheet "price listing".
At the moment I manually create the layout for the text in "sheet 1" and use a macro to update prices etc. There are about 400 lines in the full listing and having to update the layout constantly for new products, deleted products is very time consuming. I send this list to customers every few months for them to see the range and the prices, so it has to look well.
I am looking for a macro or a pivot chart or something that I would be able to run on the "price listing" sheet and would put it in some usable format. Different customers can have different prices so that it needs to be quick and flexible. I send this list to customers every month for them to see the range and the prices, so it has to look well. Is there any way to create an index also from an excel workbook?
In my data, I have:
1. Age (up to 20, 21-30, 31-40)
2. Gender (male, female)
3. Location (London, Paris, Berlin)
I want to see Age and Gender as column labels and Location as row labels. However, I don't want Gender as a sub-set of Age - I want Age Labels followed immediately by Gender labels ie:
Upto20 / 21-30 / 31-40 / Male / Female
I don't want: 21-30 Male / 21-30 Female / 21-30 Total / etc etc..
Is there a way to specify this in a pivot table?
Rows 1 to 12 of the attached .jpg file shows the format that I receive from a University. However, in order to check enrolments, etc., I would prefer it to be in the format as shown on rows 15 to 19, that is, one complete student record per row.
As you can see, not all students are studying the same number of subjects and the subject mix is also different.
I have responses from a questionnaire in the format below, and need to mail merge to a word doc.
name
subject
teacher
grade
comment
[Code]....
If I'm right I need it in the following format for a mail merge.
name
subject
teacher
grade
comment
[Code]....
I need to get this fixed by 5 pm tonight, otherwise a foresee a night of copy and paste ahead. I thought pivot tables might work, but alas no, it only tells me the number of the comments i have.
I have upgraded to Excel 2007 and the PivotTable formats are different. I prefer the old 'drag and drop' formatting, and would like to default to that.
I can switch to the classic format by going to Pivot Table Tools ---> Options ---> Display --> Classic Pivot Table Layout, but I have to do this for each table I create. I'd like this to be the defaut choice.
I've searched the board and someone posted that if you add the Pivot Table/Chart icon to your task bar, that will open the old layout --- it opens the wizard, but the table that is created is still in the new format which does not enable drag and drop.
I have a database in this format:
IDProgram NameNIDCvg BeginPlan CodeRate
B00001RT1WILLIAM9999999991/1/03INDEMA5
B00002RT2RAMON9999999997/1/06INDEMA2
B00003RT1MARY99999999910/1/04INDEMA5
The number of rows in the database changes from month to month. I'm trying to write a macro that will pull rates off of a rate table on another sheet in this format:
Plan12345
CCAREA RT1$0.00 $0.00 $0.00 $0.00 $0.00
CCAREA RT2$0.00 $0.00 $0.00 $0.00 $0.00
INDEMA RT1$225.81 $436.89 $415.78 $595.20 $320.80
INDEMA RT2$146.36 $277.76 $264.62 $376.31 $205.49
PACIFA RT1$193.07 $434.40 $415.08 $598.49 $337.85
SH RT2$74.08 $148.16
DELTA RT1$25.85 $51.70 $82.74
DELTAP RT1$39.26 $78.52 $126.76
RLIFE RT1$1.25
I'm trying to create this in a way that the user won't have to manually fill in the formulas each month when the size of the database changes. I can also reformat the rate table if need be, but not the database. I've attached a sample file so you can see what I'm talking about. I'm fairly new to VBA.
I can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
if there is a way to display a table as column percentages but have the totals as raw numbers.
View 9 Replies View RelatedI have received a database containing listings of products and their sales, by month (see left side of attached file). What I actually need is to have this table converted (transposed) into a “flat database” which I can later manipulate with a pivot table (see desired output on the right side of the attached file). In the attached file I have drafted the desired output format of 1 line from the raw table, which I would like to get.
What I am looking for (if I might be so bold and ask) is a macro that will allow me to have a simmilar result for ALL the lines (there can quite a few). I think this output format can be achieved with a few simple loops, probably nested, but unfortunately I was not able to find the right sequence (being a novice in VBA).
I have tried to find the answer here, between all the posts regarding “transpose” but couldn’t find something that looked (to me) as containing a relevant solution.
I am trying to convert a table into three columns so that I can use the data in a vlookup.
View 8 Replies View RelatedI am doing a literature review and at the moment I have a table where I am listing all articles and ticking of their "theme", "theories used" and "methodology" like this:
How_do_I_get_from-this.jpg
Now I need to map the theories onto the articles, like this:
to_this.jpg
How do I accomplish this? (in an automated manner, obviously)
I hope the pictures are sufficiently self-explanatory, it is kind of hard to explain what I am trying to achieve otherwise. Note that ticking boxes is accomplished by changing the font to wingdings 2 and using uppercase P for checking a box. I know there are real checkboxes for this purpose, but I am generating the table from imported data and can't find another way to program excel to tick boxes if a condition evaluates to true.
Go to this link to download the original file: [URL] ....
How do i consistently convert a PDF table to Excel? When in the PDF i am recognizing the text and then trying all available ways to either copy or export the selection (to all available file types!) but it only works in a usable manner about 10% of the time....
View 5 Replies View RelatedI need to convert a Matrix to a Table. Something like this:
Input file:
Capture1.PNG
Output :
Capture2.PNG
I have three columns of data starting in row 5, the headers of these columns show Origin, Destination and mileage. Is it possible to have a macro that converts the data into a table?
View 2 Replies View RelatedI am trying to convert a table into a list, please see the example, I want to convert this table
10547
aaa
140x200
160x230
300x400
10549
bbb
140x200
150x260
[Code] ........
I need to convert this kind of table to a list (like in below example)
Can someone give me an instruction or macro to do this ?
Table
X Y Z
A 3 5 7
B 2 4 6
C 8 9 10
List
Column1 Column2 Column3
A X 3
A Y 5
A Z 7
B X 2
B Y 4
B Z 6
C X 8
C Y 9
C Z 10
I have a large frequency table, the dataset looks like:-
Category Age Frequency
A 1 4
A 2 3
A 3 2
B 7 1
B 8 3
C 4 2
C 6 4
I would like a formula to get:
A B C
1 7 4
1 8 4
1 8 6
1 8 6
2 6
2 6
2
3
3
I have a table with Companies in a column and Countries in the heading row. Now in this table I have an entry "1" against certain countries for each company.
I need to get a concatenated (Company&Country) list, but I need this is all in a single column.
Companies list.xlsx
I have a table in Sheet1 and it's about of student's name, lessons and class.
I want to create a macro which convert to all data like Sheet2.
Need to fit my raw data (Raw Data Sheet) to fit my table template.
Need to convert my daily data to weekly to fit to my table template.
Please see attached file for reference.
How can I convert the top dataset to the bottom dataset?
Row Labels
2011.13
2011.14
2011.15
[Code].....
I've posted an example workbook that has sheet1 as how the data comes and sheet2 as what I need it to look like (through some kind of automatic process). I really don't have a clue as to how to get this done, and I'm guessing some VBA is going to be required.
View 3 Replies View Related