Managing Categories Based On 2 Variables And A Chart.
Aug 18, 2009
I 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
ADVERTISEMENT
Apr 28, 2014
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
View 14 Replies
View Related
Mar 17, 2008
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 Related
May 29, 2014
I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.
I found the following formula which, when used as a named range, automatically extends a list for data validation:
[Code] ....
I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?
View 2 Replies
View Related
Jul 22, 2014
I 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.
View 2 Replies
View Related
Jan 17, 2013
I do PPC work and you can export query data from Adwords with Column A being the actual search query. Then column B - E are impressions, clicks, cost & conversions (from each query). In this example, cell A1 might be running shoes, cell A2 might be walking shoes, cell A3 is running pants, and cell A4 is baseball pants. I want to add a new column (F) that does product category groupings based on partial text matches in column A. For example, anything that includes the text string shoe or shoes should be labeled as shoes in column F. Similarly, all queries that contain the characters pant would be listed as product category pants in column F.
Currently I do this manually by conditional formatting on column A for contains text and then color the cells. Then I sort by cell colors. Then I manually type in the product category into F for each color block in A. Needless to say, this is slow and manual.
What I would prefer is to have a master table (like a vlookup) on sheet 2 where column A is the list of partial text matches and column B is the product category to be returned if that the partial text in sheet 2 column A is found in the query list on sheet 1 column A. This way, on sheet 2 I could have cell A1 sneaker, cell A2 shoe, cell B1 shoes, cell B2 shoes, etc to manage the correlation between text strings and product category groupings.
Basically I want a formula to put in the cells on sheet 1 in column F that searches all of column A on sheet 1, looking for partial text matches from column A on sheet 2 and returning corresponding product category in column B on sheet 2.
View 6 Replies
View Related
Jan 31, 2014
I am trying to calculate players ranking based on their weight categories and attempts total, e.g. Category (50kg, 69kg etc..) Rank should be calculated automatically the highest total value of cat. 50kg is 1st second highest is 2nd etc. However, if tow players or more within the same Category have scored same point ranking will be calculated based on Body Weight less comes first and if both have similar weight calculation will be based on Start # first player comes first etc..
Table:
Start #
Name
Body Weight
Team
Category
Total Point
Rank
1
Player #1
49.2
Team #1
50kg
75
View 2 Replies
View Related
Dec 18, 2009
I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.
View 2 Replies
View Related
Oct 30, 2006
I am trying to create a column chart in which i want to incorporate 2 variables. However I don't want to use the line for the second variable. What I would like to do is to add the information of the number of deals a company has done on top of the column. So you see a column which gives you the average size of a deal, and the you should see a number at that column to see the number of deals done with that company.
To make this more clear I have attached an example. There are 2 tables of which the information I would like to see in one chart.
View 9 Replies
View Related
Feb 11, 2008
I've seen a preference setting somewhere that allow me to specify what happens with the cursor after entering a new value in a cell. such as moving down or moving right or not moving at all?
How do I code the cursor to NOT MOVE out of the cell after a new value has been entered?
View 9 Replies
View Related
Mar 25, 2014
i am looking at creating a combine chart for 3 variables.how to achieve that?
TeamSubTeamsScore
A X 20
Y 25
B X 10
Y 20
C X 15
Y 10
View 3 Replies
View Related
Jun 18, 2014
I have two spreadsheets of "leads"
One lead is a do not call list that has 4 separate columns of phone numbers,
The second list is a batch I purchased.
I need to make sure that the second batch does not contain the info from the first spread sheet.
I guess I could modify the 1st spreadsheet to just be one column of numbers, then I would be comparing one column to one column.
View 5 Replies
View Related
Jun 27, 2013
I`m making an Costumer Database for my business in excel, I already made a form in witch I can insert, change or exclude a client. In this form I have a button that hides the actual form and show another one to perform the search.
The search is working fine with the listbox, what I need right now is that i can search someone and then I click on the client i want in the listbox and somehow get the reference and open the other form with that reference.
The first form work with an reference number that is defined as a variant, so i have a macro that see this number and bring the client with that code. In the other form I needed a macro that gets the value of the selected reference in the listbox, then hide the form, call the other one and run a private sub.
View 4 Replies
View Related
Oct 8, 2012
We have multiple macros that have been developed by various users in the company. Many of these macros have file paths hard coded into the macro to reference file locations with networked drives.
We are planning on making some changes to the network file structure which will break these macros. I realize we will have to go in and manually change all these. I want to ensure that the end users have the flexibility they want, but not have to touch each macro if/when folder structures change, or files migrate to SharePoint etc.
ini files, master reference Excel file...,
View 3 Replies
View Related
Nov 5, 2013
I 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
View 1 Replies
View Related
Apr 19, 2014
how to return a value based on two variables. I was trying with hlookup and offset but excel kept ignoring my thoughts.
Here is a sample of how the data looks like and what I want to do:
excel.jpg
Depending on the ID which is entered in G2, cell H9 has to get the value from D4.
I know that ID is gonna have to be extracted with find function from column A. So every time an ID is entered (in cell G2) it would need to look for it in column A and then return the value VACLEG from column D.
View 5 Replies
View Related
Jun 18, 2009
I'm trying to create a simple checkbook application for a friend. I've gotten pretty much everything I want figured out except for two functions, which are basically the same thing. I'm trying to find the sum of all deposits and of all withdrawals based on two entered dates.
Basically, they enter the dates, and the formula finds the dates, then sums all the deposits/withdrawals between them. I attached the workbook with some sample data in case I wasn't really clear.
View 2 Replies
View Related
Dec 9, 2009
A B C D
COL 0
COL 0 1
COL 0
COL 0 1
COL 0
EDM 0
I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.
View 9 Replies
View Related
Jun 28, 2007
I have data in three columns. The first column contains the machine name (Robot 3, Robot 4, and Robot 5), the second column contains the paint color (1-20), and the third column contains the data that I need to sum. For example the data could be paint purges, the number of purges from each robot. Each row of data will contain a number of purges for each robot, for each color. The data is provided by day, so I will be summing the data weekly.
View 8 Replies
View Related
Jul 28, 2009
Maximum value based on multiple variables. Basically what I have is something like this:
View 3 Replies
View Related
Nov 28, 2012
I have a list of serial numbers. There are many groups of 8 same serial numbers, where group consists of 2 projects with 4 SN per project (because of 4 different events). I want to create a formula that marks one project with 4 out of 8 SN for deletion based on set of 3 dates assigned to them. In short I need to count 4 rows per project as one unit.
Serial
Project
Event description
Date1
Date2
Date3
Here is a list on what information matters when making decision if to mark project for deletion or not.
1. Project1 has no Dates entered compared to Project2. Mark Pr1
2. Project1 and Project2 has no Dates entered. Mark Pr1 (random, does not matter which should be removed)
3. Project1 has 2009 Dates, Project2 has 2011 Dates. Mark Pr1 because dates are older
4. Project1 has less Date entries filled than Project2 (same year). Mark Pr1 because less Date fields entered.
I can somewhat do it for separate rows, how I can make these rules apply for whole project as one unit related only to one SN at a time. Biggest problem is there is no pattern of dates entered. Sometimes one row can be filled another missing out info and etc.
View 1 Replies
View Related
Jan 14, 2014
I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:
Code:
Sub SummariseSheets()
'collates individual client development data from consultant
'worksheets and compiles in one sheet on summary page
[Code]....
The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.
View 2 Replies
View Related
Jun 21, 2006
I am trying to do a sumif based on two different columns. For example In col A I have part #s, in col B i have quantity, and in col C, I have the different representatives. I want to import the qty sold onto another worksheet. In the cell for the total number I want to know, how many of part# 401 were sold by rep A.
Here is the sheet that the information will be drawn from:
Part#QTYREP
4011A
4023A
4031B
401 1 A
Here is the sheet that the information will be output:
Rep
Part#AB
4012
4023
4031
View 5 Replies
View Related
Jan 24, 2014
What I am trying to do is calculate a particular charge based on two main variables the city and weight chosen (each of which will correspond to a particular value) .I have these cities and weights which correspond to a particular price per 100kg when two are selected
My excel data is set up like this
(A).....................(b)......................(c)..................(d)........................(e).........................(F)
.......................Weight..................0-999Kg.............1000-1999kg..........2000-4999kg..........5000-9999kg
1. City
2. Toronto....................................$5.50..................$4.00..................$3.75..................$3.50
3. Winnipeg....................................$3.20..................$3.75..................$3.6..................$3.21
4. Saskatoon..................................$5.4..................$5.2..................$4.90....................$4.70
(I added a,b,c,d and 1,2,3,4 for reference when explaining the formula)
I have a separate row where I am able to enter a weight in one column (A24) and and choose the city from a list in the second column (B24). I need the next column (C24) to display the dollar charge per 100kg from my data that corresponds to the city I have chosen and the weight entered. I am trying to figure out a formula for C24 that allows for instance $5.50 to be displayed when I type in 800kg for A24 and choose Toronto from a drop down list in B24. This row will look like this for reference:
..................A............................ B..............................C.
..................Weight..................City..................Charge per 100kg
24...............800..................Toronto..................$5.50
View 5 Replies
View Related
Sep 30, 2007
I just ordered vba for dummies, in the meantime I will have to get my info from the experts on this board. I want to use a name I have in a cell in a msgbox routine.
Currently I have
View 10 Replies
View Related
Oct 10, 2009
if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!
View 3 Replies
View Related
Jan 13, 2014
I have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below.
For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.
What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between
Code:
Sub BEOA4()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim FoundX As Range
Dim FirstFound As String
Dim lastrow As Long
[code]....
View 3 Replies
View Related
Nov 25, 2008
I have a worksheet ( adage inventory ) and I am trying to clean up some un-needed rows.
I need this deleting of rows based on a couple of variables.
I need to keep any row that Col F has the text "QCCONTROL"
and
I need to keep any row that COL N has the text "HOLD"
and
I need to keep any row that COL N has the text "REJECTED".
Sub Delete_OK_Lots()
lr = Sheets("adage inventory").Cells(Rows.Count, "A").End(xlUp).Row
For x = lr To 2 Step -1
If Sheets("adage inventory").Cells(x, "N") "HOLD" Or Sheets("adage inventory").Cells(x, "N") "REJECTED" Or Sheets("adage inventory").Cells(x, "F") "QCCONTROL" Then
Sheets("adage inventory").Rows(x).EntireRow.Delete
End If
Next x
End Sub
View 9 Replies
View Related
Oct 19, 2006
I have a project that looks at a table of train schedules for a range of dates. The data is formated so that arrivals and departures are on the same row but differnent columns. The dates are to the right and the grid is completed with the number of arrivals for a paticular day. I have created a formula that looks at the data and returns the arrival time for that date. It worked fine until the schedule was altered so that the same train could arrive at 2 different times on 2 different dates. So train 976 arrives at 4a on some days and 5a on others. In the example Ihave posted train 976 has 2 passengers arriving at 5a but the formula shows 4a. Is there a way I can have the formula return the correct time based on the day of the month? I should point out that the same problem could exist for departures.
View 8 Replies
View Related
Jun 6, 2007
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))
View 3 Replies
View Related
