Finding Row On Matrix Matching 3 Criteria And Get A Result?
Dec 11, 2012
The matrix:
Col A Col B Col C
2012-11-08 07:26:16Check-InJohn
2012-11-08 13:23:58Check-OutJohn
2012-11-08 13:24:01Check-InJohn
The goal:
Since i have a given:
1. Name(which exists in column C)
2. Date (which exists in column A - formated has (AAAA-MM-SS HH:MM:SS)
3. Event (which exists in column B)
4. Hour*
I want to know the nearest hour that matches all criterias.
In the example on top, if i give:
John, 2012-11-08, Check-In, 14:00:00
I want the result: 13:24:01
If, for example, the date doesn't exist, the result could be a simple error message.
View 3 Replies
ADVERTISEMENT
Apr 25, 2013
Currently I have two tabs one with six columns of data. On a separate tab I have a matrix of two of the criteria. I would like to map the company data to the matrix, but recognizing the primary representative has more than one company he/she manages. Hence I don't just need the first entry, but all of his/hers maps to the size columns.
View 5 Replies
View Related
Feb 5, 2013
I have two workbooks at the moment, one which holds a whole host of customer and accounts details and another which holds only the customer details. What I'm trying to do is cross reference the two to determine if a customers details appear on both and if they do highlight this, ultimately I'd like it to pull the customers account number back but even if it highlights a match that would be sufficient.
The only unique criteria I have to do this are customer surname and customer postcode.
In the Customer details spreadsheet I'm attempting to enter a formula which will look at the specific surname & postcode held on the row in which it is entered. It will then look at all of the rows on the other spreadsheet and determine whether any of them meet both criteria.
If they do I'd like it to display "match" if they don't I'd like it to display "no match"
View 2 Replies
View Related
Feb 17, 2009
On my example sheet it shows how many cables and how the cable length per job. What i need is for the hour/job column to be populated from the Hours sheet by matching the column and row. e.g 4 cables at 40 metres would give me cell D7 (27.89), as 25 metres would be no good - so rounding up would be required.
View 2 Replies
View Related
Nov 13, 2009
I've been assigned a task of finding a combination of three or four machines. For example AD,AJ,AQ, and AB would equal 7 therefore it would be the best combination of workstations for that cell. However, I'm having an issue that if AD, AJ, AQ, and AB are being selected more than once.
My question is, how can I analyze all the data and determine the best combination given the relationships for each row given the column.
3 = Absolutely Necessary
2 = Extremely Necessary
1 = Necessary
0 = Do not associate
View 14 Replies
View Related
May 21, 2013
I'm using multiple data tables to run sensitivity analyses on a very large model. At the end of the analysis, I've got a matrix that tells me which scenarios are TRUE or FALSE (based on multiple criteria). I'm looking for an elegant way to extract the combinations that give TRUE.
Column1
Column2
Row1
FALSE
TRUE
Row2
TRUE
FALSE
Desired Output
True Scenarios:
R1, C2
R2, C1
View 1 Replies
View Related
Aug 8, 2009
I want to display the path result from a Floyd Algorithm matrix output..
You can download the excel file here : http://ifile.it/lw4tgic
It consists of 1044 nodes, and we need to find the path between 2 nodes for ALL pairs..
Now, to find the path between 2 nodes, we need to :
1. Find the resulting cell of corresponding 2 nodes (y to x = z)
2. Update the path with that value (y z x)
3. For all the direct pairs in the current path (yz, zx), find the resulting cell
4. If destination (x) not the same with result (z) then repeat step 1 for that pairs.
5. repeat until x = z for all pairs.
Maybe it's better if i use an example....
we'll use T01 to E78 as an example
T01-E78 = if you look in the table,
y (vertical) = T01,
x (horizontal) = E78
z (result) = E77, so
T01-E78 = E77, so path = T01-E77-E78
check if result of T01-E77 = E77 (x = z?)...........................
View 10 Replies
View Related
Nov 20, 2013
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
View 1 Replies
View Related
Oct 19, 2006
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
View 8 Replies
View Related
Nov 11, 2008
i have data on the left; a Ref number.. A0, A1, A2 etc etc and each of these has an associated 'output'
What i need to do is have the ability to input random numbers (like in column E) then hit the macro and it put in the ref code next to it
so far i have the following which only goes as far as shown above..
it errors and doesn't complete the list
Sub find_paste()
Dim output As Variant
For counter = 5 To 10
Set output = Range("E" & counter)
With Worksheets("data")
Range("B5:B13").Select
Selection.find(What:=output, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(rowoffset:=0, columnoffset:=-1).Copy
output.Activate
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).PasteSpecial
End With
Next counter
End Sub
View 9 Replies
View Related
Jul 18, 2014
Unique Count situation based on multiple criteria
Scenario: we have agents from different departments who produce different sales on different plan rates
Raw Data
Employee
Plan Rate
Payout pts
[Code]....
View 9 Replies
View Related
Nov 9, 2008
My spreadsheet consists of two columns, column A holds a series of numbers whilst column B, again holds a series of numbers but with an added single letter on the end.
What I would like to do is find a function which will match the numbers and letter in column B with the corresponding numbers in column A and then place the result in column C ....
View 10 Replies
View Related
Feb 13, 2007
Each car has a value, and each car has odds.
I must choose 5 cars, and the value cannot exceed 100.
I typed in all my data, can Excel choose the best possible lineup for me?
In the end, I need the lowest result possible while only using 100 pts in
value.
I am using Excel 2003
Here is a sample of my data.
Car # Value Odds (#to1) (Result=Value x Odds)
123.5494.00
223.45117.00
322.85.75131.10
422.66.75152.55
522.212.5277.50
62315345.00
71523.5352.50
82217.5385.00
921.820436.00
1019.523.5458.25
1121.623.5507.60
122125525.00
1321.725542.50
141930570.00
1522.130663.00
1620.335710.50
1721.934744.60
182040800.00
1918.845846.00
2018.647.5883.50
View 9 Replies
View Related
Sep 21, 2006
I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).
View 9 Replies
View Related
Apr 2, 2013
I currently have two tabs. Tab A (Error Report) spit out a bunch of records that I need to find in the raw data/remove them. The value in column C in Tab A refers to Column A in Tab B (Data), and Column D in Tab A matches with Column N in Tab B, and Column E in tab A refers to Column C in tab B.
I want to know how I can find the records that match those 3 columns in the First Error Report Tab as I need to find those records and delete them as they are, as you can see, errors!
View 1 Replies
View Related
Oct 27, 2008
I have two spreadsheets, spreadsheet A and B. In each spreadsheet there is a column labeled 'event' and a number in that column. I need to match the event number in spreadsheet A with the same number in spreadsheet B. In spreadsheet B in the same row as the event number is another value that I need copied back to spreadsheet A.
Manually I would have to copy the event number from spreadsheet A and use 'find' in spreadsheet B. Then check in the same row as the event number in spreadsheet B for a specific number, copy that, head back to spreadsheet A and paste it next to the event number.
I've tried to create a macro to do this but when using 'find' to match the event number in spreadsheet B it keeps on using the first event number I used instead of moving down a row onto the next number. I can upload the spreadsheet if needed.
View 2 Replies
View Related
Oct 26, 2008
I have a sheet that returns 4 numbers where 2 will match. Is there a command that will return that matching value?
For example:
cell 1 = 5
cell 2 = 9
cell 3 = 4
cell 4 = 9
something to return the value 9
View 2 Replies
View Related
Oct 11, 2011
I have in Sheet 1 a list of product codes (that should be only numbers, i.e: 1010) on column A, product description on B and a sales column on C.
On Sheet 2 I have on column A I have in the same cell a letter "C" the product code and the product description(i.e.: C1010 Onions). On column B I got the sales total. ( I dont care about the letter "C" it can be deleted)
I would like to use a VBA code to find the same product codes between sheet 1 & 2 and copy the sales total from sheet 2 to sheet 1 when the product code is the same.
View 1 Replies
View Related
Jul 14, 2014
I have two tables, both contain a column of ID numbers and a column of addresses.
I would like a method to match the IDs from table1 to those of table2 - if no matches are found, search for matching addresses, if a match is found then output the ID in table1, the address, and the corresponding ID in table2.
If still no matches are found, output the ID and address from table1 and indicate that it didn't match.
Then I want to check table2 records - i.e. search for matching address between the records and output the table 1 ID, address and table2 ID were partial or no matches are found, as above.
View 3 Replies
View Related
Jul 7, 2009
I have 2 pretty large zip code lists, we will call them columns A and B. I know that I have some from A that match to zips in B, but I have alot more zips in B that don't match to column A. I need to identify all of the zips in B that do not match a zip in column A.
View 9 Replies
View Related
Sep 27, 2013
I need to find if there are matching addresses in 2 different excel files. If the same address appears in both files, I would like the new worksheet to return the address along with the sale price from the 1 file and the rental amount from the other file.
I have tried using vlookup but the problem is the exported data file contains the street number in one column and the street name on another column. I have attached a truncated example of both the rental data and the residential sales data.
View 5 Replies
View Related
Aug 26, 2007
I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 Replies
View Related
Aug 22, 2008
Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
View 9 Replies
View Related
Dec 23, 2008
I have an output table that looks like the followings:......
There is 1 input for the spreadsheet: What month? Enter 9, 10, 11 etc. I need it to pull from a data sheet that is set up as such:......
So effectively it needs to vlookup the name in column A, (A, B, or C), and then it needs to match that to the column that has the same name as the output table (Crit.1 or Crit.2 and the number of the month above it (10,11,etc). I attached an excel sheet to better demonstrate
View 2 Replies
View Related
Dec 23, 2009
If you look at my attached example. I'd like the date line/columns in Sheet "8002372008" to be populated automatically with data from Sheet "Data".
Is this possible? VLOOKUPs and MATCH are what I was thinking, but I'm not sure how to do it.
I know it could probably be done in VB, but I don't want to have to click on each "800 Number" sheet to update it every time, so that my summary sheets are accurate.
View 10 Replies
View Related
Jun 30, 2007
I need to match data with specific criteria. Details of the problem are as follows:
I have 2 sheets: "Main" and "Sub"
In "Main" there are 3 Important Columns: Col "C", "I" and "AF"
In "Sub" there are 2 Important Columns: Col "I' and "N".
Value in column "C" of "Main" is same as value in column "I" of "Sub", but not in same order.
Now the specific criteria:
If value in Column "I" of "Main" is not equal to "Zero", then the macro should pick up the value from the corresponding column "C" of "Main" and search for the same in column "I" of "Sub". After matching the same, it should find corresponding value in column "N" of "Sub". Then the macro should pick up the value from column "N" of "Sub" and put the value in the column "AF" of "Main".
This is basic requirement.
I am unable to retrieve the right value for the same.
Fine tuining will be:
If the value in column "N" of "Sub" is "Fully Automated" then it should only write "Automated" in column "AF" of "Main". And If the value is "Manual + ...." then it should write "Manual" in column "AF" of "Main".
View 14 Replies
View Related
Feb 9, 2006
Col cCol dFormula
Lease TermCompany #6023%
3637%
4843%
6052%
12620%
247FALSE
368FALSE
48105%
609FALSE
Residual Table
Company #
127
438
569
10
Col b
Lease TermResidual %
1210%20%9%
245%9%6%
364%7%3%
483%5%2%
602%3%1% ......
View 10 Replies
View Related
Apr 22, 2012
Modifying the following line by replacing the row number in AC2 and Q2 by an incremental variable? I mean something like “AC”&Rw , “Q”&Rw, &Rw, Rw being my incremental variable used to go through the rows.
ElxJobNo=Evaluate("Index(Index(Jobnumbers,0,1),Match(AC2&Q2,Index(Jobnumbers,0,3)&Index(Jobnumbers,0,4),0))")
View 3 Replies
View Related
Jul 6, 2006
How do I return an offset value within a named range using a formula? For example, my range "RngTest" is from C3:J43. If a value of "Product123" is found within the range, (lets say its found in cells C3; E5 and E7), I need the accumulative values in the cells 'below' (in cells D3; F5 and F7) added ( SUM) and returned to cell L3.
Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5,
the value of cell L3 should be 12. Also, do I need to express "RngTest" or "C3:J43" or does it matter?
View 4 Replies
View Related
Mar 20, 2007
I am trying to solve a small problem that i have, I am not that good with VBA and need somehelp.
heres the situation.
Sheet 1 is filled in col.1= title, col.2= table# (ex. 1,2,...11)col3.= grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10). I will enter a title then pick a table then a grid location.
On sheet 2 also known as table 1. col.1 =grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10) col. 2 = title.
I want to create a macro or function that can evaluate sheet1 and pick out when col.2=table 1 and col.3 = grid loc. A-1.... through D-10 then give me what is in col.1.
this will have to be done for 11 sheets 1 sheet for each table.
View 9 Replies
View Related
