Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to identify matches between column D & K, AND THEN, out of those matches, which ones have matches on column A & H. Basically columns D (ABC) & A (123) are linked, and K (ABC) and H (123) are linked. So out of matches between D (ABC) & K (ABC), which of those also have matches between A(123) & H(123). I know columns D & K have many matching values, but I only care about out of those matches, which ones also have matches in their corresponding columns A & H.

So I want a list of match results as following (the end result): 1. D & K, THEN 2. A & H

So there could be 3,546 matches on #1, but out of those matches, only 450 that share both #1 AND #2. It's like an IF/THEN scenario. I think in excel its the vlookup option but I can't get it to work. I also think an Access query could work comparing sheet 1 to sheet 2 and pulling a sheet 3 that shows matches between sheet1 column A and sheet2 column A AND sheet 1 column B and sheet 2 column B.

share|improve this question
2  
Welcome to Super User!! This post is not clear and also very hard to understand. Maybe you should put forth a little effort and make your post more clear. You can click the Edit link underneath your post to edit it. –  BenjiWiebe Feb 6 '13 at 18:57
    
its as clear as i can get it, tried to explain it a couple different ways –  Adam Rodriguez Feb 6 '13 at 23:06
    
Here's a way to think about it. The rows fall into four groups: 1) those in which the values in columns D & K and the values in A & H match; 2) those in which only the values in D & K match; 3) those in which only the values in A & H match; and 4) those in which neither the values in D & K nor those in A & H match. You want group 1 first. Which groups, if any, do you want next, and in what order? –  chuff Feb 6 '13 at 23:24
add comment

1 Answer

If I understand your question correctly, you have a table of values starting in column A and continuing at least through column K. I am assuming that you want to return a table that includes two sets of rows in the following order:

  1. Those in which both the values in columns D and K match and the values in columns A and H match.
  2. Those in which only the values in columns A and H match.

Assuming, by way of example, that your data are in the range A2:K1001, then the following is one way to get what you want. It uses one helper columns.

In cell M2, enter this formula and copy it down through cell M1001:

  =IF(A2<>H2,2,IF(D2=K2,0,1))*ROWS($A$2:$A$1001)+ROWS($A$2:A2)

The formula assigns a number to each row. If both columns D & K and A & H match, the number is between 1 and 1000; if only columns A & H match, the number is between 1001 and 2000; and otherwise the number is between 2001 and 3000.

Then enter the following formula into cell M1. It calculates the number of rows that you want to return (that is, with appropriately matching values in columns D & K and A & H):

  =COUNTIF($M$2:$M$1001,"<"&ROWS($M$2:$M$1001)*2+1)

Next enter in cell N2 the formula that, when copied across and down, will return the rows of data that you are interested in.

  =INDEX(A$2:A$1001,MATCH(SMALL($M$2:$M$1001,ROWS($M$2:M2)),$M$2:$M$1001,0))

Working from the center of the formula outward, the SMALL function calculates the nth smallest of the numbers that was just assigned, that is, the smallest, then the second smallest, etc. The MATCH function calculates how many rows down column M that number--and the corresponding row in your data table--is found. Finally, the INDEX function returns the data value that many rows down column A.

Finally, copy the formula across to cell Y2 to get the first full row of data, and copy those formulas down the number of rows shown in cell M1.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.