I'm new to the forum - I've done a search but cannot find the answer to my problem.
I work in a hospital and have a list of consultants who refer for a particular type of scan over a period of time.
Each time the consultant refers their name is added to a list so I get a list like this:
Dr Smith
Dr Smith
Dr Jones
Dr Smith
Dr Paeker
Dr Paeker
Dr Jones
etc...
I have a list of names in B8:B200 (unique)
I have another list of names in I7:I15
I want to count how many names in B8:B200 that matches any of the names listed in I7:I15.
I have a table of data, from which I have created lists for “items created within a period” (the items are paintings on canvasses).
Each list displays which canvasses were completed within 2 dates.
My main data table is large (over 1000 lines and growing) and I need to extract the total quantity of each canvas size that appears in each period list.
My sample attached shows sample data, with corresponding lists of canvasses created within each time period, together with the result I am looking for (shown in red).
is there a formula that gives a true or false statement upon searching a list (ie A2:A6) and finding multiple entries? like, when i search A2:A6 and there are two fields that contain the word "hello", is there a function to say "true, there are multiple entries of the word 'hello'"
View 2 Replies View RelatedI have a sheet with a top to bottom date/chronological list of Horse races arranged so;
Column 1 Column 2 Column3 Column4
Date/Time Race type Empty Horse Name
e.g
01/03/2008 12:30:00 1m stks Mad Robber
01/03/2008 12:30:00 1m stks Don't Run
01/03/2008 12:30:00 1m stks Laughing boy
etc
Then next race
i'm looking for a Macro that will add up the number of horses in each race and enter the number for a given race in Column 3 for each horse in that race. So the example above would become;
01/03/2008 12:30:00 1m stks 3 Mad Robber
01/03/2008 12:30:00 1m stks 3 Don't Run
01/03/2008 12:30:00 1m stks 3 Laughing boy
Then next race down the list;
01/03/2008 12:40:00 2m gtd 1 Walk fast
etc
I have a worksheet with an inventory of items in cells in column A. I need to count the number of items listed in the cells in column B. I have data in about 1500 rows. The items in the list in each cell are separated with a space. Please see small sample below. I would normally do Text-To-Columns, but some of the cells contain up to 30 items.
Excel 2007
A
B
1
Grouping
Number
2
[code]....
I have data in range A2:E5000. Column D contains cost centers entered by users.
I also have a list of valid cost centers in range K1:K10.
What would be the vba code to check and return the count of the items entered in column D which are not valid, based on the list in range K1:K10?
I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?
View 6 Replies View RelatedI need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.
What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.
I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.
I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.
It's a quarterly schedule for utility meter readers, divided up into days across the top, and routes/areas down the side.
There's a space with each route for the employee ID to go, depending on who's doing it.
Down the bottom of the sheet, there's a few empty lines for all the available employees who haven't been allocated to a route.
On the second sheet labelled EMP LIST, is all the employee IDs. It's also got the areas they work- it's for something I want to implement later.
I can put all the employees into a single column list, then use basic DV to give a dropdown menu by each route, to assign an employee to it. This is pretty straight forward. I've also been playing with this in combination with a countif, so that employees already assigned do not appear in the list.
This is about as far as I've managed to get with it.
I need to get the employees who aren't assigned to anything to appear in a list below the routes. I'm fairly sure this is just another countif, but I'm not 100% on the exact formulas.
I also need the list in the dropdown menu to reset for each day of the quarter, i.e. if I assign an employee to a route on the 30th day of the quarter, their ID will disappear from the list for any other route for that day, but will still be there for the other days of the quarter.
So far the only way I've managed to do this is by duplicating the employee list for every day of the quarter, which is going to get very cluttered, and also makes staffing changes difficult. I'm hoping there's an easier way to do this using one data set.
I've added an example of the sheet. It's one week and it's only got about 1/3 of the routes we would actually have on any given day.
scheduledemo.xlsx
I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.
View 1 Replies View RelatedI Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies View RelatedI have a list of patients with different categories. Count values of HBA1c which are less than 6.5 and those greater than 6.5 ONLY and only for those of the Category named "PreGDM" but not for other categories.
View 14 Replies View RelatedI have a workbook (attached).
I want to count the number of times North AND Win, North AND Lose etc.
Can anyone provide me a way of doing this. I dont want to have to use B2&C2 for example. I know I could do this and then use an IF statement (I think).
I have 2 column: item and quantity. I need to count number of items. In column "a" some item are more than one time. I need something like SUMIF function but by using vba. open attached file
View 3 Replies View Related1. I need to count how many different items within one column, these are formatted as Text. cells G63:G1600
Sample text:
F41D6
F44DD
FABHD
FAXHD
FDTGD
FFDGD
FFTED
FGDFD...................
2. I need to count how many dates fall during the week using the ending dates. Weeks ending 1/06/07, 01/13/07,etc L2:L53, the dates to count are L63:1600.
Sample Text:
09/05/07
09/21/07
09/07/07
09/17/07
09/21/07
09/05/07
10/03/07
09/13/07
09/12/07
09/18/07
09/18/07.............
I need a quick way to count the number of items in a column
For example
COLUMN A COLUMN B
apples 3
oranges
pears
bicycles 4
cars
trains
buses
There are over over 17.000 rows
Counting unique items in column (numbers)
View 9 Replies View RelatedI have a two massive spread sheets in which I am comparing items from one with the other and changing in one if the other doesn't match the first. I already have reduced the processing time from 6 hours to about 2 minutes by rewriting the macro to use autofilter to make the changes. The problem I have now is that I need to keep some metrics on the changes i.e. when I apply an autofilter, I need to be able to assign a variable to the number of items actually shown after autofiltering so I can add it to a variable that is keeping track of total number of changes. I know that outside of VBA, in regular excel use, the status bar displays # of total# found during autofilter, so is there a way to tap into the program and pull that number during execution of a macro?
View 9 Replies View RelatedI am working with VB 6.0. So far I have been able to create my recordset, opened excel spreadsheet and imported data to the spreadsheet using the recordset. The spreadsheet has five fields and each field has multiple items as in the example below.
Name:Pass#:Amount:
John:22222:500.00
John:22222:500.00
John:22222:500.00
John:22222:500.00
------------------
------------------
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
------------------
------------------
What code can I use to count all the occurences of "John", "Jane" in the spreadsheet?
I need to make a pivot table with data from 2 columns. The data has columns named Unit Type and Name. There are 9 different unit types and over a hundred different names. I need to find a way to make a table that counts however many times the name VACANT shows up for each unit type. Is this possible using a pivot table?
View 3 Replies View RelatedFor each of my staff I need to count if they worked 15 or more days in the 30 days preceding a statutory holiday. (As a follow on problem... If they have worked 15+ shifts then I'll need to calculate the sum of their hours over the 30 days / 30).
I tried the functions below but they didn't work. (the Name1 column records the number of hours worked that day's shift) The actual count in my test case is 5.
=COUNTIFS([stat],"=STAT",[Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0") result: "0"
=COUNTIFS([@stat],"=STAT",[Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0") result: #VALUE!
=IF([@stat]="STAT",COUNTIFS([Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0"),"NO") result: "0"
I have this work sheet with several formulas in columns Z to AD. All of them highlighted red work fine as for as I can tell. I am stumped with the one needed for the cell highlighted yellow AD2. It should count all the dates in AD1 that are Requested Changes Made and/or Rejected in Column "M". AD2 is a total of today minus 8. Equipment Change out - TEST.xlsx
View 5 Replies View RelatedCan someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).
Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)
I need a formula to count the total items per unique id. For example
Column A Column B
11111 Basketball
11111 Basketball
11111 Basketball
33333 Baseball
33333 Baseball
22222 Hockey
Output to Column C,
ID 11111=3 Basketball
ID 33333 2 Baseball
ID 22222 1 Hockey
I would like to count items that appear in a list that will also appear in several rows. The list is also in no particular order. I have attached an example which may make it clearer than a wordy explanation.
View 2 Replies View RelatedI have a table lists producted items and quantities per weekdays as follows:
Weekday
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Produced item
Yellow
Yellow
Yellow
Red
Green
Blue
Yellow
Amount
1200
300
790
275
942
612
421
What I need is: If I specify the weekday; to start from; and number of days, then I should get the total productivity by item..
For example:
Tue (i.e. starting weekday)
3 (i.e. number of days)
Results:
1090 - Yellow
275 - Red