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(Sh eet1!$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(Sh eet1!$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.