I have a column of data, and a marker next to it. Think:
ID#1 -- A
ID#2 -- B
ID#3 -- A
I'm trying to move over only the ID numbers with the marker A - so my final result is
ID#1
ID#3
This is very easy to do with VBA - Filter the range, copy, paste. I'm trying to find a non-VBA solution to this problem. I've been trying various array formulas - Index(range,sumproduct(row*criteria)), but I can't quite get it working. How would I get this to work?
Computational elegancy is a significant factor - the sheet I'm dealing with should be a database due to it's size, but the powers that be have nixed that idea.
Answer
Use:
=IFERROR(INDEX($A$1:$A$8,SMALL(IF($B$1:$B$8="A",ROW($B$1:$B$8),999999),ROW(A1))),"")
Small will return the IDs with "A" for each row (smallest to largest)
A1:A8 change it to correspond your ID column
B1:B8 change it to correspond your A column
Keep $ for fixed references
press Ctrl+Shift+Enter instead of Enter it is an array formula
No comments:
Post a Comment