Divide Data Into Two Categories?
Aug 1, 2014I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 3 Replies
ADVERTISEMENT
How To Divide Data Into Categories
Aug 1, 2014I am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 10 Replies View RelatedGrouping Data Into Categories
Jun 9, 2006I have a given set of data. Each row of data fits into one of 7 different categories. Besides doing a simple filter to be able to pull out a certain "category", how can I do this differently? Is there a code/macro process I need to go through? Basically what I need to do is find a way to group these categories together, so that someone searching for a certain category can easily pull it from the data.
View 8 Replies View RelatedData Formatting (choose The Different Categories)
Nov 6, 20081. I would like to be able to play with it by choosing different categories, sub-categories etc (adding different ones etc.). I tried pivot table but to get this data in pivot table format is a very time consuming task to start with.
2. The sample file has only one month. I get this type of data every month. So have to annualize using other months etc. I could get different months in a column also. So for example column d could have Jan, Feb, March etc. for row of data. But that would mean I will have 1922 * 10 = 19220 rows. And when I update this every month row reference from formulae (If I had formulae) will need to be updated.
I have the flexibility of getting this source data rearranged little bit but not too much flexibility there.
Automatically Sorting Data According To Defined Categories?
Apr 25, 2014I have two columns, in column A will have four or five categories repeating randomly, in columns B will have unique names.
What I would like to do is to define categories as a column names in a table and then to automatically fill that table with the data from column B depending on category.
Extract Data Into Categories / Columns From ALL Being In One Mixed Column?
Sep 13, 2013I extracted data from PDF into a spreadsheet, and now I need to put all the data - according to name, email, address etc. into separate columns, so it is usable.
All categories of data are in 1 column at the moment - mixed up.
How can I sort this into columns according to category?
Divide Data Into Different Sheets
Apr 7, 2014I have minutes of meeting and have to divide them into different sheets against completed, and different departments and dept HOD responsible for it. How do I do it without "Copy/Pasting" the code? Also I have to remove the ones that are done. Any macro or something that can be written for these.
format of sheet
Sl No. | Date | Remarks | Dept | Dept Head 1 | Dept Head 2 | Status
I have to get data into different sheets against Dept, Dept Head 1, Dept Head 2 and remove the "Done" status to different sheets.
How do I do it without manual labor?
Divide Value In Column A By Data In B:AO Using VBA
Aug 15, 2013I'm trying to divide the value in column A by the data in B:AO using VBA, however I need to repeat this for many rows (the values differ for each row).
There could also be a different amount of rows with data in each time, and there is also a single blank row after every 18 rows with data.
How To Re-divide The Data At The Same Time Interval
Apr 18, 2009I have 2 columns of data (value and time):
for example:
15 4/2/08 13:00
4 4/2/08 19:00
7 4/5/08 12:00
13 4/9/08 3:00
They are continuous data. so I want to divid the value into hourly data as follows.
15 4/2/08 13:00
? 4/2/08 14:00
? 4/2/08 15:00
. .
. .
? 4/9/08 2:00
13 4/9/09 3:00
Divide Uneven Data Into Columns
Oct 10, 2009I would normally sort out using Text to Columns, but I can't in this case as the remaining elements can vary.
I have a list of names with Forename and Surname and then the elements after that vary.
Names are written in one cell under a single column like so:
John Smith 13/04/2000
James Smith 123 123 1233
Jane Smith 07122 12345
Another Smith AB99 9CD
All I want to do is end up with 3 columns of data,
Forname | Surname | Other
Is it possible to use a formula to make the breaks into each column at the first 2 spaces only, therefore the contents after the second space would just end up in one cell?
Excel 2003 :: Create Pivot Table That Aggregates Equal Data From Separate Categories
May 29, 2012Is it possible to aggregate data in a pivot table from different categories?
I have excel 2003.
For PURE illustration, I have 6 columns, A-F, respectively:
"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.
I want to see in a final output:
Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.
When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.
It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.
Sum Categories At Each Change In Value
Feb 25, 2010Could somebody please suggest a formula that will sum values at each change in category?
I've played around with a pivot table, but for this it really doesn't fit.
Column A is the category and there are totals in column B for each entry.
The only place I need a formula is at the change in First and Second...so for any second all of the third's plus that second and for the first everything below.
Attached is an example with outcome in column C
xxxx First
xxxx Second
xxxx Third
ComboBox With Categories Or Subheadings
Mar 11, 2013Is it possible to make a ComboBox with categories. I have provided a sketch below of how I want my ComboBox to look like. I don't want the user to be able to click on "Fruits" or "Vegetables", but they should be able to select any of the other options.
ComboBox.png
Matching Categories And Summing
Oct 28, 2009 The ones we need to focus on are Columns A,C, H, and AG. Daily this sheet ‘Raw’ will be filtered and data sent to sheets 100,200,300.
In the Raw sheet we have many rows of data, but when this is transferred to the other sheets we will only see one row of data in each sheet.
1. The Date from Cell(C2) should be copied to Cell ‘A3 or next empty cell on column A’ on all 3 sheets(100,200,300)
2.In Column A there are 3 accounts – 101,201,301
If Column A value = 101 then send the corresponding Column H (P&L) value to Sheet”100”, If Column A =201 then send to sheet ‘200’, If 301 then to sheet ‘300’
Now that’s not quite what I need, the Column H values in some cases have to be added up – by category. You can see this on sheets 100 – 300. Columns B to K on Sheet(100) have the different categories of products. What I want is a code that would go through Column AG in Sheet(Raw) match the category name to the category name in the Range(B2:K2) and copy the P&L figure (Column H value in ‘Raw’) there.
e.g. I need all the rows pertaining to account 101 with the same label ‘Interest Rate’ to have the P&L (Column H) added up. So I would get the value 188760 in Sheet ‘100’ Cell D3. (I just added H2,H3,H12,H13) ...
Averaging Different Cells From Specified Categories?
Mar 17, 2014I want to average cells in different columns which meet a minimum criteria, i.e. > 2.0, from specified categories list in the first column. Example: I want to select a category listed in column A and average the numbers listed in columns C and F for that category which meet a minimum threshold.
View 9 Replies View RelatedSumming Dates & Categories
Apr 7, 2009How do I summarise the attached table. I want to have a drop down menu in the MONTH cell (B13) that summarises the month nominated. Also a drop down menu in CATEGORY that I choose (B14). The value of the category in any particular month is totalled in B15.
I know how to create drop down menus and how to define ranges - just not sure of the formulas to use in order to get the correct answer in cell B15....
Pivot Time Series With Two Different Categories
Dec 29, 2013I have a series of up to 40 or so entries for given dates. These entries are categorised by either "bid" or "offer" and I'm trying to find a way to chart them both using a pivot table. I'm stuck as it seems that a pivot can only count the number of bids and offers on any given day, but it cannot differentiate between them.
Note: that the original list the pivot depends on will constantly be updated so I can't just develop a custom chart.
I've attached a spreadsheet with an example of what I'm playing with. Pivot graph is in "PIVOT" worksheet, data is in "DATA".
pivotland.xlsx
Dropdown List From Table Categories?
Apr 14, 2014I have a file and it has a table on it with 2 categories.
what i want to do is select a color in the first drop down list and than in the second only select the items that match the color i have previously selected.
Is there anyway to do this with named ranges because i would like to index everything in a table.
Total Categories Of Expenses Spreadsheet
Feb 8, 2014I have a small online business and am slowly learning Excel to keep my records. I looked at Quickbooks and I think that it just a little too complicated for my needs, besides I like excel better.
The spreadsheet I want to make is how can I summarize the different categories, shipping, travel, EVSE, Wire, or whatever I come up with in the future from a daily expense spreadsheet. I guess the summary should be on another page.
I also guess I can make up a total also of the companies I buy from...
I've attached a beginning daily expense spreadsheet with some entries.2014 costs.xlsx
How To Insert Space In Different Categories Of Numbers
Jun 22, 2013I have a column of numbers that look like this: 111222111222111 and I need them to look like this: 111 222 111 222 111. The numbers are all random and not 111222.
Then I have a second column with numbers like this: 11112222111122221111 but they need to be like this: 1111 2222 1111 2222 1111.
Count The Number Of Categories In A List
Nov 1, 2009I am building a tracking list for several items and have categorized them
Software
Hardware
Networking
I will have several of these and the list keeps getting bigger. I have a table above the list that summarizes these catergories and the count of them. How can I count each categorie and so that it updates each time I add one.
The formula would have to sumproduct or determine what categories are there and how many (unique list) and update when I add one.
Currently its a manual process of creating a unique list and doing a countif.
Pivot Table / Chart To Filter Categories
Apr 28, 2014I have a list of customer satisfaction scores that are pulled in from a sharepoint list. The list is then used to create a pivot chart that is used in a web part on a dashboard in sharepoint. The chart is supposed to show average customer satisfaction scores per category per month.
The problem I have is that I can't filter the categories using a slicer, I can only filter the categories' values (i.e. the scores). I can filter by date though.
When I try to filter the various score categories/series (Support, Change Management etc) I only get the option to filter their values i.e. 1,2,3,4,5.
I want to be able to use a slicer or similar to be able to display either all of the series or just selected ones.pivottable.JPGpivotchart.JPG
Drop List - Arrange Items By Categories
May 10, 2014Drop list. First of all I have sheet with all my data and i have define the name of each table.
I have about 15 categories table and a table with min 2 and max 100 items in each category.
I have made in a different sheet a drop list for my categories. Near this list i made another drop list with the items by using the formula =indirect() to arrange my items by categories.
The problem that i have is:
- Some categories they have many items and is difficult to find some items. i need to put a formula to put some letters and find what i need easily (easy searchable).
How To Find Percentage Of Total For All The Items In Different Categories
Dec 17, 2013Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total
1234Bed150
5678Bed100
1547mattress52
58658mattress188
12356bed12
8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
Managing Categories Based On 2 Variables And A Chart.
Aug 18, 2009I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?
View 6 Replies View RelatedSumif Multiple Criteria: Sum Spend In Certain Categories
Sep 1, 2009I have a column which lists total spend by vendor. I am using the sumif formulae to sum spend in certain categories, for example spend greater than £500,000. I would like to use two criteria, for example greater than £300,00 but less than £499,999. I am also using countif to get the number of vendors in each range of spend. How do I adapt my current code of "=SUMIF(D2:D1463,">300000",D2:D1463)" to look at greater than £300,000 but less than £499,999. I'm sure this is simple but like most things only simple if you know how.
View 2 Replies View RelatedCreate New Column To Group Subcategories Into Categories
Apr 6, 2012I have a column that has subcategories, and I would like to create a new column that would group those subcategories into categories.
Is there a formula that once it detects a certain word in the subcategory should give a certain category name that I choose?
Excel 2013 :: Categories In Reverse Order
Oct 4, 2013Using Excel 2013. I have a pivot chart with date as the x-axis, 1 y-axis series on a Primary and 1 series on a secondary axis. My pivot table is sorted in descending order by date because I need the most recent date on top. To chart the date in order from left to right, I've turned on Categories in Reverse Order .
1. When hovering over the data points on the primary series, the date is reversed. Example: the data point Jan31 says Jan1.
2. The bigger problem is the series using the Secondary Axis is plotting Jan31-Jan1 instead of the Reverse Order .
Is this a problem with Excel or is there other options that I can try to correct all this?
Summing Values Based On Multiple Categories
Jul 22, 2014I am trying to create an inventory control system that keeps tracks of what's in bins based on additions and subtractions to the bin. I am hoping to generate a summary output of bin contents based on the transaction list. I know how to do this using pivot tables, but am looking for a VBA or excel function solution since I will have hundreds of bins and thousands of transactions.
Bin number
Product Code
Pounds
1
A
50
1
A
-25
[Code] ......
I want to have the following table auto generated.
Bin
Product Code
Sum Quantity
1
A
25
1
D
75
[Code] .......
I'm using an array formula right now, but it is proving to be extremely slow.
=IF(ISERROR(INDEX('Harvest Generator'!$E:$I,SMALL(IF(LEFT('Harvest Generator'!$E:$E,LEN('Harvest Generator'!$E:$E))=LEFT(B$3,LEN(B$3)),ROW('Harvest Generator'!$E:$E)),ROW(1:1)),B$2)),"",INDEX('Harvest Generator'!$E:$I,SMALL(IF(LEFT('Harvest Generator'!$E:$E,LEN('Harvest Generator'!$E:$E))=LEFT(B$3,LEN(B$3)),ROW('Harvest Generator'!$E:$E)),ROW(1:1)),B$2))
Where Harvest Generator is the sheet where my table is on.
Count By Categories Within Pivot Chart/Table
Mar 17, 2008I can not get my Pivot Chart to count, sort and categorized my data. I have included a sample file for your viewing. I basically want the data in cells C2:E18 counted, sorted and totaled by categories and locale. I thought the pivot charting was simple, but I'm having quite a challenge with this one. The pivot chart data should reflect as my sample indicates in cells E24:K27
View 3 Replies View Related