Lookup array
 

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

Lookup array
 

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)



"RD Wirr" wrote in message
...
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

Lookup array
 

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

"RD Wirr" wrote:

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

Lookup array
 

=IF(ISERR(SMALL(IF(LEFT($A$1:$A$100,1)="B",ROW(IND IRECT("1:"&ROWS($A$1:$A$100)))),ROWS($1:1))),"",IN DEX($A$1:$A$100,SMALL(IF(LEFT($A$1:$A$100,1)="B",R OW(INDIRECT("1:"&ROWS($A$1:$A$100)))),ROWS($1:1))) )

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


"RD Wirr" wrote:

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

Lookup array
 

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"

"Toppers" wrote:

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

"RD Wirr" wrote:

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

Lookup array
 

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

"RD Wirr" wrote:

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