+ Reply to Thread
Results 1 to 10 of 10

arrays in excel

  1. #1
    Dan
    Guest

    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. #2
    Domenic
    Guest

    Re: arrays in excel

    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!

    In article <[email protected]>,
    Dan <[email protected]> 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. #3
    Dan
    Guest

    Re: arrays in excel

    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),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!
    >
    > In article <[email protected]>,
    > Dan <[email protected]> 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. #4
    Leo Heuser
    Guest

    Re: arrays in excel

    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" <[email protected]> skrev i en meddelelse
    news:[email protected]...
    > 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. #5
    Domenic
    Guest

    Re: arrays in excel

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

    In article <[email protected]>,
    Dan <[email protected]> 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),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!
    > >
    > > In article <[email protected]>,
    > > Dan <[email protected]> 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. #6
    Harlan Grove
    Guest

    Re: arrays in excel

    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),$A1),
    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. #7
    Dan
    Guest

    Re: arrays in excel

    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 <[email protected]>,
    > Dan <[email protected]> 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),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!
    > > >
    > > > In article <[email protected]>,
    > > > Dan <[email protected]> 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. #8
    Dan
    Guest

    Re: arrays in excel

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


  9. #9
    Harlan Grove
    Guest

    Re: arrays in excel

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


  10. #10
    Dan
    Guest

    Re: arrays in excel

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1