im currently working on a project that needs to consolidate the data per given date. but the thing is, all of them are located into one column. i was able to separate each category into separate columns but now, i cannot align the data on a per date basis.
View 10 RepliesI 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.
I 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
I 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 RelatedI am using a pivot table to summarise information with a list of tonnes and sources from civic amenity sites. All was fine until I got to this month to do monthly reports. The pivot table appears to be treating what appears to be exactly the same categories of "CIVIC" and "TRANSFER" differently. By this I mean that although the values in the column appears to be "CIVIC" or "TRANSFER", some get treated differently in the pivot table making it pretty useless. I have made sure that all spellings of the words are the same. there are no spaces before or after the word e.g " CIVIC" but to no avail!
View 4 Replies View RelatedI have a macro that copies data from 1 sheet to another one.
However, in the destination sheet I have no way of know which is the 1st empty row that my data can be paste into.
I have a table of data, every entry in the table is different. I need a way in a single cell to determine what column the data is in (and a way to get the row it's in would be nice too), some way to do VLOOKUP and HLOOKUP in reverse.
In the attached example I need a function that can take dog and return "1", "A" or even the header "Animal", if another similar function can return the row, that would be nice also but if I can get the column I can get the row. Example
I've tried a few things with creating an array formula to do matches across multiple columns and I can make a multi-cell array that will flag the right column with the right column number, but any attempt to sum/max/match this array formula returns 0 or #N/A
I would like to only view (or otherwise mark) the highest value cells (in column g) for each category (column c),
View 3 Replies View RelatedI have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.
Example.xlsx
Is 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.
I am wondering, in excel 2003 is it possible to randomly select two numbers from one column, say column b9:b45, that are random numbers, and selected by two different font colors. In other words: b9=1348, b10=1349, b11=1350, b12=1351, b13=1352, b14=1353.
Say we press a macro button, and in field B50 one radom black colored number is selected of a field of say 30 numbers that are all black in that column, and in b51, one red number is selected, in a field of 30 red numbers. We want to used this macro to select winners simulating a raffle drawing.
i'm trying to automatically create hyperlinks in some cells from mail addresses located in another column. this is what came out:
View 2 Replies View RelatedI 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?
I 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?
I have one column of data, I would like to separate the ODD and Even row values into two separate column, such that the data will appear staggered.
ie. this is my data in column 1
1
2
3
4
5
6
7
8
would like to separate it into columns 2 and 3 as follows
1
.....2
3
.....4
5
.....6
7
.....8
I believe I need to use the ISODD, ISEVEN function somehow but can not figure it out.
I am trying to divide one column of data into three. I can't change the order of the data because it is set up as a schedule at my work.
Here's how the info is sent to me, lets say columnA Row2:
500001
500003
500002
700002
700003
700001
800003
800001
800002
Here's what I need my spread sheet to do.....lets say in column's C, D, and E (all in Row 2)
C D E
500001 700002 800003
500003 700003 800001
500002 700001 800002
I have several large excel spreadsheets with different pieces of information that appear in a single column and I am looking for a way to seperate the data into multiple columns. I have read many different articles and how-tos and none seem to help with what I have...here goes:
I have a column that has a few thousand lines of info that looks like this:
Acme Stores
Name:
Smith, Steve
Reed, Tom
ShopRite Stores
Name:
Stadler, Fred
O'Neil, Tim
etc.
So, its a repetition of the store name, then a cell that just says 'name', followed by the people names associated with that store.
I am looking to somehow separate out the store names, leaving just the people's names in that column - but keeping the store names in a seperate column so the people names can be associated with the store name. The only thing that I think might be able to help, is that the 'name' field is constant and its always the cell below the store name. I don't know if that is something that is even remotely helpful here, but thats the only thing I can think of if someone knew some type of conditional command that would pick out a previous cell from one with the word 'name' in it.
I have a glossary with 400+ rows in column A. See examples below. I'd like to separate them so that the term is in column A and the definition is in column B. Once Done I will not need the . Normally instead of that tag I would have a hyphen separate the term from definition but the fact that some terms have a hyphen like "D-VHS" was throwing me further. In the end I will not need a separator because everything will be in separate cells.
DSL Digital Subscriber Line is a technology for bringing high-bandwidth information to homes and small businesses over ordinary copper telephone lines. A DSL line can carry both data and voice signals and the data part of the line is continuously connected.
D-VHS Connects a digital audio/video cable for use with some D-VHS digital VCRs.
E-Mail Messages sent to a customer's receiver that are viewed on their television screen. Pending messages are indicated by an icon on the on-screen status display (channel marker) and by having the Power button flash. (Not all receivers have a blinking light.)
I have one column that has forename and surname information and I need to separate the surname into a separate column next to the forename.
Is there a formula for this?
I've tried the formula where you can request to move so many characters to the right, but it's difficult with all of the surnames being a different number of characters.
a way to separate out data that has been all entered into a single column
code .....
The above information is a sample of the data. The data has been entered into 1 column, column A.
I want to separate the entries in to 1 column each for vehicle ID, full journal description, actual journal, EE code, Report total and equipment description.
Whoever created my customer contacts Excel sheet prior to my arrival entered all of the contacts address information into one cell and in order for me to put this info into Access, I need them seperated. For example:
10000 X Street Louisville, KY 40291 is in cell E2
I would like it to read:
10000 X Street in cell E2
Louisville in cell F2
KY in cell G2
40291 in cell H2
They also did this with phone numbers (ie. desk# / cell# / fax#).
There are over 1000 contacts in this sheet, so it would take forever to split these apart row by row.
I'm not sure if what I am trying to accomplish is best done using the LOOKUP function. It occurred to me that a table might be the best way to go but I'm not sure exactly how to go about it. The LOOKUP function I am using contains way too many variables and thus has not yet been correct in displaying the proper info.
What I would like to do is create a workbook with a table, say column "A" is the Style/Model product, column "B" would be the pricing information, and column "C" would be the Product Identifier......
Good Afternoon. I am attempting to create a pivot table using 4 columns from 5 different tabs. I have created pivot tables before using one tab and they are pretty easy. However, I have been working on this for over an hour and I am getting nowhere. Can someone tell me how to put all five tabs into one pivot table? The columns are all in the same order on each tab. They are as follows: System, Management Area, Schedule Date, WO Type. I want the pivot table to have System and Management area on the left side, date on the top row and counting the number of WO Types.
View 6 Replies View RelatedI have 10 workbooks that each contain a table of data. Each table is consistent in format and headings.
I need to group all 10 tables into one big table. I do not want to merge or combine data just simply have all the data in one master table.
Previously I have used named ranges and arrays to copy them in but this seems cumbersome and thought there must be an easier way to do it.
I have a problem which requires a vba routine that is currently outside of my level of expertise...
Here is an example of the data:
TESCO GARAGE 3729
59
REF 177 1092098207 BCC
59
CO-OP GROUP LTD
60
[Code]......
I am working on a spreadsheet to track membership data. In Col A, I have the year that the member started their membership. In Col B, I have the dd/mm/yy that their membership renews.
For instance:
ABC Company began membership in 2002 (Col A). The next billing cycle (membership renewal) is scheduled for 7/1/2014 (Col B).
Is there a formula to take the month and day from Col B and the year from Col A and combine them? For the example above, I want the result to read: 7/1/2002
I currently have a spreadsheet that has 4 columns. I want to review the information in column A and if it matches, I want to combine the cells in column D into one cell for the matches in column A. Here is an example of the information I have:
123456
ABC Company
901
0001
123456
ABC Company
901
0099
[Code] ....
So basically I want to review column A and any # in that column matches, I want to combine the cells from column D into one cell. So I would want the first sequence to catch that A1, A2, and A3 match... so then it would combine D1, D2, and D3 into one cell that would read "0001/0099/HRA1". Is this possible?
see the picture below to get an idea of what i want to do.
http://aycu15.webshots.com/image/362...0389296_rs.jpg
Say I have a column of numbers. I'd like to be able to string them together in the following format: number1|number2|number3....
connect.xlsx
in column A i have account name but it can be up to 4 lines (rows)
in column B i have the fund name which goes with the first account name row
so...