Assigned value to cell based on two references

Sorry, I know the title may be a bit ambiguous and general, but I can't think of an easy way to describe my problem!

I currently have a chunk of data, to which I'm trying to assign a group, and then count how many occurences there are of the said group. I'll try and explain a little better in a table..... As an example, my data follows this pattern (which is a simplified form of the actual data):


1 a
1 a
3 b
6 a
4 a
4 b
4 a
2 b

The two groups are "a" and "b". If a number in the first column is followed by a "b" in the second, all occurences of that number need to be assigned a "b". So if there are multiple occurence, 2 of which are "a", and 1 "b", they all need to be assigned "b". As an example based on the above data, I need it to read:

1 a
1 a
3 b
6 a
4 B rather than a
4 b
4 B rather than a
2 b

I've been messing with IF statements with AND and OR's, and trying to think my way round a VB solution if needed, but drawing a blank! I hope I've explained it well enough, any help would be greatfully received!

Hi,

Assuming that the data you give above is in the range A2:B9, entering this formula in C2 and copying down will give a column containing the 'corrected' letters for each number:

=IF(ISNUMBER(MATCH(A2&"b",INDEX($A$2:$A$9&$B$2:$B$9,,),0)),"b","a")

Regards

You're welcome.

"Question: What does INDEX($A$2:$A$9&$B$2:$B$9,,) do? As far as I can tell from evaluating the formula step by step, it creates an array with concatenated pair-wise number and letter. Is the INDEX function only to create the array to feed into MATCH without using array function?"

Yes. Precisely that.

Generally, if a non-array solution can be generated (using e.g. INDEX) then it is, for various reasons, to be considered preferable to the array version.

Regards

I personally don't have time right now, unfortunately, though you may find this link of interest:

http://www.decisionmodels.com/optspeedj.htm

Regards