Matching Multiple Criteria And + / - Numbers
Jun 28, 2014
I have a spreadsheet where I want to match the PO info with the invoice info. I want to match the cost center, location, PO number, item number and the price.
Period
Cost Center
Account
Item No
PO No
Amount
Test
DEC-12
5000AF
000
M0002648
13579
(59.89)
5.11
[code]....
The formula I used in the test column
=SUMPRODUCT(--($B$2:$B$13&$C$2:$C$13&$D$2:$D$13&$E$2:$E$13=B2&C2&D2&E2),$F$2:$F$13)
As you can see in that the formula is showing the first 4 rows with a difference even though that the difference is in the first two. How can i fix this?
My second question, on the lines with the 989.04 difference, how can I make the same formula to say "Fix the account" or to fix the column with discrepancy
View 4 Replies
ADVERTISEMENT
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
Jan 30, 2014
I'm trying to create an overview which shows a breakdown of the total minutes for each task on each given day.
I need to get a formula to work in the grey area which sums the total minutes (column C) based on matching two criteras of task (column A) and day (column b).
View 5 Replies
View Related
Jun 25, 2007
I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got
View 3 Replies
View Related
Feb 3, 2013
I've got an array of data about 30x1500 cells in size, I want to use this data in a table I'm making on another worksheet.
Normally I'd just use VLOOKUP and this works great in most cases but I want to do something a little bit more complicated. Basically I want to (in normal English) have excel look at the table, find a cell that matches criteria in this data array, then compare to another cell on the same row in this array. If the criteria for these two match then display the data from another cell on the same row. The criteria for matching the first cells would be an exact value, both being strings of text.
I've tried using a couple of nested VLOOKUP and IF statements but failed .....
View 4 Replies
View Related
Jul 14, 2009
I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.
HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.
C is the part number column in the schedule
K is the date row in the schedule ....
View 10 Replies
View Related
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
Jun 2, 2007
I am using the below code to count records in a range that meet multiple criteria.
myCount=0
For Each dCell In Range("MyRange")
If dCell = var1 And dCell.Offset(0,-1) = var2 Then myCount = myCount + 1
Next dCell
Is there a faster way to count multiple criteria in a VBA procedure than what I'm doing, but without using worksheet formulas? I tried "WorksheetFunction.Sumproduct" like you would in a worksheet formula, but that didn't work (unsurprisingly).
View 4 Replies
View Related
Aug 28, 2007
On my worksheet (which I have stripped down significantly to be able to upload here), I am looking for a way to copy certain cells over when the TCR (machine) numbers match. So, on the spreadsheet, if there is a 1 on the De La Rue sheet in the A column, it will then copy the value that is in cell B (directly next to it) and paste it into the balance sheet tab in column J in the row that has the same number in it. It would then do this for all machines in the balance sheet.
Then it would go to the "Ecoin Amounts" sheet, and do the same match, copy, paste, but this time it would be if the machine number matches, it takes the value from column D and the value from column H and pastes these into the balance sheet sheet in columns C and E respectively.
As you can see from the balance sheet tab, I tried using a VLOOKUP function, but when I import my initial data to the sheets, and manipulate it the way that I want, it gives be a reference error. I could possibly circumvent this by writing the VLOOKUP amounts as part of my macro, but I wanted to see if this could be done any easier.
View 2 Replies
View Related
Jan 31, 2008
I am developing Spreadsheet that you will enter a list of parts that you need. it will have many columns but only 4 are vital to the check. this will be generated for each project.
With each part i want to then look up in another file that is a list of all the possible parts you can have with there cost. this file will be standard for everything and it will be updated from time to time.
now not all the criteria to look up will be exact matches.
here is an example
code temp/pres rating diameter material
GLV 4500 15 16Mo3
in the other file we will have multipu entries for GLV with different values.
code temp/pres rating diameter material Cost Reference
GLV 4500 20 16Mo3 400 aaa1
GLV 5500 15 16Mo3 350 aab1
GLV 5000 15 16Mo3 300 aab2
GLV 5000 15 13CrMo4-5 600 acb1
GLV 5500 15 13CrMo4-5 600 acb2
For Code i need it to match (there are lots of codes even including variations like GLV.)
For Temp/pres rating i need it to be equal or higher to the value
For diameter i need a match
for material i need a match.
so as you can see from the list above there are 2 options. so then i want it to return the cheapest.
that is step one.
step two is to repeat it for multiple list of parts.
View 4 Replies
View Related
Jun 7, 2009
I have the following dataset:
[Date] [Category] [Currency] [ExchangeRate] [.....], etc.
1-3-09 A USD 0,8
1-6-09 A EUR 1
1-7-09 A USD 0,7
1-8-09 B USD 0,9
1-9-09 B USD <formula>
I'd like to have the value of <formula> looked up in older records. Currency and category should match and it should pick the exchange rate with the maximum date.
Which formula and what syntax should I use to have this done?
I use Excel 2007.
View 10 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
Jun 8, 2014
I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.
The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.
View 2 Replies
View Related
Jun 17, 2009
How to sum dollar amounts in one column as they relate to two other columns. The two green columns (Account & Dept.) need to correspond to the appropriate dollar amount (Total Transaction Amount) and then the dollar amount needs to be summed under each Dept # as it relates to the Account #. As you can see you can have different Depts. for the same Account.
View 9 Replies
View Related
Aug 27, 2009
Column A: Start Date
Column B: End Date
Column C: Ethnicity Code (letter and number such as B1)
Column D: Hours
What I need to do is work out how many hours each ethnic group did in a specific month.
View 14 Replies
View Related
Dec 23, 2009
I am trying to use the code below to eliminate data in a spreadsheet based on certain criteria. I have been able to get this to work with text fields just fine, but now I am running into an issue with numbers. I am trying to get this code to delete rows if they have the word "unknown" in column C and if the value in column B is greater than 900 hours (999:00:00), the numbers in this column are formatted as [hhh]:mm:ss. What should I tweak to make this work?
View 7 Replies
View Related
Feb 11, 2014
Here is my formula that does not return accurate results:
=IFERROR(SUM(SUMIFS(Tenure!$H:$H,Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""})/SUM(COUNTIFS(Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""}))),0)
I need to average column H from the sheet Tenure IF column E is 1100 or 1090 (formatted as text) and IF column C is equal to 12, 13, or 14 and IF column G is less than or equal to a date and column I if greater than or equal to a date OR if this column is blank.
I tried AVERAGEIFS, the above route and many others. For whatever reason, I have been unsuccessful, but close many times.
View 3 Replies
View Related
Mar 12, 2013
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
View 1 Replies
View Related
Sep 11, 2008
I want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 Replies
View Related
Nov 29, 2012
I am trying to find a function which will do the trial and error bit for me. I have for example 5 numbers an want to fit the answer to the puzzle.
For instance
3 5 X
2 3 Y
1 2 4
Is there a way to find "X"? Then as a result "Y". I can't just go back up the equation as it is not hat simple. Is there a way excel will try an find what fits?
View 1 Replies
View Related
Nov 9, 2007
I have 10 separate columns with an unlimited (but right now 354 rows) and I would like to search for two matching numbers going row by row, giving me the answer in one field that would say how many rows the two matching numbers are in, is there a way?
View 9 Replies
View Related
Jan 14, 2010
I have a very large file with two columns with about 40,000 rows, is there an excel formula to match the same numbers in column A to the same numbers in column B and all the other non matching numbers to be pushed to the bottom of the columns.
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
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
Jun 17, 2012
I have the following columns (O & S):
S O
-----
1 3
3 7
6 6
8 4
1 3
2 8
1 6
I want a code (Formula) do the follwoing: Matching number 1 in column S with number 3 in Column O and count how many matches found in the two columns, Which in this case only two.
View 9 Replies
View Related
Sep 30, 2013
I have two tables. One has long list of numbers, and the other one has lesser. I'd like to match the identical numbers in both tables. By the way the first table has 14600 cells and the short one has 1466 cells.
1424771
1424782
1424772
1424786
1424773
[code].....
View 9 Replies
View Related
