I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?
View 4 RepliesUsing Excel 2007
I have six colums (Date; Skill; ST Hrs; OT hrs: Area; Type). I copied and pasted below a small portion of info from the spreadsheet. I know when I copy and paste into a post the information under the headers don't line up exactly...it appears offset, so basically "Date" is of course the date you see..."Skill"'s columns has the first item as nb-mech b..."ST Hrs" first item will be 0.00..."OT Hrs" will be 1.50...."Area"'s first item is "Log FM NO"....and "Type"'s first item is MX07
And there are 17,000 rows of this info and forever growing...a lot of repeated items in each column such as you can see "blaster" has multiple entries on 4/1/2012.
What I am attempting to do which I have not firgured out yet (i have tried array formulas but they bog down the pc forver and couldn't figure out exactly how to make it give me the results I needed...and I also tried sumproducts but that didnt get me what I wanted....and it is being requested of me to NOT make a pivot table but a chart.
What I am trying to create is is the following:
1 - cells to plug in a date range for example cell A1 I plug 4/1/2012 and cell A2 plug 4/30/2012
2 - drop down boxes for columns Skill; Area: Type, which show only unique records in each column
Once I have that, I could then filter all 3 itmes and have only items show in the date ranges I picked and present ONLY the SUMS of columns ST Hrs and OT hrs.
Once the SUMS show up, I can then have cells off to the side that will give a percent break down of what percent of the total hours (for the chose criteria) are ST Hrs and what percent of the total are OT Hrs....and this percentage break down will show up in a Bar Chart, per day chosen in my date ranges....I'm thinking one simple stacked bar per day in the chart.
So say, based on the info below, I wanted to see the total ST Hrs and OT Hrs from 4-1-12 to 4-2-12 for a nb-blaster in LOG FM NO and type MX06.....my result would be 0.00 ST Hrs...23 OT Hrs.....and my bar chart would be one simple bar peaked out at 23 on day 4-1-12.....and within the bar it would be 0.00% ST Hrs and 100% OT Hrs and maybe the total hours for each showing also..don't need that but it may be useful.. ..and no bar for 4-2-12 (because the criteria picked had no hours for 4-2-12)
I have been messign witht is all week and just can't nail it.. I do remember a while ago I would have been able to load the spreadsheet into the post and people could see it in excel...is that option to do that no longer on this site?
DateSkillST HrsOT HrsAreaType
4/1/2012nb - mech b0.00 1.50 Log FM NOMX07
4/1/2012nb - scaffolding0.00 8.50 Bogs NOMX02
4/1/2012nb - coatings mech b0.00 3.00 Log FM NOMX07
[Code].....
I have a big DB with several rows and columns.
Column B has several card numbers (e.i. 7987654345678)
Column C has a location name (e.i. madrid)
Column L has a date (the date is when the card was used)
What I want to do is identify the cards that appear more then twice on the same date and the same place.
Card Number
Location
Date
123
LOC
18/01/2013
[Code] .........
I would like through a macro or pivot to show me the results of the 2 last rows, because those are the only ones that are exactly the same.
I have excel 2007 running on Windows7
I have set of data in excel which is basically a table of workload and I need to be able to select someones name from a dropdown list and all the other names are removed.
I've attached a screen shot of an example, I want to be able to select any name from a dropdown e.g. Select Trevor and then only cells which contain Trevor are shown (i.e. the other names disappear). BUT all the jobs 1-10 and Stage 1-3 have to stay even if Trevor doesn't work on them.
Screen Shot 2013-10-15 at 16.44.12.png
I am using Excel 2007
I have 50 000+ rows that covers fiscal years from 2006 to 2014 and I want to calculate how many unique clients are there per asset type per fiscal year. In addition, the results table needs to adjust to filters i would use on the raw data..
As:
2007
2008
2008
Commodity
45
FX
Other
other
Here is a sample file: SampleData.xls
I would like to get the count of unique values in my 2nd column using my values in 1st column as the criteria. Below is my example of my data set.
EFGH5DeptHC
6A304794A17B86122B38B86179C39B90050D1310C82705
11C94955
12C308165
[Code] .........
Excel 2007
I would like to see the count on column H (highlighted in yellow).
[Excel 2007]. I'm trying to get the list of unique values from a Filter on a column in VBA.
I've given the column a Range Name
Code:
Dim rClient As Range
Set rClient = ws.Range("CLIENT_NAME")
Dim aClients() As Variant
aClients = rClient.???
There's nothing in the range method dropdown that obviously relates to the Filter on the range.
Is it something to do with the Table method?
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View RelatedBasically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.
What I want to do is look down Sheet OEE V20:V500 and get two lots of information -
The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.
I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.
I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.
Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
I have a workbook containing several worksheets. I use one worksheet to collate information from the others. I do this by referencing the relevant cells I need from the other worksheets with the '=' command.
When this displays it shows as a '0' if the original cell is blank. Is it possible for this to show as a blank unless there is any data. I have tried the ISERROR function but it still leaves the entry as a '0'.
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
I have three search boxes in XL , two are search from and to dates and the other is search be ref.
I can currently search by dates and ref but would like to show all records in the specified dates if the ref field is left blank.
I am using the query writer and referencing back to cells in XL. I don't use the wizard or SQL. I have tried many options and think I should be using a OR statement with a wildcard but can't seem to get the result, does the wildcard just show the blank cells in the actual data?
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies View RelatedI have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
Can DCOUNTA be used to only count unique values?
If the above is not possible how would I do the following:
Criteria
Column H = text1 AND Column B date is between date1 & date2
Count unique values in column C
I need cell d4 to display the number in column b next to the applicable insurance company when the insurance company is selected from the drop down menu in cell c4. How do I do this?
View 4 Replies View RelatedFor input I have 2 columns:
A---B
1---5
3---4
-----
2----
I need for output column C:
C
1
2
3
4
5
It's possible to do that using one or more function ?
2013
Current Year
Q1-13
Q2-13
Q3-13
Q4-13
Totals
Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00
[code].....
I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.
I'm using Xl 07 and need to combine two sheets of contact info and have them sorted alphabetically.
Each contact entry takes the follwing form(comma's denote new columns)
55555, Name , John, Smith, Tel: , 555-555-5555
ID , Company, XXX , , Email, xxxxxx
Each sheet has a list of 200 or so entries about 400 rows long. The entries on each sheet are sorted in alphabetical order. the names also alternate alphabetically from sheet to sheet. So when i look at the entries on my final sheet, the 1st will be from sheet1 the second from sheet2 the third from sheet1 and the fourth from sheet2 etc.
I'm not sure whether I should build a new list by pulling alternating entries from each sheet or just copy one list below the other and then try to sort it somehow.
I have a Excel Table, everytime I type a new record in the last row all the fields above that has drop-downs and formulas get's carried over and displays, except for one field which never tends to display a drop-down. I end up having to copy and paste the drop-down from the above row. I don't like to do this.
View 1 Replies View RelatedI have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.
View 1 Replies View RelatedI am been trying to do a drop down only with coloring in Excel 2007, need not want any text to be the drop down list instead need a color specific drop down list, so that i could type a free text in drop down, which would enable to identify the content through coloring.
View 7 Replies View RelatedI have some data validation drop down lists in excel, I can tab through all the lists but I have to press alt + down arrow to show the list, Is their a way it can be automatically shown as soon as I focus on the tab.
Check the attachment: Survey.xlsx
Using Excel 2007.
I setup a named formula: Spread1 =Sheet1!$E$2:$BE$2 where I entered % values to spread a an annualized figure over 53 weeks
On Sheet 2 I entered the amount to spread in $D$2 and in $E.. I entered my formula: =Spread1 * $D$2. Everything appeared great!
But then I deleted a few columns on sheet2 so first calculation is now in $G$2
Spread1: =Sheet1!$E$2:$BE$2
Calculations: Sheet2! $G2:$BG$2 (the values in I,J,k while summing to the total are in the incorrect weeks based on the spread on Sheet1, BF and BG have #VALUE! errors)
Seems odd, if I highlight "Spread1" in the formula bar and [F9] I see an array of 53 possible values and the 3 values I expect are in the place in the array (elements 5,6,7).
How I can defeat it with the possibility that a user may delete a column?
I have the equivalent of Vlookup code(I couldn't get vlookup to work correctly) I am using Excel 2007. I have an array 2 cols x 1652 rows. I have another column of 6264 items. If a text item in the first column of the array matches a text item (minus 3 characters) in the longer column, I place the matching row from the array next to the item in the longer column. Whats not working is that the text never gets put in the cell.
Code:
Sub Macro1()
'
' Macro1 Macro
[Code]....
I created a data validation formula for an Excel 2010 workbook that would only display client names who were assigned to the manager that they selected. The formula works fine in Excel 2010. However, when I open the same workbook in Excel 2007, the data validation type is reset to "Any value" and the formula is missing.
OFFSET is an available command in both 2007 and 2010. When I try to add the formula as a data validation list in 2007, an error indicates that Offset cannot access data in a different table. I have not been able to find any documentation which restricts the OFFSET command in Excel 2007 but there seems to be some incompatibility between the two Excel versions.
The formula I used was:
=OFFSET(Clients!$A$1,MATCH(Summary!$A$1,Clients!$A:$A,0)-1,1,COUNTIF(Clients!$A:$A,Summary!$A$1),1)
Where:
"Clients" is a sheet which lists all clients under each manager."Summary" is a separate sheet which contains the manager's name. Only clients matching this name will be available in the employee's client list.There are 50 "employee" sheets, each which should use this formula for data validation in the client column.
The error we received seemed to indicate that the above command could not access data on another worksheet. It is not an option for me to copy the client list onto each sheet that uses this formula. Are there any other options?
=COUNT(
1/FREQUENCY(
MATCH($A$1:$A$50,$A$1:$A$50,0)
),
ROW($A$1:$A$50)-ROW(OFFSET($A$1:$A$50,0,0,1,1))
+1))
I'm looking to tweak this formula to count the number of unique values that occur at least 3 times.
So, imagine that A1:A50 contain 50 last names from the phone book, 27 of which are unique according to the formula above.
Now, further imagine that only 5 of these unique 27 last names appear at least 3 times.
I'm looking for the formula that returns 5.
And (like always), for simplicity and sharing reasons, I'm hoping to find a single formula that occupies a single cell, and does not rely on additional columns of intermediate information.
I need to count the # of unique employee #s based on the criteria in column B. If the value in column B=0 then I do not want to count the employee #.
I realize a pivot table would be much easier but I need to show each line which includes other data.
i want to make a dropdown list from excel 2007. I try data validation then allow then list then source but i cannot make the sheet 2 as the source of my dropdown list in sheet 1. i uses excel 2007 and my OS is XP.
View 4 Replies View Related