Comparing pairs of cells in 2 sets of data

April 7, 2011 at 11:56:44
Specs: Windows 7
I am trying to see if there is any way to compare a pair of cells from one section of a worksheet, to a range of cells in the same worksheet, and then have that function display an output from another cell. (This is worded horribly, so hopefully my example will make more sense):

column A is a Pick-up Town
column B is a Drop off Town

In columns F-H, there is a set of data acting as a database of common distances between Pickup and Drop off towns. So:

Column F - Pick up Town
Column G - Drop off Town
Column H - Distance between F and G

Is there any way of writing a function, macro, formula, anything, that would be able to compare the pair of cells A1 and B1 to the entire Data table of columns F and G, and if there is a match, the cell would display the associated output from column H

So, for example:
A1 = Town 15 B2 = Town 11

in the data table:
F32 = Town 15 G32 = Town 11 H32 = 10

so the comparison would see that the pair of A1 and B1 is the same as the pair of F32 and G32, and display the input of H32 (a number of miles)

See More: Comparing pairs of cells in 2 sets of data

Report •

April 7, 2011 at 18:11:36
Post some of your data, not sure I completely understand what you want.

Read this How-To first:


Report •

April 7, 2011 at 19:48:14
A simpler solution would be a matrix setup, something along the lines of this:

       A         B         C        D      E        F       G         H
 1)                     New York Boston Stamford Chicago Baltimore Orlando
 2)          New York      0       20     30       40      50        60
 3)          Boston       10        0     31       41      51        61
 4)          Stamford     11       21      0       42      52        62
 5)          Chicago      12       22     32        0      53        63
 6)          Baltimore    13       23     33       43       0        64
 7)          Orlando      14       24     34       44      45         0
 9)  PickUp:  Boston						
10) DropOff:  Chicago						
12) Distance   41						

The formula in Cell B12 to get the distance would be:



Report •

April 8, 2011 at 06:05:21
Thanks for the suggestions. Ill see how this works out. Thanks

Report •

Related Solutions

April 8, 2011 at 06:37:17
Ok, so these two columns for example are in columns E and F. They are input by the spreadsheet user, and represent the pick up and drop off locations for jobs that done on a given day. On a day-to-day basis, the input for these cells will change. For this example, lets say the cells below are E9:E13 and F9:F13 and "Mlg" is cells i9:i13

P/U Town	D/O Town	Mlg
Farmington	Meriden		18
New Haven	Middletown	24
Middletown	Middletown	0
Farmington	Meriden		18

then, there is a data table set up like this in columns R-T. This is a small sample of the table, there are a total of approx 70 rows in the data set. "FROM" is column R, "To" is column S, and "Miles" is column T

FROM	            TO	        MILES
Berlin   	Hartford	12
Berlin   	Meriden	        9
Chester	        Wallingford	24
Cromwell	Hartford	11
Deep River	Middletown	20
Deep River	Old Saybrook	8
East Hampton	Middletown	11
East Hampton	New Haven	34

So, what I'm hoping to accomplish is for the function to compare Columns (E and F) to columns (R and S), and if the two match, to have the proper number from column T automatically pasted into column i. If there is not a match, then the user would need to be able to input their own number in Mlg without losing the formula. Hope this clarifies things. Thanks again.

Report •

April 8, 2011 at 14:30:22
OK, try this.

If your data looks like this:

       A               B           C
 1) FROM              TO         MILES
 3) Berlin         Hartford       12
 4) Farmington     Meriden         9
 5) Chester        Wallingford    24
 6) Cromwell       Hartford       11
 7) Deep River     Middletown     20
 8) Deep River     Old Saybrook    8
 9) East Hampton   Middletown     11
10) East Hampton   New Haven      34
14) P/U Town       D/O Town      Mlg
15) Farmington     Meriden        9  <<--formula
16) East Hampton   Middletown    11

Put this formula in cell C15:


See how that works out.


Report •

April 11, 2011 at 06:19:36
Thanks for the reply. I was actually able to find another solution based off a mileage calculator that the company had already set up. The solution ended up being this:

In a seperate worksheet, there was very large table similar to the matrix you mentioned in one of your earlier replies. I copy/pasted this sheet into the sheet I was working on. I then created a pair of drop down lists that would contained the names of the various towns. Then, in the desired cell, I was able to use a VLOOKUP function that would search the matrix in a simlar fashion to what you showed in one of your earlier posts. Thanks again for all the help.

Report •

Ask Question