# Comparing Two Ranges And Returning A Match

Jan 22, 2010
I need a function to compare two ranges and return a match. For example

Range A: {A56, B43, C98, D44}

Range B: {A33, G89, D44, K123, OB55}

the function would return D44. Is it possible at all with functions or do I need a macro?

View 14 Replies
ADVERTISEMENT
Feb 24, 2014

I would like compare data using a certain column to key off of and compare whether data has changed.

I have 5,000 rows and 26 columns. Cell C1 is CALLED PMNUM, cell I1 is called CHANGEDATE

I want to find all of the same PMNUMs (C2-C5000), look at the last change date (I2) (sorted in descending order), compare it to the previous change date (I2) and if anything has been changed in columns D2 through G2 and J2 through Z2 compared to the last time the data was reported, list the changes in in AA2.

View 4 Replies
View Related
May 31, 2007

I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.

HTML Column A Column B Column C

Date Number Who

3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~

Column A Column B

902-555-7958Big Bird

902-555-5100Daffy Duck

902-678-0352Elmo

902-555-5752Donald Duck

902-555-7420Bert

980-123-6621Ernie

902-555-2596Grover

View 12 Replies
View Related
Feb 10, 2009

I'm quite new to array formulas in excel and was wondering whether or not I can advance on this formula:

=AND($C$5=B7,$K$5=G5)

The problem is I would like to compare the entire colums (or array) and return the number of 'TRUE' statements in the array as a value.

=AND($C$5:$C$65536=B7,$K$5:$K$65536=G5)

however, this returns all values as 'FALSE'

example of arrgument:

IF C5:C65535=customer then count how many cells in K5:K65536=completed

View 8 Replies
View Related
Aug 5, 2009

I have data exported as .txt from an external program in the following format:

Sample Name Marker Allele1 Allele 2 ....Allele n

AAA D8S1358 10 11 XX

AAA D16S539 16 19 XX

and so on.

Each "Sample Name" will have 16 different "Marker"s and upto 10 different "Allele" at each "Marker". There could be upwords of 200 Sample Names on each exported sheet. Is there a simple way to compare all of the data from each "Sample Name" to each other and possibly to another sample set that will always be static but of the sample general makup as this?

View 9 Replies
View Related
Apr 22, 2014

I have an attendance sheet that employees scan their badge number, and returns their name and time/date stamp using a simple VLOOKUP.

I want to create a report that compares the names in the attendance sheet against the all the names in the master sheet, and only returns the names that have not attended the training.

I have tried a few test formulas using COUNTIF function, but with no luck.

I have attached an example. Use the worksheet labeled April 2014 A (2nd shift)

New Six Point Safety with Attendance Sheet Barcode Scanning.xlsx

View 3 Replies
View Related
May 30, 2014

Excel user that has been manually computing a 6000 row spreadsheet. Here is sample data and what I need. Column A goes through the alphabet (A,B, C, etc.) and Column B is numbers from 1-65. I need Column C to count the number of rows for each alphabet/number pair. K, 28 has 3 rows; K, 33 has 1 row; L, 21 has 4 rows; etc.

Column A

Column B

Column C

K

28

3

K

28

3

K

28

3

[code]....

View 2 Replies
View Related
Sep 18, 2013

If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).

Example: D7=Bob and D8=25

D11=John and D12= 40

D14= 45

D16 (answer cell) =John

OR:

D7=Bob and D8=46

D11=John and D12=48

D14=45

D16 (answer cell) =Bob

View 2 Replies
View Related
Mar 21, 2014

I have two lists, one has 250 items, the other 4500, both contain a String

I need to compare any word within a cell in the short list, against any word within a cell in the long list.

The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.

View 4 Replies
View Related
Sep 25, 2008

I have 3 sheets. Sheets 1 & 2 contain lists of data occupying columns A to M. Although the data on these sheets generally will be different, there are occasions where sheet 2 may contain identical rows to sheet 1.

What I want to do is two separate things -

copy the duplicated rows to sheet 3, and then in columns N & O indicate the row numbers from sheets 1 & 2 where the duplicates can be found (this only where the entire row matches)

do the same as above, but only where the contents of columns I & J match (rather than the entire row).

View 9 Replies
View Related
Apr 8, 2014

I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

View 3 Replies
View Related
Jan 26, 2010

I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:

Sheet 1

A1 - Mary Jones, B1- Internet Donation

A2 - Mary Jones, B2 - Silent Auction Purchase

A3 - Mary Jones, B3 - Event Ticket Purchase

A4 - Gary Jones, B4 - Foundation Grant

A5 - Gary Jones, B5 - Internet Donation

A6 - Sara Parker, B6 - Event Ticket Purchase

I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:

Sheet 2

A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3

A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes

Does this involve IF, MATCH or VLOOKUP formulas?

View 9 Replies
View Related
Dec 4, 2012

[URL]

take a look at the attached example and let me know how to make the formula work?

For each "title" in column A, I want to compare the two sets of start and end dates (columns B & C versus D & E) and if the date range in column B &C overlaps with the date range in column D & E, input "yes" or "no" in column F.

In my attached example, rows 8,9,11, and 12 would be populated with a "yes", the rest would be "no".

View 3 Replies
View Related
Feb 11, 2009

I'm on Vista with Excel 2007, HOWEVER, the solution must be Excel 97-2003 compatible.

I have two columns. One will a Y or N in it, the other will have a number. I need to sum the column with numbers, but I ONLY want the numbers added that have a 'Y' in the same row of the other column ...

View 6 Replies
View Related
Jul 7, 2014

I have a list of numbers which I need to compare to a range of numbers. Each number in the list needs to be compared to the range, and depending where it falls within the range, a particular cell needs to be referenced in the result. I've attached the xls with the explanation.

I was going to do this with a nested IF, but with a max of 7 this will not work.

Attached File : Help with comparing to a range.xlsx

View 5 Replies
View Related
Apr 23, 2012

[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])

[2] save each result of [1] where there was a match in column B

[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E

[4] count and message box the final number of matches from column E

Data Example:

A B C D E F

----- ----- ----- ----- ----- -----

12345 match 12345 11/1/2011 match 11/1/2011

77777 34345 ... ...

A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.

Sub other()

Dim range1, range2, range3, range4, range5, range6 As range

Dim x As Variant, y As Variant, z As Variant, a As Variant

[Code].....

View 1 Replies
View Related
Apr 30, 2008

I have a collum (A )with numbers and in other sheet have a collum (B)with numbers too.. and some numbers match. what i want is in the cells that the numbers match have other colum and i want to copy that number to another cell.

View 2 Replies
View Related
Feb 5, 2014

I have 2 tables where I need to pull data from one table into the other.

Table 1 - pull data here

A

Site

B

Begin Date

C

End Date

Rate

[Code]...

For example: site 77 in table 1, I need to pull whatever rate is shown for site 77 in table 2 for the date range specified on table 1. So the correct answer for the first row site 77 in column E would be .02. Notice there is a problem...mainly the dates do not match exactly. I just need the rate the applies for the majority of the time.

Another example. For table 1, site 174 from 2/11/11 to 2/22/12, the correct answer in column E (pulled from table 2) would be .02, even though Column D shows .00.

View 1 Replies
View Related
May 3, 2009

If I have a column with a list of items, and another column with a different list of items, how can I get excel to check/compare both list and return a value (eg. TRUE) if there is an item that is both lists, or a different value (eg. FALSE) if there isn't?

View 9 Replies
View Related
Aug 15, 2014

I'm using two workbooks and looking to see if two columns match, then value in the cell should be the corresponding value of that cell.

For example:

I'm using

If(Match(A2,"name of other workbook",0, I want the value in column F of row 2, "not in raw data")

But it keeps saying too many arguments.

View 6 Replies
View Related
Aug 15, 2013

From the attached file.

Column 1 is a file name

Column 2 is a list of file names (but some of the files have been split into -A and -B parts or suffixed with an A or B

I need to highlight in column B, if the exact file name in column A exists

Example:

Column A: BSDS-0001

Column B: does not have this exactly instead it has BSDS-0001-A and BSDS-0001-B

I need some indication/highlighting that BSDS-0001 does exist on column B

So an indication that the whole of the file name in column A appears partially in column B. I have tried using max character lengths searches etc but cannot get an accurate way of doing this.

View 11 Replies
View Related
Jun 25, 2007

Application.ScreenUpdating = False

Range("B:B").Select

For Each c In Selection

If c.Value = Range("A4") Then c.Rows.Interior.ColorIndex = 36

Application.ScreenUpdating = True

I got this code so far that highligts the rows that match but range a4 needs to go down the column to a5, a6, a7....so it only checks the first cell in column a. I need it check all cells. Then i was going to delete the entire row in column B where it was not highlighted.

View 9 Replies
View Related
Apr 10, 2014

Not sure where to start with this one, an IF statement or vlookup? Basically I have 3 columns A, B, C (original sheet) and A, B, C in my working sheet. A contains car registrations, B contains a date and C contains a weight. I need to return C from my original sheet into my working sheet only when A and B BOTH match from my working sheet (there are scenarios where the same car registration has a different date next to it). So I want to return the weight only when the car registration and the date match in a specific instance from my working sheet.

View 6 Replies
View Related
Dec 3, 2013

In the attached I have an index/match formula in B15 that I cant get to return any results not even a N/A#

Quick Quote Tool.xlsx

View 2 Replies
View Related
Jan 27, 2014

I have two tabs (In-Transit) & (AC) column A in both sheet have a "Concatenate" that i copied and pasted and values only and also switch the format to "Text" on both tabs. I do a vlookup

[Code] ......

and it returns the results i want....But then i was thinking due to the volume of data what if the value in column A is found twice on tab "AC".. i wouldn't know with a "Vlookup".

So trying to build a Index/Match formula

[Code] ....

I also returns some good results, but i also get some back as #REF. I also get #NA and both of the formulas and those are valid no matches...

View 2 Replies
View Related
Dec 8, 2009

I'm using vlookup to get a price for a unit of measure and it passes the value of the lesser not the greater. Is there anyway to get vlookup to pick the closest match as the greater value not the lesser one?

For example:

1000 units costs $8,000

4000 units cost $23,000

12000 units cost $68,000

If someone wants to buy 2500 units, I'd like the vlookup to pass the price based on 4000 units, but it passes 8,000 the value on 1000 units.

Can I use vlookup to give me the result of the greater unit not the lesser?

View 4 Replies
View Related
Jan 20, 2014

I am trying to find the 2nd occurrence in a range of text along 1 column and return the cell one to the left of that 2nd occurrence. I will also need to find the 3rd, 4th, 5th, etc occurences, down the column.

So far, I have a working formula to find the first occurrence and it looks like this:

=INDEX(INDIRECT(A2),MATCH("*SS*",INDEX(INDIRECT(A2),0,2),0),1)

How would I find the 2nd occurrence?

what if the formula looked like this?

how would I find it's 2nd occurrence?

{=INDEX(INDIRECT(A2),MATCH(TRUE,ISNUMBER(MATCH(INDEX(INDIRECT(A2),0,2),{"CF","LF","RF","OF"},0)),0),1)}

View 3 Replies
View Related
Mar 2, 2014

I am trying to sum the data in "details sheet" column H into "monthly sheet" based on the period and type match.

I am not sure where I am going wrong as the column references returned values that they should. However, the values in Col H from details sheet are not being summed up based on the criteria into monthly sheet.

I am getting zero amounts.

View 5 Replies
View Related
Nov 10, 2009

I need to retrieve information from multiple rows in a table based on certain criteria (date compared to today's date, existence of "no" in a column).

View 6 Replies
View Related
Dec 15, 2009

In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?

View 4 Replies
View Related