Excel 2003 :: Return First Value In Column That Meets Criteria

Apr 30, 2012

Using Excel 2003.

In column Y, I have a list of employee numbers (there are a random number of blank spaces in between each number)

In column AE, I have a list of clock in times (there are a random number of blank spaces in between each clock in time)

In column AY, I have a list of the same numbers from column Y (there are no blanks in this list)

Column BB. I am looking for a formula to place in cell BB2 and copy/paste down that will match the number in column Y and return the first non blank cell from that point in column AE.

Example:

10062 is employee number in cell AY2. Her employee number (10062) is also located in cell Y5. Her clock in time is located in cell AE7.

10099 is employee number in cell AY3. Her employee number (10099) is also located in cell Y14. Her clock in time is located in cell AE16.

What formula can I place in BB2 so that it returns the clock in time of employee 10062. Then copy and paste so that BB3 returns the clock in time of employee 10099?

Say I have a column of cells filled with numbers that are ascending. What I would like to do is to pick out the first cell that meets my condition. For instance, these cells contain irrational numbers that increase from 0 to 100. I would like a formula to pick out the first cell that exceeds 10.2, and to return the row number of that cell.

In the case of descending numbers, if I would like to pick out the first cell that goes below 10.2, would the formula be the same?

On sheet 1 column A and B. On sheets 2 I would like to make a seach tab - so if the people type part of the word or code it must return all the data from sheet 1 to sheet 2 that meets that criteria in full or part thereof. I have tried but can't get it to work, might it be that it is on 2(two) sheets ?

I do not understand code so I really do not know what to do where. Basically it should work as a Ctrl-F (find-all) function.

I am looking for a formula to place in C2 that will macth the number in column D with the number in column A and return the LAST date/time from column B that matches the day from cell C1.

If correct, C2 would return 01-02-2012 3:15 PM (Last date/time for 10055 on 1-2-2012) C3 would return 01-02-2012 9:00 PM (Last date/time for 10062 on 1-2-2012)

I need a formula that returns the Nth number from a single column that contains a small number of values within a span of 1000 cells. E.g., within 1000 cells there are 36 with numbers and the remaining cells are blank. I need to obtain the first occurrence of a number in the column, then the second, third... to the thirty-sixth. The end result would be a column with 36 numbers with no blanks in between without loosing the correct order of the numbers.

I have excel 2003 and have been tooling around with the index and lookup functions with no success.

In theory I know what I should do, but I don't know the syntax. So here it is:

There are 1450 unique records in my XLS, every record contains 45 different rows(these are the phases) with their position(1....45). Every row has a status (Not Started, In Progress, Complete)

COLA(uniqueid)....COLi(it is a number, it is the position).....COLN(status)

basically I would like to get the last "in progress", If not found, the last "Complete, If not found then the first "Not Started". and put a "Y" right next to the row to a new column for all the groups(45 rows)

I know if I use dmax for only where first column equals 13 I get 460 but how do I get the second highest value for only those rows that have 13 in the first column (expect the answer to be 268). Then I want to do the same for 3rd, 4th highest etc. I know large does it for one column and not only when the first column matches a designated criteria.

Is it posssible to use the xirr function with conditions. For example, an array formula that will only calculate xirr if a column meets a certain criteria or if the dates are within a range?

I am looking for a way of creating the following conditioned concatenation.

I have two tables, let's call them "summary" and "detailed".

The "detailed" table is something like the following:

ID VOL

001 01

001 05

[code]....

The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:

ID (unique) VOL (concatenated)

001 V01, V03, V05

002 V01, V04

003 V06

PS: I have people using this table with office 2003, so compatibility is necessary...

Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.

I need create a macro to perform the following - I don't know if it needs to be an Excel macro or via VB. This is to speed a process up and eliminate any spreadsheet messing around for our "admin" staff - they are not good with excel at all!

So, I have a daily/weekly down load of a spreadsheet (exported from access).

* I want to copy the values of column AK to A * Then delete all entries in column D that equal 19, 20 and 6 * Then delete all entries in column AL that equal "A","B","C" or "D" but not "E","F" or "G" * Then delete entire rows where the value in column A is a duplicate within the list

I will call this spreadsheet, for the purpose of this exercise, the "Master". I now have another spreadsheet with values in that should relate to values in either (or both) columns A and B in the "Master". I need Spreadsheet 2 values to be highlighted as cross checked in the "Master" and then all those in the "Master" that do not appear in the 2nd S/S will be normal white cells thus showing that they need to be chased by our Admin staff.

I have a rolling 12 month (each day in column) tab in Excel 2010 that references variances by ID number in column A, and the column headers are each day for 366 days (2011). I would like to create a tab that shows the past 10 day's variances by ID number. I haven't been able to write a formula that will look at the date headers and the row ID number to return the figure for that day. Here is a sample of the 12 month rolling and the 10 days at a glance that I want to populate.

Rolling 12 Months IDName12/25/201212/26/201212/27/201212/28/201212/29/2012and so on 1234Employee Name - - - - (11.07) 1235Employee Name - - - 0.20 - 1236Employee Name - - - - - 1237Employee Name - - - - (1.00)

In Excel 2003, I remember being able to tab across a row of cell entering data and when I got to the end, I'd hit enter and it would take me to the beginning of the next row. But in 2007, I can't...is there something I need to do in Excel Options or is this function gone?

So now, I want E19 of the table below to bring back column N of sheet 1, but only the last instance in which those Employee 1 and Client Assistance/Request met. In this example N7 of Sheet 2.

I have a table in A2:F200 and want to sum the entries in column C for those rows containing both an F in column E and an L in column F. I thought this would be easy but I cannot figure it out.

How many tickets are created and fixed by a user for a certain period. See below. I've tried countif but it doesn't seem to be working for many criteria.

I have a couple of issues and if its okay will post two threads to make it easier to follow due to my basic skills in Excel.

I have a workbook with 5 sheets. The first sheet is a stat sheet which picks up data from four other spreadsheets. I want to work out the average working days taken to complete a case but this data is across multiple sheets.

So for example Column E in each sheet tells you what type of case it is i.e. investigation. Then column T tells you number of workings day it took to complete the case. To work this out I have used the following formula:

I know its very basic but its the only way I know how to work out an average in Excel 2003. I need to add a second criteria where on every sheet it looks in column G for date the case was allocated. As I'm only looking to report in the current report year I want it to look for cases where the value in column G is =>01/04/14 but =<31/03/14.

I posted an earlier question about using a countif formula with two sets of criteria, Below is the formula I was given but for the life of me I can not get it to calculate. I have created a drop down box for each agent at my work and a drop down box for pass and fail. I want to keep a running total if they pass or fail on an assigment. I have labeled my worksheet phone.

I am having problems getting a formula to calculate the max if it meets 2 criteria. MAX of time listed by 1/past a certain date. 2/are of a specific client.

Here is my formula... {=MAX(IF(All_Tickets_07012014!$D$2:$D$155685>1/1/2014,IF(All_Tickets_07012014!$O$2:$O$155685=$A2,All_Tickets_07012014!$G$2:$G$155685)))}

The data... $D$2:$D$155685 - is the list of dates per ticket. $O$2:$O$155685=$A2 - Are a variety of clients, where $A2 is a name of one of the clients. $G$2:$G$155685 - Being a range of time stamps.

The formula runs, however it only shows the MAX of all entries, not within the specific date range...

Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"

I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.

I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.

I am setting up a template to use to track candidates throughout their lifecycle. I am also creating a 'summary report' on another worksheet that counts total number of candidates, counts the number of candidates that have been submitted to the client, count of the number of candidates that have been interviewed by the client, etc.

How do I count the number of candidates in the spreadsheet?

In Cells A6:B5, I have "Submitted" In Cells A7-?? I have dates for those candidates that have been submitted. Then a new section titled, "In Process" A list of candidates are contained within that section. Then a new section titled, "For Review" etc.

There are multiple sections, so the date in Column A1 may not always be in a constant row, as it depends on how many candidates are within a certain 'phase'.

I have a column I5, titled "Status" This column has a built in drop-down list that contains pre-defined status, for example, "Submitted", "Candidate Declined", "2nd Message Sent", etc. I need to be able to create a summary report based on a count of candidates that fit certain 'Statuses'. For example, count the number of candidates that were Interviewed. This could be a combination of Statuses: Phone screen with client, 1st F2F, 2nd F2F, etc.

(At home, I am using Excel 2003. At work, I am using Excel 2007. So if it's easier to create in Excel 2007, that's fine.)

s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate). Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).

In the attached Exel work book I have work sheets named

Material Usage – Usage of materials Estimate - calculate the Total material cost for a job Material Cost – defines the material cost for each material type

In “Estimate” worksheet Job number is repeated but sub jobs falling under a particular job number is unique. Materials used for each sub job is different.

Once the job number is selected from the list box , I need to calculate the total material cost for each job. I tried sumif function but I don’t know how to get it to look up for each material type and get the sum .

i am trying to get my excel formula to say if 2 date ranges in my excel workbook are the same (in seperate worksheets) to put a value from a certain cell into another cell in another worksheet. This is all in the same excel application...

The first step i have taken are:

1) if the 2 date ranges are the same to say 'true' if not 'false'

I am trying to get a forumula to say "if it is true to put a value from a different cell into another cell (both in different worksheets) but the same excel application...? but if its false to move on to the next date