# 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 Towncolumn B is a Drop off TownIn 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 TownColumn G - Drop off TownColumn H - Distance between F and GIs 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 HSo, for example:A1 = Town 15 B2 = Town 11in the data table:F32 = Town 15 G32 = Town 11 H32 = 10so 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

#1
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:http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Report •

#2
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 8) 9) PickUp: Boston 10) DropOff: Chicago 11) 12) Distance 41 ```The formula in Cell B12 to get the distance would be:=VLOOKUP(B10,B3:H8,MATCH(B11,C2:H2,0)+1,FALSE)MIKEhttp://www.skeptic.com/

Report •

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

Report •

Related Solutions

#4
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 •

#5
April 8, 2011 at 14:30:22
 OK, try this.If your data looks like this:``` A B C 1) FROM TO MILES 2) 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 11) 12) 13) 14) P/U Town D/O Town Mlg 15) Farmington Meriden 9 <<--formula 16) East Hampton Middletown 11 ```Put this formula in cell C15:=SUMPRODUCT((\$A\$3:\$A\$10=A15)*(\$B\$3:\$B\$10=B15)*(\$C\$3:\$C\$10))See how that works out.MIKEhttp://www.skeptic.com/

Report •

#6
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 •