In an Excel spreadsheet, I want to search for multiple matches based upon the entry in a cell. That is...

Col A Col B Col C
x x 1
y y 2
z 3
x 4

Using the above data, I want to search for however many entries in column b have the letter x, and compile a list of the entries in column C for the Column B matches.

So, if column A has x, the answer (In column D or wherever) would be

Col D
1
4

If column A had a y, column D would answer only one entry of 2 (in the above data).

I am comparing risk factors and related mitigating factors. There are about 20 risk factors (what would go in column b above) and about the same number of mitigating factors (column C) and there can be more than one mitigating factor for each risk factor. That is, there is a matrix relationship between the two sets of factors. Thus for each risk factor entry I need a list for relevant mitigating factors.

I am suspecting that I may need to run a series of code blocks in a macro to accomplish this, such as:

assign entry in column a to a variable
Go to column b and, while column B is not "" look row by row finding each instance of the variable and for that instance put the entry of column C in a cell somewhere and go down one cell for the next "hit". And repeat until we hit an emtpty cell in column B.

And then go to the next row in column A and start over again with a different destination for the results to be recorded. Repeating the above until we hit an empty cell in column A and the whole thing comes to an end. I am not sure how to write this code in Excel VBA.

More specifically, I do understand how to write if statements.
I am not sure of the syntax to:
Assign a cell's contents to a variable
Compare that variable to another cell's contents (presumably using a second variable).

I believe that the list of results can be part of a while loop.

Although I know how to assign the correct cell to use for the first hit, I suspect that the way to move down a cell for each additional hit is to use a counter in the cell assignment (for where the results entries go). That is, the first time through the loop the results entry would go to Cell z(12+0) where excel sees this as z12 (or wherever) and the next entry in the loop would then be z12+1 or z13 to excel, etc. I am not sure how to use this cell reference using a counter to increase the row number with each loop.

Any help you can give would be appreciated.

Robert

Post a sample of the spreadsheet. Put text in the cells that are calculated. e.g Cell D3 = if A3 = x and B3 = x then put this value in here

Try the following freeware: http://sites.google.com/site/compare...pare2Lists.zip

Code:

---

Col A      Col B    Col C    Col D
  x          x        1        1
  y          y        2        4
            z        3
            x        4

---

For your original query, the only way to do that in formulas (without some additional "helper columns") is with an array formula.

Enter this formula into D1 (critical, it must be D1) and then confirm the formula by pressing CTRL-SHIFT-ENTER. Curly braces { } will appear around the formula and the first answer "1" should appear. Then copy the cell down.

=IF(ROWS($D$1:$D1) > COUNTIF($B:$B,$A$1), "", INDEX($C$1:$C$100, SMALL(IF($B$1:$B$100=$A$1, ROW($A$1:$A$100), ""), ROWS($D$1:$D1))))


A macro is not necessary, though you're right, could be LOOPed pretty easily. But this formula would keep the list updated on its own. You can copy that formula down as far as you want, it will return blank cells when it runs out of matches.

Is the values in column C really just row numbers? It looks that way from the sample. If it is, perhaps the C column isn't really needed.