arrays in excel

[Dan]

Hello,

What i'm trying to accomplish is to get a list of all po's (purchase orders)
that we have on order for a certain part.

What i need to do is to get a list of all po's associated with a certain part.
part# po#
A1:021-310L B1:107803
A1:021-310L B1:109851

What i'm trying to get excel to do is to place the data like this according
to what part number is entered in the adjacent coloumn from another sheet
containing the data shown above, i tried using the {=SUM(IF formula but that
only added the po's, and =VLOOKUP only gives me the first one it finds.

A1: 021-310L B1: 107803 C1: 109851

Much thanks in advance!

 

[Domenic]

Assuming that Sheet1, Columns A and B, contain your source data, and
Sheet2, A1, contains your part number, enter the following formula on
Sheet2...

B1, copied across:

=IF(COLUMN()-COLUMN($B1)+1<=COUNTIF(Sheet1!$A$1:$A$100,$A1),INDEX(Sheet1!
$B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A1,ROW(Sheet1!$A$1:$A$100)-ROW(S
heet1!$A$1)+1),COLUMNS($B1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Dan]

Sorry, I wasn't very clear, what in need is this:

A1:021-310L B1:107803
A2:021-310L B2:109851
A3:021-310L B3:109551

so when i enter in a1 on a seperate sheet i would get this:

A1:021-310L B1:107803 C1:109851 D1:109551

Thanks Again!

 

[Leo Heuser]

Hi Dan

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),"-")

Entered as one line with <Shift><Ctrl><Enter>, also if edited later.

Copy B1 to the right as far as necessary with the fill handle (the
little square in the lower corner of the cell)

Copy the selection down with the fill handle.

 

[Domenic]

Actually, my formula should give you the results you're looking for.
Same thing with Leo's formula. What are you getting?

 

[Harlan Grove]

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.

 

[Dan]

Hello,

Thanks to all you guys, very much appreciated, this is the perfect!

You are amazing....

 

[Dan]

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]

Dan wrote...
....

. . . 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

I'm guessing that final 'C30' should have been 'C3: 001'.

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:

....

Expand the definition of Tbl to include the 3rd column containing store
numbers, and change the C1 formula to

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

 

[Dan]

Thank you so much, you're the best!

Dan wrote...
....

I'm guessing that final 'C30' should have been 'C3: 001'.

....

Expand the definition of Tbl to include the 3rd column containing store
numbers, and change the C1 formula to

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