Comparing Times & Sorting
Aug 13, 2009I have a column of different "finish" times (hh:mm:ss). I would like to have the adjacent column look at all of the "finish times" and post their ranking from 1st place to last.
View 9 Replies
ADVERTISEMENT
Comparing How Many Times One Cell Is Greater Than Another On Same Day
May 10, 2014I want to be able to count how many instances Primary Price is >= Price 2. And, secondarily how many times Price 1 was, at a minimum, >= to Price 2 and then also >= Price Price 3.
Ultimately I want to compare Primary to 100 or so "Prices" over a years time
I figure it would be a sumproduct function, but can't get it dialed in
Sorting Rows By Comparing Cells ...
Mar 13, 2007the system works by whoever has the most points wins.. i already have my formulas and everything done..
i want the excel program to move the entire row, if possible, by whoever has the most points to the top..
(i.e If ryan d. has the most points he will be is vince v.'s spot)
can this be done automatically in excel or do i have to do it by hand?
Comparing And Sorting Multiple Column Groups?
Feb 28, 2014I'm am trying to take multiple column-groups and sort them by product ID number.
I am trying to put together a sales record for the past 26 months for around 2000 different items and sorting them manually would obviously be a nightmare.
I would like to sort them so that all the drugs with the same NDC (prod ID) have the correct data for each month displayed on the correct row for that NDC. Right now I have each set of data for that month sorted by product ID number but some months we used certain products and others we did not. Is there any sort of Macro that I could use? What I am trying to do I attached two files. The "before" and my desired "after".
starting point.xlsx
end product.xlsx
Excel 2010 :: Sorting Multiple Times
Aug 27, 2013I have a sorting question in Excel 2010. Attached is an example workbook with a simplified version of the situation.
I want to sort a table multiple times. I have a table with part numbers and alphanumeric locations (Row, Shelf, Bay, Slot). I have formulas that divide up the location into 4 separate columns to be able to sort.
The first sort I do is by location, which I can easily achieve. The issue I have is sorting AGAIN by part number, while keeping the original sort somewhat intact. If a part number shows up multiple times (i.e. in two DIFFERENT locations), the Nth instance might show somewhere down the list.
Is there a way to sort my table to where you keep it in location order WHILE accounting for duplicates, which I would want grouped together in location order? Please see attached file : SortExample.xlsx
Macro Loop – Find Specific Times, Extract Date, Average Values Of Times
Feb 23, 2010In 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.....
Excel Formula To Calculate Difference In Times But Only Between Specific Times
Apr 8, 2014I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
Convert Start Times And End Times And Minus
Mar 7, 2009I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
Count Times Between 2 Specified Times
Sep 15, 2006I thought I found a formula that would work, but it's not working. Each month I have to count the number of service tickets that have arrived between certain time ranges. They want to gauge during what times we seem to get the biggest batch of service requests.
6 am to 10 am
10 am to 5 pm
5 pm to 6 pm
6 pm to 6 am
The format of the cells are:
1:21:19 AM
1:28:08 AM
1:35:48 AM
1:49:19 AM
2:17:02 AM
7:14:38 AM
7:29:12 AM
8:08:28 AM
8:51:48 AM
8:54:19 AM
The formula I tried for 10 am to 5 pm: =COUNTIF(B2:B677,">="&TIME(10,0,0))-COUNTIF(B2:B677,">"&TIME(17,0,0))
It gives a result of 676, and I know from manually counting that there is only 327 cells that have a time between 10 am and 5 pm.
Lookup Times Between 2 Specified Times
Jul 12, 2007I included a spreadsheet that lays out what I am looking to do, basically I copied some times and speed from net. It shows up in non 24 hour format. I need to find the times and an associated speed for each row (day) directly before and after my inputted desired time. I've searched for days, tried different formulas with index,match, lookups,timevalue, time, etc and even tried using other peoples vba code without success.
View 9 Replies View RelatedDetermine Times Between 2 Times
Sep 28, 2007i need to calculate between time. If a person is working between 8 till 12 and then 1 till 5 i need to count the instances between these times.. so if somebody is working between these hours a 1 should appear and if they have finished their shift or are on lunch then this should change to 0
e.g.
8.00 | 8.30 etc.. 12.30 | 13:00
1 | 1 | 0 | 1
Comparing 2 Timestamps?
Jan 22, 2014Attached is an example.
I get data pulls of logged in time, and shift start times.
Example in column C the person's actual login time is there, and column D has their start time. If there's a variance of 'x' how could I have column E display the variance? In this example 15 minutes?
Basically, if there's a difference between the 2, I need a formula that tells me what difference is.
Attached File : Login Time.xlsx‎
Comparing Two Tables
Jun 10, 2008I have one .xls file that contains an inventory list of all the products that I want.
Unfortunately this .xls file does not contain the prices for these items. I have another .xls file that contains SKU numbers and the prices related to those SKU numbers.
Is there a way to run every single SKU in the first file against the second file and, when a match is found, take the entry in the price column of the second file and place it in the first file so that my first .xls file contains all my products with prices?
I included sample data.
Comparing 2 Values.
Nov 5, 2008Comparing 2 values.
I need to compare the value's on 2 cells. I have a sheet setup for my sales I would like to compare each day of the week. So in other words I would like to compare this Monday with last Monday and know what the percentage of my sales ether up or down is.
Example:
Last Monday = sales $100.00
This Monday = Sales $200.00
100%
So If sales are up I would like the percentage amount font to be in green. But if sales are down I would like the percentage amount to be in red.
Comparing The Cells
Apr 6, 2009I have two spreadsheets I would like to compare against each other, last weeks inventory (Sheet 1) and this weeks (Sheet 2). All items are listed by unit number, is there a way to have all unit numbers on Sheet 1 colored red that are not on Sheet 2 and vice versa?
View 5 Replies View RelatedComparing Two Cells
Nov 16, 2009Is there a way to compare two cells and return true if they are the same, false if different? There is text and numerical data. As a side note, can anybody recommend a lightweight reference so I can sit for an evening or two and at least get an idea of what excel can do?
View 3 Replies View RelatedComparing Strings ..
Mar 30, 2009I have two string that I want too compare.
String 1 could be something like channel_0_user_defined._system
string 2 could be channel_0_user_defined
I want too compare string2 with string 1 at the left side of string 1.
I don't really know what I should use I found startswith, but vba doesn't have this particular function.
Comparing The Lists
Jun 9, 2009I have two Excel lists. One master list (list A) contains all our email addresses from our customers. The other list (list B) contains a list of people who do NOT want to recieve emails. How do I take the emails from list B (there are 1,200 of them) and compare them automatically to list A? Basically if any email address from list B appears in list A, I want it to delete in list A.
Comparing Value Between Worksheet
Sep 15, 2009i have two worksheets. group and search. in group sheet it contain are groups of club & nation. in sheet2 when click the button find it will prompt player name. for example,when i put torres it will tell us that he belong to Liverpool club & Spain.
View 5 Replies View RelatedComparing Then Adding
Sep 14, 2008say i have 4 columns of 5 values each consisting of 0's and 1's.
...a b c d
v 0 1 1 1
w 1 0 0 1
x 1 1 1 1
y 0 0 1 0
z 1 1 0 0
how would i write a function that would compare each value under column 'a' to each value under column 'b' and then return a value of 1 if they are equal, and return a value of 0 if they are not equal, then add up the return values.
(comparing column 'a' with 'b' you would get a result of 3.) then have the function continue on to compare column 'a' with column 'c'..'d; comparing column 'b'..'d', etc
so in long form: IF(av=bv,1,0) = 0
IF(aw=bw,1,0) = 0
IF(ax=bx,1,0) = 1
IF(ay=by,1,0) = 1
IF(az=bz,1,0) = 1
SUM(IFs) = 3
Comparing Two Lists?
Apr 3, 2012I have two lists of data and am trying to write a formula that will return a "1" if the second list firstly contains one or more entries that are not in the first list and secondly the fourth cell on the same row of one or more of the entries not in the first list contains an "F".
The first list is in a sheet called "Select" cells J2:J7 and the second list is in a sheet called "Model" cells A2:A50. The second list will occasionally contain blank cells.
I have tried to use a combination of COUNTIF and VLOOKUP but am struggling to make any progress.
Comparing Two Columns
May 5, 20141) if Column M has the value "School" then corresponding value in column N should have oly "Bus","Subject","Teacher". if values in Column B other than "Bus","Subject","Teacher" then the cell should populate RED Color.
2) if Column M has the value "College" then corresponding value in column N should have oly "Box","Madam","World". if values in Column B other than "Box","Madam","World" then the cell should populate RED Color.
Comparing Value To Three Groups
Mar 2, 2007Here's one:
Column C (starting at C6) contains the team number. I want column B to find out if the team number is an AM, Mid or PM team.
I have listed the AM teams at J1:U1, the Mid teams at J2:K2 and the PM teams at J3:V3.
How can the formula in column B search those ranges and determine if the team in column C is AM, Mid or PM?
Comparing 2 Reports
May 26, 2007Files :- [url]
i have upload 2 worksheets (in a zip file)
i have attached my 2 files,
1 is report of Feb 2007 & other is March 2007
on 1 Branch,
i got 200 Branches reports & i have to compare the people recored and have to identify following things
1) in which Days bukket is fall in Previous report ( compare with current)
2) if the Account # doesnot match then it should be HIGHLighted!
what i have to do is to compare the Account # with Previous & add a New Column & write the previous days (in number) plus highlight or show some remards that doesnot exisits in last report,
Comparing Days In VBA
Jun 29, 2007Using VBA, I would like to compare the date found in Cell(datRow, colIndex) of the Recap workBOOK to the date found in Cell(varRow, 3) of the Deduction workBOOK.
I loop through several Recap workbooks, one for each month. The problem I am encountering is this: If the date(s) found in the Recap workbook are before the date(one date) found in the Deduction workbook, the procedure will add the value of data below it to a running variable total.
However, what I am Needing, is for the value only to be added to the total IF the DAY of the date found in the Recap workbook (not the entire date) falls before the DAY of the date found in the Deduction workbook.
I know this is lengthy, but I have researched this for hours and am finding little to help me. Is there a function used in VBA to compare only the day of a date? And can I use a conditional IF statement such that execution happens when Cells(varRow, 3).Value > Cells(datRow, colIndex).Value even though the cells reside in different workbooks (both are open during processing)?
Comparing Two Worksheets ....
Sep 8, 2007have been trying to write a VB macro to compare two spreadsheets. Here is what I need help achieving....
Compare Sheet1 and Sheet2. Data exists in rows that have a unique identifier in one column.
If unique ID (with row of data) exists in Sheet1 and NOT Sheet2, add row from Sheet1 to Sheet3 and mark "REMOVE" in new column next to row.
If unique ID (with row of data) exists in Sheet2 and NOT in Sheet1, add row from Sheet2 to Sheet 3 and mark "ADD" in new column next to row.
If unique ID (with row of data) exists in both Sheet1 and Sheet2, take row from Sheet2 and place in Sheet3 and mark "UPDATE" in new column next to row.
Comparing Two Lists ..
Oct 1, 2007I have two lists in the same sheet. List one spread over 3 columns contains all the possible postcodes available to my job, the second list once again over 3 columns contains all the postcodes my job is currently using.
How can I find out the Postcodes that I am not currently using from the available list. I am sure it is a simple Lookup command, but cant for the life of me figure it out.
Comparing 2 Workbooks
Jan 24, 2008I have got 2 excel workbooks with multiple worksheets (around 6 megs in size). Is there a way i can do a quick comparison which will point out the differences between the cells?
there is a difference in dize, 200 KB
Comparing Two Lists
May 9, 2008I have a worksheet that contains six columns (A-F). I am trying to compare the values found in column F (~50 values) with the values found in column E (~1500 values). Both columns contain unique values. The majority of values in Column F are contained within Column E. The goal is to run a function that returns all rows in column E that contain a value found in Column F. I've been trying VLookups and what not, but my overall goal is to match up all values found in Column F with the appropriate row that contains the same value in Column E.
First Name
Last Name
Utility
Email
Account Number
Comparing The Ranges
Sep 25, 2008I have 3 sheets. Sheets 1 & 2 contain lists of data occupying columns A to M. Although the data on these sheets generally will be different, there are occasions where sheet 2 may contain identical rows to sheet 1.
What I want to do is two separate things -
copy the duplicated rows to sheet 3, and then in columns N & O indicate the row numbers from sheets 1 & 2 where the duplicates can be found (this only where the entire row matches)
do the same as above, but only where the contents of columns I & J match (rather than the entire row).