I have a column containing text values eg M1, T2, M3, A4 etc. and I am trying to return the value (numeric) in a second column to a cell when the text value in the first column is matched.
View 4 RepliesI have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
View 4 Replies View RelatedI have two separate workbooks, each with a great deal of information, such as name, ssn, dates, statuses, etc. I would like to compare the SSN column for both workbooks, and when it finds a match (which there would be many) to copy the MOS cell from the same row to the other workbook. Here is my breakdown with names:
Workbook 1: Macros.xls
Workbook 2: MASS 162.xls
The SSN column is column C in both workbooks
When there is a match found between the SSN column in Macros.xls and the SSN column in MASS 162.xls, I need to copy the MOS cell (column E) from the MACROS.xls workbook to the MASS 162.xls workbook into the same row, but in column M, which is currently empty.
4 columns a-d. now if i find either 1 of the 2 particular strings "hello" or "bye" in column A, I want to move column b into its place and leave the rest of the columns untouched
Example:
dog, circle, blue, 123
hello, frog, green,223
pig, triangle, red, 445
bye, elephant, grey, 322
into
dog, circle, blue, 123
frog, blank, green, 223
pig, triangle, red, 445
elephant, blank, grey, 322
I have data (coming straight out of another system, so whilst is seems messy, its the easiest way to have it in Excel).... where there are a number of rows (one for each employee) - then every column represents a date in the month (so there are about 30 columns)....the cells contain a number of hours worked by that employee for that day.
I have figured out how many hours they worked for each day of the week, but my client now wants to know:
for each employee - how many Mondays did they work?- how many Tuesdays? and so on
I am happy to figure out the day of the week for the first date in the month and then manual set up another column heading above the date that tells me what day it is - but I feel that Excel may actually be able to work out what day of the month it is - although the dates come across from the other system as text - April 26, 2006 etc...
So I would like a summary in my sheet that has column headings
Mon, Tues, Wed, Thurs, Fri, Sat, Sun
and then for each row for each employee - I would like the number of days that employee worked for the month by these Days of the Week.
I have looked at Count if - but I need to be able to select the range of cells to count, and also the range of cells to evaluate for the criteria and I cant figure it out!
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
I have a produced an Excel workbook which uses a VBA sign in/out userform.
Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave.
To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8.
I need to have the row number returned for the IDnumber where the Out cell is blank.
This should be the last occurrance of the IDnumber
Example
Row
1 ID# In Out
2 1 9:00 13:00
3 4 9:00 13:00
4 3 9:00 13:00
5 2 9:00 13:00
6 5 9:00 13:00
7 6 9:00 13:00
8 2 14:00
I've been looking through past threads and I can't find what I need to do anywhere. There was one formula that came close: =SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100)). but this adds up all the matching results.
What I need to do is match the info in columns A and B and return a number in column C. The problem is that there are multiple rows with identical entries and I just want to return one of them. So I was thinking a version of the above that produced a mode or average would give the desired result.
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.
Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
How do I count unique values in Column A only if the values in Column B match?
A B
1 a
2 a
1 a
4 b
5 b
1 b
The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.
I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:
A1 - Apple
A2 - Banana
A3 - Apple
A4 - Banana
B1 - 3
B2 - 2
B3 - 1
B4 - 1
I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
I recently learned how to count cells in a range based on the value from another column (excel 2007) How to count cells in a range based on the value from another column but now how I can go about returning a value from another column that matches the conditional counting. For example in the table below I'm first wanting to find the rows matching "chr15" from column A that also have a value from column B that is greater than 25,000 and less than 3,000,000. But what it I wanted to instead report the corresponding values in column C? I've played with VLOOKUP to no avail but I'm not sure if that's the right line of thinking. The answer would be the values in bold.
A B C
1 chr2 12008 AA
2 chr2 149700 BB
3 chr15 51 CC
4 chr15 5624 DD
[Code] .........
I have the following two rows that I'm looking to return a single column number that maxes two criteria. In the example, 70, 30 would return column 7. I'm not looking to create a dummy row the combines the two. Reason for using MATCH or HLOOKUP is that the two values may not be the values in the given table.
View 9 Replies View RelatedDoes anyone have a macro that will delete entire row if column a matches column d
View 6 Replies View RelatedI have a spreadsheet which will be completed by numerous users, with a worksheet reserved for each area. The spreadsheet is to record the number of days lost to training etc on a weekly basis.
Each worksheet has 3 columns – column A DESCRIPTION, column B WEEK COMMENCING DATE and column C DAYS LOST.
The table will be completed by the manager’s as the info becomes available to them.
I will be collating the data on another worksheet and need a formula that will look in column B for all instances of 01/10/07 and then sum the corresponding cells in column C, then do the same for 08/10/07 and so on.
I have attached an example of a page.
I thought it may be VLookup or Sumif, but I don’t know how to go about it.
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:
If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.
2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE
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?
I've got a database that is sorted by date...
I need a formula that will look for 2 criteria and once it finds those 2 matching criteria, I need it to return the information on that line that's in column 6, let's say.
so in one column I have the date, the next column I have the sales persons name, 4 columns over I have their order number.
In cell a1 of worksheet 1, I have a drop down ready that has all my sales persons listed. In cell a2 I will manually enter the date that I need to reference.
I need the formula to then look for cell a1 and a2 in worksheet 2 where I have my spreadsheet with the info I mentioned above, match that criterium in worksheet 2, and return the info in column 6.
Here is what I am trying to accomplish.
I have to columns.
I have copied column A from a PDF file. It contains entries like this, all in the same column:
5 0.002HP Direct- 26845 7/1959 Airesearch
This column contains an ID # (5), a description (0.002HP Direct), a part # (26845), a date (7/1959), and a manufacturer (Airesearch). All this info is in one column. This column is 11,000+ entries long.
I have column B which is 45,000+ entries long, and contains ONLY part numbers, like this:
325111
I would like to compare column B to find the matching results from within the text of column A, and put these matches into column C.
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)
[Code] .........
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
View 3 Replies View RelatedIn my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
I have a spreadsheet of several thousand named items (in column B) with values associated with them in column A). The "name" field is a string of several alternative names for the item.
I have a list of ~50 items that I am trying to find the values for. Each uses one of the alternate names.
What I want is a function that will return the associated value in column A when one of my shortlist names is found.
For example, my big spreadsheet looks like:
AB
1dallas, DFW. 12345
2Atlanta, ATL, 23456
3Boston, BOS, 34567
4Chicago, CHI, 45678
5Los Angles, LA, LAX, 56789
And my list looks like
Atlanta
CHI
I want the output to look something like:
Atlanta2
CHI 4
Is the feasible? IT seems like there should be a way to do it....
formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.
View 4 Replies View RelatedI'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.
In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.
I have a list of values in a column. In that column, I want to find 50 possible matches. Is this possible in Excel? In the column there are multiple strings of text.
After it finds these 50 potential matches, the formula should return a value next to the cell where it matched. PLEASE HELP IF THIS POSSIBLE.
I have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.