I have a list of product IDs in one col. In another 2, I have date and vendor that each items was sent. Note that each items can be sent several time to different ven for at different date. From this list I would like to build another list that shows me WHEN & WHERE each item was last sent.
I was able to find a formula to return a list of unique item for my product IDs, but get stuck on the next step.
I need a formula that links to another workbook where it looks in a column for the oldest date which appears more than 10 times.
So if the oldest date was 01/01/2006 but it only appeared twice, it would ignore it, but if there was then 02/02/06 which appeared 11 times, it qould quote that date in a cell in the workbook
I have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........
Is there a way to select a table and have excel produce a list ranking the #
of times each item shows up in the list, i.e.
dog 5
cat 2
bird 1
?
If I have 1,000 entries in a column is there an easy way to determine the number of times a certain item is in the columns. For example if field A1 is "qwerty" I wan to see how many times "qwerty" or field A1 appears in the entire column?
View 2 Replies View RelatedIn my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
compare one coloum with another and return a corresponding value in the adjacent cell. For example
I have 4 colums A,B,C,D. In "column A" I enter Products names (Example- Apple, Orange..)and in corresponding Coloumn B, I enter Product Codes (01 for Apple and 02 for Orange etc..) When I enter Apple in coloumn C for 10 rows (C1 thru C10), I need to get Code 01 in Column D in all rows (D1 thru D10)
I need to search from a multiple column and return its corresponding outcome . Make it simple,
Price Fruit Vegetable
5 Apple Tomato
12 Banana Carrot
29 Watermelon ginger
what i need is a formula where when i key in either the fruit name OR the Vege name, its price will shown on a cell.
I try with Vlookup..but as I am new and i failed when i try 3 x 3 range.
I am looking to return a sequential list of items which match to an element of text that I have selected. An example will explain what I want better.
Item Selected Tom
Ref AmountTom C 32206 3683Tom B 36212 34464Frank 10552 43642Mary 13228 30534Tom A 30694 35798Mary B 9121 27489
So I want a Formula which will return the Reference for items in column A which contain the word Tom.So the Result I want is:Selected
Tom C 32206 3683Tom B 36212 34464Tom A 30694 35798
The formula I have to return an item containing Tom is:
=INDEX($A$5:$A$10,MATCH("*"&$B$2&"*",$A$5:$A$10,0))
But this only returns the first Tom and no other.The Formula I have to return sequential items with exact reference:
=INDEX($B$4:$B$11, SMALL(INDEX(($B$2=$A$4:$A$11)*(MATCH(ROW($A$4:$A$11), ROW($A$4:$A$11)))+($B$2$A$4:$A$11)*1048577, 0, 0), ROW($A1)))
This will (if I change the row reference at the end) return all Tom C's (If I change B2 to "Tom C") in sequential order but only the Tom C's and not the other Tom's (Tom B and Tom A). I have tried replacing the $B$2 with "*"&$B$2&"*" but that doesn't seem to work.
I want to get the value for the selected item from the combo box in excel. I don't know very much excel but I want do this forlmular.
See attachment. I want in this box real value…. If I choose livingroom I want to show value of livingroom = 20
I am trying to see if there is a way using count or another option, of using one formula to look in a column and then spit out how many of each item are there:
i.e: lets say it says a, b, c, d etc...
Can a formula go through and say you have 3 of a, 5 of b and so on
I understand the basic of count sum iserror etc, just not sure if excel can do what I am wanting or if i should use access.
i need a formula in Cell B2, that will look in raw data and then look for the team in criteria (named range) then return the number of item for each manager....
View 9 Replies View RelatedI have a spreadsheet!
I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).
What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.
Example:
Sheet 1 - Films
How To Apply Bridal Makeup
How To Fight A Donkey
How To Write Excel Formulae
Sheet 2 - Tags
Channel / Tag / Keywords
Tech - Microsoft - windows vista xp word excel
Tech - Computers - internet pc
Tech - MP3 Players - iPod Zune
So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.
DONNAS [Single cell, 1st row] 09/08/2003 15:34 [Single cell ( date and time combined) 2nd row]. Do you know of any way that I can move the name, date and time from the above cells to three separate columns on the same row?
View 4 Replies View RelatedI have to work out the time difference between 2 times over a 24 hour period.
There is a fixed booked departure time and an actual time of departure, the actual time of departure can be either before or after the booked time.
I need the time difference between the times shown as either +/- minutes at present it returns the time in [HH:MM].
I have a simple IF formula to work out - time but when it calculate + time it returns the reverse difference.
A= booked time
B= actual time
I have search and searched the forums, and maybe am not entering the right info to get an answer, BUT I'm trying to do the following and have no idea where to begin... Workbook2: go to Workbook1, lookup c4:n43 and enter the cell value1 (if any) into I4, Workbook2
THEN
In the row the value1 was found, go to column A, retrieve it's value and return it to D4, Workbook2
THEN
Look at row 1 Workbook1, if the column that value1 was found = Jun-Aug then return 01/2008 to B4, Workbook2.
Of course I need this to go down line by line in Workbook2 to fill in all the info.
I'm trying to use countif or similar to show how many times a given date is between 2 dates (inclusive). Each row of the spreadsheet contains amongst other info, a Start Date and an End Date. If i've got 2 rows like this:
StartDate EndDate
01/01/2009 03/01/2009
02/01/2009 04/01/2009
I'd want to lookup 02/01/2009 and have it return 2 (02/01/09 falls between the dates on row 1 and the dates on row 2). To give some context, this is an export from a database containing staff holiday dates, and I'd like to be able to see how many people will be on holiday on any given date, given the start and end date of all holiday occurances throughout the year.
I'm trying to do a countif that matches 2 names and returns the amount of times the 2 name matches is in the list. Ihave first names in column a and the surnames in column B.
And there are lots like so
John
Greenwood
Bill
Griffiths
Melanie
Griffiths
I have a spreadsheet which is used for tracking work items for a team of people. On one sheet I have a column of dates and rows of named 'tasks' against them. I need to identify the first and last dates at which a 'task' is listed.
My spreadsheet is attached.
On sheet2 K2 I want to see the first / earliest date where 'Fish' occurs on sheet1
On sheet2 L2 I want to see the last / latest date where 'Fish' occurs on sheet1
I have tried using SUMPRODUCT MAX / MIN but I got myself in a muddle!!
TrackerRH.xlsx‎
I have a formula that I use to tally data and return the number of times a particular value is present in column C.
IF(COUNTIF($C$2:C2,C2)=1,SUMIF(C:C,C2,E:E),"")
I copy this formula down the sheet until arriving at the last input in column "C".
Can someone tell me how to write this in code. All I would like visible in Column "F" is the output not the formula.
I have created a userdefined function. the Idea is for the user to calculate the difference in hours and minutes. The input would be like budtime = 30 , realtime = 1.25 the answer I would like to have is 28:45 (28hours and 45 mins). Im aware that I can format the cell manually with [h]:mm but I'd like the function to do it.
I have written this:
Function RT(BudTime, RealTime)
RT = Format(((CDec(BudTime) - CDec(RealTime) / 24)), "[h]:mm")
End Function
I am trying to make a summary from a table showing the newest value available for a set of items(1,2,3,4), this is according to an investigation date the summary table shoul display only the values for the investigation date, but if there are no values for the investigation date, them should take the previous recent date from the investigation date, and if there is not value at all, then shown N/A
I am attaching the excel sheet for a better ilustration of the problem
I tryed a combination of formulas that I tooked from other threads but is not working properly.
Excel Userform
VB:
'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected)
Private Sub ListBox1_Click()
ListBox2.ListIndex = ListBox1.ListIndex
End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
Book1.xls
Here is some dummy data and explanation with my problem...
I can't use usuall sumproduct because array of date doesn't match with data array... Since names are in alphabeticaly order you can see that Dave is sometime at first, second, third position.. there is more names etc...
I need to see how many times some name appear in last x time...
I have a spread sheet for equipment that gets loaned out on a regular basis. Cells B4 - B25 have combo boxes in them, with the options to select "On Loan", "In Stock Room" etc in them. What I would like to do is create something so that as the combo box is changed, i.e. from "In Stock" to "On Loan", a date changes adjacent to each combo box.
For example, combo box in B10 has been changed from "In Stock" to "On Loan". The adjacent cell records the date automatically that the combo box was changed.
Combo box date example.jpg
In cell A2 I have the date 28/12/11, I need that date to repeat 6 times, then in A8 it should say 01/03/11 6 times and so on , I know this can be done but my brain is on a go slow this afternoon and I can't for the life of me work it out
View 3 Replies View RelatedI have a column that can be found in the example attachment. My primary question is, how would I go about filtering out all of the Excel Dates & Times that do not have a multiple of 5 minutes in the "Timestamp". Can this be done w/ out the use of macros by use of the custom filter? My ultimate goal is averaging every hours worth of data so it would rather be hourly data rather than the 5-minute data given. More specifically I would be averaging the "Flow" data. The example given is actually a very small portion of what I am working with. I actually have a years worth of 5 minute data.
View 6 Replies View RelatedI am recording events on a day to day basis in excel using the first column as the date, second column as start time, and third column as end time. So an entry might look like 1-Jan _ 13:00 _ 13:40, in the three columns across. There will be multiple entries for each date, but the number of entries for each date will vary. Entries may also overlap in times. For example, the next row may read 1-Jan _ 13:10 _ 13:45. Some times there will be gaps such as a third row readng 1-Jan _ 14:15 _ 15:00. What I would like to do is compute the total elapsed time spent on projects for each day, not counting any overlaps. SO even though the total time spent on those three projects for January 1st is 120 minutes, I would like to know how much time during the day is spent doing work, regardless of how many projects are being done at the same time. So I would like excel to be able to tell me that on January 1st , 90 minutes of the day was spent on work. Hopefully this makes sense. I also need it to distinguish between dates so that it can tell me how much time was spent on January 1st. then January 2nd, 3rd and so on. It would be great if this ould be computed as the data is entered. Maybe It could record it on a new sheet with a column for each date and then it lists the time spent on that day in the row below it.
View 9 Replies View RelatedI am looking to total a sheet as follows into a header row - Is it possible?
A - B - C
1)Date - Estimated Time - Actual Time
2)
3)7/27/07 - 2 - 4
4)7/27/07 - 3 - 4
5)7/27/07 - 2 - 5
6)7/28/07 - 2 - 4
7)7/28/07 - 8 - 6
8)7/28/07 - .25 - .4
9)7/28/07 - 1 - .5
I would like B2 to read "7/27/07 - 7hrs, 7/28/07 - 11.25hrs"
and B3 to read "7/27/07 - 13hrs, 7/28/07 - 10.9hrs"
numberdesc
1_______yellow
1_______yellow
1_______blue
2_______purple
2_______purple
3_______green
3_______orange
4_______black
I need some way that can identify when the item in the description column doesnt match the first item of the same number- for example, here the 1-blue and 3-orange would be flagged because they should match the 1-yellow and 3-green.
I need to do this on a much larger scale (approximately 20,000 data points), so I wanted to create a formula or macro that could do this for me.. I thought making a reference page with would work but I keep getting an error.. I haven't done VBA in a while, so I may have syntax errors.
If Range("A2:A9").Sheets("Sheet1") = Range("A2:A6").Sheets("Ref") And Range("B2:B9").Sheets("Sheet1") = Range("B2:B6").Sheets("Ref") Then
Range("C2:C9").Sheets("Sheet1") = "x"
End If