Find a value in a range which may have multiple instances of that value

Hi,

You don't really need a VBA procedure to do this. In fact anything in VBA is inherently slower than doing the task in the Excel App. assuming of course that it's possible. And in this case it is. See attached.
Assuming your data is in columns A & B and C & D contain the values that you want to sum then the simple array formula in row 1 copied down

Formula:

Please Login or Register  to view this content.

entered of course with Ctrl-Shift-Enter will work.

Hi,

One way, array formula** in G22 and copy across and down:

=INDEX(A$2:A$10,SMALL(IF($C$2:$C$10=INDEX($G$2:$G$4,MATCH(TRUE,MMULT((--(ROW($G$2:$G$4)>=TRANSPOSE(ROW($G$2:$G$4)))),COUNTIF($C$2:$C$10,$G$2:$G$4))>=ROWS($1:1),0)),ROW(C$2:C$10)-MIN(ROW(C$2:C$10))+1),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+MMULT((--(ROW($G$2:$G$4)>=TRANSPOSE(ROW($G$2:$G$4)))),COUNTIF($C$2:$C$10,$G$2:$G$4))),ROWS($1:1))))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Hi,

Well on the face of it your results 'array' boils down to merely sorting A2:C10 using column C as the key.
Then using a match on column C values to see if they exist in G2:G4 and delete the rows where there is no match.

Or am I missing some subtlety here?