Home |
Search |
Today's Posts |
#1
|
|||
|
|||
arrays in excel
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! |
#2
|
|||
|
|||
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),IND EX(Sheet1! $B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A1,ROW(Sh eet1!$A$1:$A$100)-ROW(S heet1!$A$1)+1),COLUMNS($B1:B1))),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Dan wrote: 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! |
#3
|
|||
|
|||
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! "Domenic" wrote: 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),IND EX(Sheet1! $B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A1,ROW(Sh eet1!$A$1:$A$100)-ROW(S heet1!$A$1)+1),COLUMNS($B1:B1))),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Dan wrote: 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! |
#4
|
|||
|
|||
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. -- Best Regards Leo Heuser Followup to newsgroup only please. "Dan" skrev i en meddelelse ... 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! |
#5
|
|||
|
|||
Actually, my formula should give you the results you're looking for.
Same thing with Leo's formula. What are you getting? In article , Dan wrote: 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! "Domenic" wrote: 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),IND EX(Sheet1! $B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A1,ROW(Sh eet1!$A$1:$A$100)-ROW(S heet1!$A$1)+1),COLUMNS($B1:B1))),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Dan wrote: 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! |
#6
|
|||
|
|||
Leo Heuser wrote...
One way: In Sheet2 B1: =3DIF(COLUMN()-COLUMN($B1)+1<=3DCOUNTIF(Sheet1!$A$1:$A$100,$A1), OFFSET(Sheet1!$B$1,MIN(IF((Sheet1!$A$1:$A$100=3D$ A1)*(COUNTIF( OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$100)),$A1 )=3DCOLUMN()- COLUMN($B1)+1),ROW(Sheet1!$A$1:$A$100)-1)),0),"-") ..=2E. 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=3D$A1)*(COUNTIF(OFFSET( Sheet1!$A$1,,, ROW(Sheet1!$A$1:$A$100)),$A1)=3DCOLUMN()-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) =3DCOLUMN()-COLUMN($B1)+1,ROW(Sheet1!$A$1:$A$100)-1)) since the outermost IF condition makes the (Sheet1!$A$1:$A$100=3D$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=AD$1:$A$100=3D$A1,ROW(Sheet1!$A $1:=AD$A$100) -ROW(Sheet1!$A$1)+1),COLUMNS($B1:B1)=AD)) 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 =3DIF(COLUMNS($B1:B1)<=3DCOUNTIF(INDEX(Tbl,0,1),$A 1), OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=3D$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. |
#7
|
|||
|
|||
Hello,
Thanks to all you guys, very much appreciated, this is the perfect! You are amazing.... "Domenic" wrote: Actually, my formula should give you the results you're looking for. Same thing with Leo's formula. What are you getting? In article , Dan wrote: 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! "Domenic" wrote: 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),IND EX(Sheet1! $B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A1,ROW(Sh eet1!$A$1:$A$100)-ROW(S heet1!$A$1)+1),COLUMNS($B1:B1))),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Dan wrote: 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! |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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)=$A 1) *(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),"") |
#10
|
|||
|
|||
Thank you so much, you're the best!
"Harlan Grove" wrote: 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)=$A 1) *(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),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
I get a program error when I download an excel template | Excel Discussion (Misc queries) | |||
Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
Problems with Excel Horizontal arrays with regional options using. | Excel Discussion (Misc queries) |