Unique Job #'s By Date VBA Solution
Jan 20, 2009
I'm looking for a VBA solution that will return a list of unqiue job numbers in B by date, the below is some sample data in Sheet 1
Sheet 2 is the result I'm looking for and usually there will be muliple job mumbers for the same date and in this case I'm fine to leave it as a blank cell in sheet 2 for that day.
Unable to use advanved filtering as it's a data table thats changing all the time. It's verly likely that the same job numbers will be in multiple days....
View 11 Replies
ADVERTISEMENT
May 29, 2014
I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.
[Code] .....
Attached File : Staffing Report 1.90.xlsm
View 7 Replies
View Related
Aug 14, 2014
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx
View 5 Replies
View Related
Dec 9, 2009
Is it possible to have a formula that will return the answer as the value.
For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value.
I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution.
Would this require a macro?
View 5 Replies
View Related
Jan 30, 2010
my questions below are more oriented to hows & whys, as opposed to asking for a fast solution to a pressing problem.
I'm just reviewing the basics of SumProduct to be sure that I understand what I'm really doing when I deploy the function. So, I have the following formula:
View 3 Replies
View Related
Apr 23, 2009
I'm looking for a VBA solution that will look at the machine name above in blue and insert in in column E
Machine 2's expected results have been filled in....
View 9 Replies
View Related
Jan 24, 2010
I have an excel sheet which has been exported from an 'Employee Attendance Software',
http://www.flickr.com/photos/34247990@N06/4299288997/
In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow.
Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion
http://www.flickr.com/photos/34247990@N06/4299288959/
View 9 Replies
View Related
Feb 7, 2007
I am trying to solve a problem with = LOOKUP. Here's the data. A1 can have a value between 1-100.
IF A1 IS <80.00, NO MULTIPLICATION IS NEEDED
IF AI IS 80.01 TO 85.00, MULTIPLY A2 BY 0.37%
IF AI IS 85.01 TO 90.00, MULTIPLY A2 BY 0.52%
IF AI IS 95.01 TO 95.00, MULTIPLY A2 BY 0.87%
IF AI IS 95.01 TO 100.00, MULTIPLY A2 BY 1.07%
Is there a =LOOKUP formula that can be used here?
View 2 Replies
View Related
Oct 9, 2008
I have the new version of excel and I can't figure the correct formula to do the following.
Attached is my example.
A1 is available Inventory qty. C1:H1 are forcast qty's. I would like to have the available forcast qty. shown in black and the unavailable forcast qty. shown in red based on available inventory shown in A1
Example
So C1,D1 and E1 would be Black and F1 and G1 would be red. As inventory changes so would the colored numbers on the forcast.
View 14 Replies
View Related
Oct 3, 2008
I am using HLOOKUP to show a date depending on the month shown in B10.
If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "".
Here is my formula:
=HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE)
I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.
View 9 Replies
View Related
Mar 3, 2010
I would like to have the answer to a formula, say in cell G6, to show up in say G5. I notice in my college coarses, doing our homework through excel, the solutions are always in a cell around the entry cell.
View 9 Replies
View Related
Jan 12, 2007
I am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level:
I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red).
Here is my existing Keyword highlighting
Sub Highlight_Keywords()
Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long
View 9 Replies
View Related
Jan 28, 2009
My problem is that long text for the cell overlaps any blank cells to the right. I have more then 20.000 rows, so I would not like to put a space character in all the blank cells. Is there a way of formatting which will stop this? I don't want these texts to overlap the blank cells!
View 3 Replies
View Related
Dec 29, 2009
I have the attached table. I need to format with conditional formatting with the following criteria:
1) If >= 80% , then flag as green
2) If >=1% but <80%, then flag as yellow
3) If 0%, then flag as red.
I did the formatting in conditional formatting but the it did not results in the desired solution.
View 4 Replies
View Related
Jan 30, 2010
use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution:
http://www.excelforum.com/excel-gene...-new-post.html
I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
View 2 Replies
View Related
Feb 1, 2007
I need to determine federal tax withholding from gross pay.
If gross amount (for S-0 = single no dependents) is
Over.................But NOT Over............Then Subtract.........And multiply
$0.00...............$195.00.....................$5 1.......................10%
$195.00............$645.00.....................$99 .......................15%
$645.00............$1,482.00..................$351 ......................20%
$1,482.00..........$3,131.00..................$447 ......................25%
$3,131.00..........$6,763.00..................$849 .....................30%
If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?
I tried this one: =IF(M10
View 9 Replies
View Related
Jul 4, 2009
I have two columns of adjacent data Call them A & B (although this could differ)
If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.
View 9 Replies
View Related
Feb 9, 2010
Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.
View 9 Replies
View Related
Apr 4, 2006
I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
View 7 Replies
View Related
Aug 4, 2014
I'm needing to finding the latest date, of each unique ID (and Name, Criteria).
In my attachment ('Table'), the "Newest" column with the result of "Yes" is what my calculated result should be.
Any formula here (outside of a Pivot)? Because, I then need to delete all of my "No" results from the list.
View 3 Replies
View Related
Apr 7, 2009
I need is for the below code to not delete anything if the date is within a certain range. I want to keep all rows which have a date of 11 days past the current date. So for example. If today is 04/07/2009 then the macro should only delete unique values from 04/19/2009 and on.
Column A is the Item # and column D is expiration date.
View 3 Replies
View Related
Jun 25, 2014
I need an excel function to return the earliest and latest date for a particular/unique ID.
For example: In my sample workbook, the earliest date for all the columns with ID = 1 should return 11/1/2001 and the latest date should be 12/2/2011. If all the dates for a particular ID are the same, it should just return that date. For example, for ID = 2, all dates are 5/5/2010 so the earliest and latest dates should equal 5/5/2010. The earliest and latest dates have to be for each unique ID.
View 5 Replies
View Related
Feb 19, 2010
I have a table in a sheet and I want a formula that will count the unique records. The simplified database is as follows (in a table):
View 6 Replies
View Related
Oct 12, 2009
I'm scrambling my head to put this together. Could somebody show me how this is done?
I have two rows of data that contains dates in the mmm-yy format. The first row is the start date of the activity and the second row is the end date of the activity. Thus, in each column i would have an activity with its start and end dates. If i want to total the number of months taking into consideration only unique months (counting over-lapping months just once), how do i do that? These dates could have breaks in between, too. Like if an activity ended Jul-08, the next could begin Dec-08.
I am able to count the months using the datedif function, is there a match or other function that i need to use?
View 9 Replies
View Related
Apr 27, 2009
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
View 2 Replies
View Related
Mar 26, 2013
I have a column of dates (Column A) with intermittent blanks and I need to display the number of occurrences for each unique date.
This is what I am starting with: (Note= The number of rows varies by project so I need the whole Column A referenced)
Dates
02-24-2013
02-24-2013
12-15-2012
[Code].....
View 9 Replies
View Related
Jan 26, 2008
Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.
Goal: create a count of unique entries within a defined variable date range
I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.
I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.
It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
View 9 Replies
View Related
Aug 21, 2013
I have an issues with a formulae I have written to measure two factors;
A; How many different countries appear within a certain date range. B; What countries are they.
I tried a unique list, but could not get it to work within a range? I dont particuarly care how the data is pulled through as long as I do it. At the moment I have become stuck in 'Unique Ranges' and have n given up?
The formulae issue is in the tab "Problem_Formulae", the dat in the sheet 1, and the date ranges in sheet 2. Also, just to make life a bit more challenging pivot tables are a no go .
View 6 Replies
View Related
Oct 3, 2011
I am finding many posting on this topic with unique numeric values and have not come across one in regards to a text value. The essence of the formula is looking through a list for unique email addresses and now I need to up it to a date range and eventually a store # range
I am using the following array formula to establish an overall count:
=SUM(IF(FREQUENCY(IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""), IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""))>0,1))
Ctrl+Shift+Enter
The date column is E:E.
The store number column is G:G
View 3 Replies
View Related
May 19, 2014
File A has patients with discharge dates over two years. Each patient may have multiple dates. Example:
Patient Discharge Date
John Smith 7/1/2012
John Smith 10/1/2012
Judge Judy 7/1/2013
Judge Judy 12/1/2013
File B has office visit dates for the patients. Again, each patient has multiple office visits. I need to be able to see which office visits were within 15 days of a discharge date. Example:
Patient Office Visit
John Smith 6/1/2012
John Smith 6/15/2012
John Smith 7/10/2012
Judge Judy 7/20/2013
Judge Judy 12/12/2013
Is there any way to note next to the discharge date that for John Smith the 7/10/2012 office visit was within 15 days of the 7/1/2012 discharge? And the same for Judy with the 12/12/2013 visit?
View 8 Replies
View Related
