EXCEL ARRAYS & IF STATEMENTS

Hello,

=IF(COLUMNS($B1:B1)<=COUNTIF(INDEX(Tbl,0,1),$A1),
OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=$A1,ROW(Tbl)-CELL("Row",Tbl)),
COLUMNS($B1:B1)),1,1,1),"")

PROBLEM# 2

This is a great formula! I just forgot, i need one more IF statement
nested, how would i add another IF statement to check for anothe value so
this is the table:

PART# PO# Store#
A1: 021-310L B1: 107893 C1: 001
A2: 021-310L B2: 108983 C2: 002
A3: 021-310L B3: 109983 C30

So now I need to have these results on the next page:

PART# STR# PO# PO#
A1: 021-310L B1: 001 C1: 107893 D1: 109983
A2: 021-310L B1: 002 C2: 108983 D2:


Thanks alot in advance!

"Harlan Grove" wrote:

> Leo Heuser wrote...
> >One way:
> >
> >In Sheet2 B1:
> >
> >=IF(COLUMN()-COLUMN($B1)+1<=COUNTIF(Sheet1!$A$1:$A$100,$A1),
> >OFFSET(Sheet1!$B$1,MIN(IF((Sheet1!$A$1:$A$100=$A1)*(COUNTIF(
> >OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$100)),$A1)=COLUMN()-
> >COLUMN($B1)+1),ROW(Sheet1!$A$1:$A$100)-1)),0),"-")
> ....
>
> Solves the OP's problem as stated, but not generally. This formula
> relies on the source range beginning in row 1.
>
> Small point: Domenic's COLUMNS($B1:B1) is a much nicer way to generate
> a sequence as a horizontal array than COLUMN()-COLUMN($B1)+1. Wish I'd
> 'a thought of it.
>
> More significantly,
>
> MIN(IF((Sheet1!$A$1:$A$100=$A1)*(COUNTIF(OFFSET(Sheet1!$A$1,,,
> ROW(Sheet1!$A$1:$A$100)),$A1)=COLUMN()-COLUMN($B1)+1),
> ROW(Sheet1!$A$1:$A$100)-1))
>
> could be shortened to
>
> MIN(IF(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$100)),$A1)
> =COLUMN()-COLUMN($B1)+1,ROW(Sheet1!$A$1:$A$100)-1))
>
> since the outermost IF condition makes the (Sheet1!$A$1:$A$100=$A1)
> condition unnecessary.
>
> Finally, efficiency. The final expression above involves MIN iterating
> over an array derived from calling COUNTIF on 100 derived ranges of
> size varying from 1 to 100 rows. The COUNTIF call is O(N^2). Domenic's
> alternative,
>
> SMALL(IF(Sheet1!$A*$1:$A$100=$A1,ROW(Sheet1!$A$1:*$A$100)
> -ROW(Sheet1!$A$1)+1),COLUMNS($B1:B1)*))
>
> involves implicit sorting of a 100 entry array, which is O(N*log(N)) if
> my light testing of SMALL and LARGE is accurate, in which case they use
> Quicksort.
>
> So, bundling all the ideas together, and using the defined name Tbl to
> refer to the source data range on the other worksheet, try the array
> formula
>
> =IF(COLUMNS($B1:B1)<=COUNTIF(INDEX(Tbl,0,1),$A1),
> OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=$A1,ROW(Tbl)-CELL("Row",Tbl)),
> COLUMNS($B1:B1)),1,1,1),"")
>
> Final consideration: Leo's formula requires 7 levels of function calls.
> The final formula above requires 6 levels of function calls.
>
>

problem solved, thanks!

"Dan" wrote:

> Hello,
>
> =IF(COLUMNS($B1:B1)<=COUNTIF(INDEX(Tbl,0,1),$A1),
> OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=$A1,ROW(Tbl)-CELL("Row",Tbl)),
> COLUMNS($B1:B1)),1,1,1),"")
>
> PROBLEM# 2
>
> This is a great formula! I just forgot, i need one more IF statement
> nested, how would i add another IF statement to check for anothe value so
> this is the table:
>
> PART# PO# Store#
> A1: 021-310L B1: 107893 C1: 001
> A2: 021-310L B2: 108983 C2: 002
> A3: 021-310L B3: 109983 C30
>
> So now I need to have these results on the next page:
>
> PART# STR# PO# PO#
> A1: 021-310L B1: 001 C1: 107893 D1: 109983
> A2: 021-310L B1: 002 C2: 108983 D2:
>
>
> Thanks alot in advance!
>
> "Harlan Grove" wrote:
>
> > Leo Heuser wrote...
> > >One way:
> > >
> > >In Sheet2 B1:
> > >
> > >=IF(COLUMN()-COLUMN($B1)+1<=COUNTIF(Sheet1!$A$1:$A$100,$A1),
> > >OFFSET(Sheet1!$B$1,MIN(IF((Sheet1!$A$1:$A$100=$A1)*(COUNTIF(
> > >OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$100)),$A1)=COLUMN()-
> > >COLUMN($B1)+1),ROW(Sheet1!$A$1:$A$100)-1)),0),"-")
> > ....
> >
> > Solves the OP's problem as stated, but not generally. This formula
> > relies on the source range beginning in row 1.
> >
> > Small point: Domenic's COLUMNS($B1:B1) is a much nicer way to generate
> > a sequence as a horizontal array than COLUMN()-COLUMN($B1)+1. Wish I'd
> > 'a thought of it.
> >
> > More significantly,
> >
> > MIN(IF((Sheet1!$A$1:$A$100=$A1)*(COUNTIF(OFFSET(Sheet1!$A$1,,,
> > ROW(Sheet1!$A$1:$A$100)),$A1)=COLUMN()-COLUMN($B1)+1),
> > ROW(Sheet1!$A$1:$A$100)-1))
> >
> > could be shortened to
> >
> > MIN(IF(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$100)),$A1)
> > =COLUMN()-COLUMN($B1)+1,ROW(Sheet1!$A$1:$A$100)-1))
> >
> > since the outermost IF condition makes the (Sheet1!$A$1:$A$100=$A1)
> > condition unnecessary.
> >
> > Finally, efficiency. The final expression above involves MIN iterating
> > over an array derived from calling COUNTIF on 100 derived ranges of
> > size varying from 1 to 100 rows. The COUNTIF call is O(N^2). Domenic's
> > alternative,
> >
> > SMALL(IF(Sheet1!$AÂ*$1:$A$100=$A1,ROW(Sheet1!$A$1:Â*$A$100)
> > -ROW(Sheet1!$A$1)+1),COLUMNS($B1:B1)Â*))
> >
> > involves implicit sorting of a 100 entry array, which is O(N*log(N)) if
> > my light testing of SMALL and LARGE is accurate, in which case they use
> > Quicksort.
> >
> > So, bundling all the ideas together, and using the defined name Tbl to
> > refer to the source data range on the other worksheet, try the array
> > formula
> >
> > =IF(COLUMNS($B1:B1)<=COUNTIF(INDEX(Tbl,0,1),$A1),
> > OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=$A1,ROW(Tbl)-CELL("Row",Tbl)),
> > COLUMNS($B1:B1)),1,1,1),"")
> >
> > Final consideration: Leo's formula requires 7 levels of function calls.
> > The final formula above requires 6 levels of function calls.
> >
> >
>