I am trying to set up a worksheet whereby two critera when matched from a drop down list will populate cell 'x' with a coressponding answer. if you have Destinations then a From and To column with various locations listed below, then in a thrid column you have an output cell which is kilometres. What formula can i use to match the various location 'to' and 'from''s with the correct kilometre match?
View 8 RepliesI would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.
The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).
And I want it to extract "Bills" value from "rule" sheet.
There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.
How do i solve this problem with index-match function?
I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments.
The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments.
I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if:
(1) The software title in worksheet two matches the software title in worksheet one
(2) The comment cell in worksheet one contains a comment (or text)
I have had only partial success; my obstacle being the duplicate software titles in worksheet one.
I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. The data set is something similar to the below:
Car
Region
Own
Use
Color
Honda
North
Yes
I use it to go for work
Green
[Code] .....
I want to be able to do following (2 separate tasks):
Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):
Use
I use it to go for work
Family trips
Weekend fun
2nd car
Work
Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):
Car
Region
Color
Honda
North
Green
White
I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.
Column A would be Recipe Numbers, Column B the Planned Total.
I want to search column A for all matching recipe numbers.
Then, any recipes that match, look in column B for the planned totals, adding all incidents where they match.
Then the most difficult part, which just occured to me now.
I only need the Total Planned Total and Recipe name reported once.
Recipe#..........Planned Total
450................100
600................75
620................125
450................50
620................180
450................100
600................200
450................110
I will not be able to sort these lists.
If-Match-Sum? to display:
Recipe#............Total Planned Total
450....................360
600....................275
620....................305
I've researched for the first part... and it seems like Match will only find the first match and not look further? Am I wrong on this?
How to display the results hadn't even occured to me yet, since I was still trying to figure out how to GET the result.
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
have two worksheets. sheet1 has order information on it with orders, dates, customer names. sheet2 has customer name list. How can I (via vba) search through the order sheet and find the most recent order date for each customer in the customer name list. post that most recent date next to the customer name on sheet2.
View 4 Replies View RelatedI created a userform for a giant spreadsheet with tons of data in it. There are three criteria. I created two command buttons - one that's "search" and one that's "close." The close works fine, but I don't know enough to program the "search" command. I'd like it to pull all data that matches the three criteria - rows H, D and M. So, if you answer all three, the macro will pull the rows of data that matches and paste it into another workbook (that opens in another window).
I'm not sure userforms are typically used for this sort of thing...
the attached workbook. Am trying to write a formula in Column F, which returns the results shown in Column H. Hope the workbook clearly sets out the criteria I'm trying to apply to the Lookup.
View 2 Replies View RelatedI 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 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 "=COUNT(IF(..." formula to count the number of hourly employees that have start dates older than 3 years. Column A has the start dates and column B has the salary status (S/H). =COUNT(IF(AND(A2:A1000<DATE(2004,1,1),B2:B1000="H"),A2:A1000)) This formula returns 1 no matter what I change the data to.
View 3 Replies View RelatedI'm trying to restructure a list of files at work into a format that makes some sort of sense and can be filtered into useful information.
Part of what I need to do, is match up all the drawing numbers that a certain project references. The numbers are formatted like "####A##" ex. 1234A01,1234A02,5678A01, etc. The cells that I need to extract drawing numbers from contain some description or other text (not just the drawing number) so I need to extract the drawing number from that cells value. (Ex. I need to get the drawing number 1234A01 from a cell whose value is "blah blah 1234A01 blah blah blah")
I was thinking I could just search the string for "####A##" but I'm not really sure how....I tried instr, but I think its searching for the literal value of "####A##" rather than treating the # characters as wildcards...
I tried:
Code:
if rngNames.Cells(intx, 2).Value Like ("*####A##*") then
set intStart = instr(1,rngnames.Cells(intx,2).value,"####A##")
'....
end if
but intStart remains 0, so this method is not working...
I have a advanced filter that works pretty much close to how I want it to. However I would like it to either cancel the new filter or copy everything if it finds 0 matches.
If the copied location is blank it breaks my sheet. So I need to find a way for it to never be blank, either by canceling it, copying everything, or finding some other way I haven't thought of.
Also for some reason my Advanced Filter does NOT Ignore blank "OR" cells. If I place a word in the top cell, then leave the bottom blank, it searches for the top cell or anything and I end up with everything. Its quite frustrating.
This is a continuation of thread 182629 [URL] ....
The End User wanted originally to type characters into cell C1 of a worksheet that the macro searched for in another worksheet, copying across every row containing the input.
The responses gave me the following code which worked perfectly:
VB:
With Sheets("PICKLIST")
myFind = "*" & .[c1].Value & "*"
End With
If myFind = "*" & "" & "*" Then Exit Sub
Unfortunately, the user NOW wants a Text Box instead of putting the characters into a cell.
I added a textbox (originally called 'TextBox2' but renamed "Choice") and "Dimed" CHOICE As Range.
Then tried the following code:
VB:
With Sheets("PICKLIST")
myFind = "*" & CHOICE & "*"
End With
But it generates a "RunTime Error 91" - Object variable or With block variable not set.
I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
I am trying to match data on two different reports. Both reports have month, location and supplier number. I would like to match a quantity on one report to information to another. Here is an example: (A-D are columns in excel)
Report 1:
A B C D
Month Location Supplier # Quantity
Report 2:
A B C D
Month Location Supplier # Quality
I would like to match the quality on the second report to the data in report one. I would have several months of data and want to match on month, location and supplier number. But there could be occurences where a match is unable to be made - for example having report with quantity but no corresponding quality on the other report for the month.
I am trying to use the vlookup fxn to display employee names according their in-time (10:30 for this example). I have multiple employees in at 10:30 so I need to find a way using vlookup to accomplish this.
This is a sample of my Master worksheet showing the schedule for all employees (Cell used for this example is D16):
My vlookup formula for finding the first match for a 10:30 in-time:
Code:
=VLOOKUP(C5,Master!D4:$W$38,20,FALSE)
MasterABCDEFGHIJ9AndrewSmith10:30BQT10AnnaSmith11:30CBQT11:30C5:30C11:0011BrianSmith12Brittany
Smith11:30C11:30C11:0013CassieSmith11:30C5:0011:005:0014
DanielleSmithBARBAR5:30C15DemirSmith5:0016DerekSmith10:305:00OC5:0011:30CExcel 2010
The other worksheets in this workbook are various Floorplans depending on how many employees are scheduled that day with a column designated to the employee's name(B), and a column for their in-time (C). Here is a sample of the '8 Server Floorplan':
8 ServersABCDEFGHIJ52Derek10:3023243334632625353674Anna11:30C272892938511:30C4142615196#
VALUE!10:3043446352Excel 2010Worksheet FormulasCellFormulaB7=VLOOKUP(C7,Master!D4:W38,20,FALSE)A5=A4+1B5=VLOOKUP(C5,Master!D4:$W$38,20,FALSE)
A6=A5+1A7=A6+1A8=A7+1A9=A8+1B9=VLOOKUP(C9,Master!D4+P6:W38,20,FALSE)
Finding the first 10:30 match with vlookup is fine, my issue starts when trying to find the next 10:30 match, since vlookup only finds the first match (this is happening in B9).
Here is the sample of cells showing my formulas for finding the address of the first '10:30' match from my initial vlookup fxn (O5 and P5):
8 ServersOP3Cell Loc.Cell Loc. + 145D16D1767D1189Excel 2010Worksheet FormulasCellFormulaO5=ADDRESS(ROW(Master!$D$4:$D$38)+MATCH(C5,Master!$D$4:$D$38,0)-1,COLUMN(Master!$D$4:$D$38),4)P5=ADDRESS(ROW(Master!D4:D38)+1+MATCH(C5,Master!D4:D38,0)-1,COLUMN(Master!D4:D38),4)P7=ADDRESS(ROW(Master!D4:D38)+1+MATCH(C7,Master!D4:D38,0)-1,COLUMN(Master!D4:D38),4)
I thought by just finding the address of the first match (Master!D16), incrementing it by 1 (=D17) and plugging the cell containing this formula (P5) into my data range, that it would force the vlookup formula to adjust its data range from D4:W38 to D17:W38. This, in turn would then find the first match for 10:30 and so on for anyother identical in-times...
I have some values representing daily values belonging to a particular Week number. The week numbers appear from 1 to 6 times in the top row. I would like to get data pertaining to a particular week number (as entered in B10) from the array in the area shown in Light Yellow and from there, I would do some minor calculations to arrive at the required data..
In the sample, the week number 3 appears 3 times and is shown here. The first value I am able to get using HLOOKUP. Getting subsequent occurrences are the issue I have. I thought I will try and use OFFSET function based on the cell reference of the first occurrence of the week number to obtain the rest. I have not had any luck in that.
Basically, my issue is how to use HLOOKUP to obtain if the Lookup value is repeated in the range. Or should this be tackled using some other route?
The week numbers appear in a sequence and are not placed at random.
i'm trying to do with an index match formula. My index match formula goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?
e.g. worksheet 1 has all the data
A B C
email ID date
worksheet 2 has a list of specific email addresses i'm looking for info on
A B
email date
My formula in column B of worksheet 2 is along the lines of this:
index(worksheet1 C:C,match(worksheet2 A1, worksheet 1 A:A,false))
basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.
The thing is we could have the same email address listed a number of times, so i'm looking for the latest date to be returned, not the first one it finds.
I am trying to populate a worksheet that takes information from a table on another worksheet. I have to match three columns and show multiple entries for each correct match. Attached is a sample worksheet.
Invoice.xlsx
The information should be on the "Invoice" sheet. The cells in green are what will be given. The cells in yellow are what should be grabbed from table 1 on "June Sands Tracking Sheet"
The delivery date, Customer and truck number are the given fields.
from there I want it to search the table and populate "Truck BOL #","Sand Yard","Time In Staging", "Time Out of Location", and "Sand Type"
I tried this formula in the BOL # column but it wouldn't work
=IF(OR(C10={"",""}),"",IF(COUNTIFS(Table1[Delivery Date],$B$7,Table1[Truck '#],$E$7)=0,"No Entry",IFERROR(INDEX(Table1[BOL'#],SMALL(IF(Table1[Delivery Date]=$B$7,IF(Table1[Truck '#]=$E$7,ROW(Table1[BOL'#])-MIN(ROW(Table1[BOL'#]))+1)),ROWS(C$11:C19))),"")))
I am trying to connect a special date and a price to a special name (please see the attached file).
In sheet 1 I have the following:
- In column A I have a set of different names.
- In column B I have a set of different dates.
- In column C I have a set of different prices on bonds that off course refers to the different names at their respective dates.
In rest of the sheets I want an overview of the different bonds, and what their prices are at the exact date. The problem is that if I use the vlookup it is only the first value that will be extracted. How can I get all of the prices corresponding to the different dates to the correct bond. I also want the possibility to update this every day.
I am trying to get a formula to work that looks up a vendor, and then checks the quantity of their order (less than 99999999, less 500, less than 200, and less than 50). I get either a #NAME or #N/A when I try to use what I have written. I tried to include an =index(array,AND(Match(row 1,exact match),Match(row 1 less than quantity),Match(row 1,greater than quantity)),Match(rate,exact match)) returning the variable rate. I have had no luck using vlookup and pivot tables.
View 6 Replies View Relatedhow do i lookup values when there are multiple matches and i want to return values horizontally
eg
column A Column B
A 1
B 2
C 3
A 4
B 5
C 6
D 7
D 8
D 9
D 10
and the output should be like:
column A Column B Column C Column D Column E
A 1 4
B 2 5
C 3 6
D 7 8 9 10
So here is how I have a template laid out:
A
B
C
D
E
F
ROW 1
Period 10
Period 11
[Code] ........
Cell E4: =SUMIF('P10-2011'!$A$4:$A$272,'Budget Upload'!$A19,'P10-2011'!$H$4:$H$272)
I have a SumIF statement in cell E4 but I would much prefer some sort of index or something where I can drag the formula down because the cells in column D will change (and require me to pick a different SUM range for my SUMIF function). Now my issue is I have multiple matches so when I use a typical index it returns the FIRST match.
Here is the formula I used in cell E11 to try and replicate the result in E5:
=INDEX('P10-2011'!$A$2:$Y$272,MATCH('Budget Upload'!$A4,'P10-2011'!$A$2:$A$272,0),MATCH('Budget Upload'!$D4,'P10-2011'!$A$2:$Y$2,0))
My index function works as it is designed but only returns the FIRST match. I should have multiple matches.
Is there a way to use an index function to return multiple results?
To clarify,the index function I put in cell F4 returns only ONE column (which is correct and will always be correct), which is related to the cell D4 but I have multiple rows (related to "7002" row match portion of the index function).
I am looking to perform a search that will return a match and place it somewhere else, and then search again until its finished the list?
View 9 Replies View RelatedI 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"
what i'm trying to do with an index match formula. My index match formula goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?
e.g. worksheet 1 has all the data
A B C
email ID date
worksheet 2 has a list of specific email addresses i'm looking for info on
A B
email date
My formula in column B of worksheet 2 is along the lines of this:
index(worksheet1 C:C,match(worksheet2 A1, worksheet 1 A:A,false))
basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.
The thing is we could have the same email address listed a number of times, so i'm looking for the latest date to be returned, not the first one it finds.