Lookup array

[Guest]

I have a list of values like this:
A
A1
A2
A3
A4
B
B1
B1
B3
C
C1
I need to have an array of formulas that looks up the instance of one of the
alpha characters (referencing the value in another cell), and then populate a
column of cells with the values that include the referenced Alpha for
example, if the reference is B, the lookups would populate my column of cells
with:
B
B1
B2
B3
The qty of values included with each alpha is variable.

Does anyone know an efficient way to do this?
Thanks in advance,
RDW

 

[Bob Phillips]

Use this array formula and copy down

=IF(ISERROR(SMALL(IF(ISNUMBER(FIND("B",$A$1:$A$20)),ROW($A$1:$A$20),""),ROW(A1))),"",
INDEX($A$1:$A$20,SMALL(IF(ISNUMBER(FIND("B",$A$1:$A$20)),ROW($A$1:$A$20),""),ROW(A1))))

as an array formula, commit it with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

One way which assumes (1) data is contiguous (2) output starts in row 1

$B$1 contains desired alpha character(s)

=IF(LEFT(OFFSET(INDIRECT($A$1
&MATCH($B$1,$A:$A,0)),ROW()-1,0,1,1),LEN($B$1))<>$B$1,"",OFFSET(INDIRECT($A$1
&MATCH($B$1,$A:$A,0)),ROW()-1,0,1,1))

If output starts in row n change "row()-1" to "row()-n"

Copy down until cell is blank i.e. list is completed

HTH

 

[Guest]

=IF(ISERR(SMALL(IF(LEFT($A$1:$A$100,1)="B",ROW(INDIRECT("1:"&ROWS($A$1:$A$100)))),ROWS($1:1))),"",INDEX($A$1:$A$100,SMALL(IF(LEFT($A$1:$A$100,1)="B",ROW(INDIRECT("1:"&ROWS($A$1:$A$100)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as required

 

[Guest]

Should be:

=IF(LEFT(OFFSET(INDIRECT("A"
&MATCH($B$1,$A:$A,0)),ROW()-1,0,1,1),LEN($B$1))<>$B$1,"",OFFSET(INDIRECT("A"
&MATCH($B$1,$A:$A,0)),ROW()-1,0,1,1))

But other solutions are "better"

 

[Guest]

Thanks to all for the help. I really appreciate it. I got the last one from
from Teethless Mama running in my application. But FYI, I found another nice
way to do it with an advanced filter and VBA. Another excel wizard had this
one on their website thata I downloaded a while ago and now can't find where
I got it. In any case Here's the code in case you are interested. Runs super
fast in my overloaded spreadsheet:

Sub BuildKomp()
'calculate criteria cell in case calculation mode is manual
Sheets("Build").Range("Criteria").Calculate
Worksheets("MRP").Range("BOMKomp") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Build").Range("Criteria"), _
CopyToRange:=Range("BuildKom"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("build").Calculate
End Sub

I know this code could probably be tightened up a bit but admittedly I am no
VBA guru myself. It works and I don't have to recalc the whole workbook to
grab the data.

Anyway, I have another application that can use the slick lookups you have
posted here.
Thanks again,
RDW