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

Multiple Criteria Matching In VBA?

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))")

Matching Multiple Criteria In SUMIF?

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).

Count Matching Multiple Criteria

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

Matching Multiple Criteria On Row Then Showing Cell On That Row?

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 .....

Sum Index Results Matching Multiple Criteria

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 ....

Matching Multiple Criteria On Separate Tab To A Matrix

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.

Count Cells Matching Multiple Criteria

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).

Copy Cells Matching Multiple Criteria

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.

Return Value By Multiple Matching And Unmatching Criteria

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.

Find Value Matching Multiple Text Criteria And Max Date

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.

Matching Multiple Criteria On Single Rows And Returning Result?

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"

Returning Data From Separate Worksheet By Matching Multiple Criteria - Vlookup / If Combo?

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.

Sum Numbers With Multiple Criteria

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.

Summing Numbers With Multiple Criteria

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.

Delete Rows Multiple Criteria W/Numbers

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?

Average Of Numbers From List Based On Multiple IF-OR Criteria

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.

Matching Pairs - Column To Automatically Populate With Any Of 3 Digit Numbers That Share Two Numbers

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...

Deleting Rows Matching Numbers From Numbers In Column

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?

Matching Two Numbers

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?

Matching Numbers

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?

Matching The Numbers

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.

Vlookup And Matching 2 Criteria

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

Lookups Matching 2 Criteria

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.

Matching Data With Specified Criteria

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".

How Can Find A Value Using Two Different Matching Criteria

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% ......

Sum Based On Matching Criteria

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?

Results From Two Matching Criteria

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.

Matching Numbers In Two Columns?

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.

Matching 2 Numbers In Tables Together

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].....