Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: suppliers in one row

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    220
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    I just came accross another problem and I am hoping for help.

    I have many multiple items in column A(sheet1) from various suppliers. Just
    as an example: One of the items could have been purchased from four
    different suppliers (maybe three or five or only two suppliers). I have no
    problems to sumif the qty and total values of each item in sheet2 as a
    unique item. The problem I have is to list the various suppliers on the same
    row where the unique items are. I am dealing here with 40000 rows of data.
    Example sheet1:
    .................qty.............value........supplier
    Apple........30.............3500.......company1
    Apple........50.............4000......company2
    Apple........60............7000........company3
    Apple........70...........2000........company4
    Apple........70...........2000.........company2
    Pear...........150.........6000........smith
    Pear...........90...........7000.........vista
    Pear...........30...........7000.........a company
    Pear...........20...........2000.........vista

    Example sheet2:
    ......................qty.............value..........supplier1.............s
    upplier2...............supplier3...................supplier4
    Pear.............521............20100...........smith...............vista...
    ...................a company
    Apple...........450............15700..........company1.......company2.......
    ......company3....................company4
    etc.

    You help would be very much appreciated. This looks like a difficult
    one to me.
    Andonny


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,660
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Hi Andonny,

    You never cease to come up with beautiful questions.

    First of all you need Longre's morefunc.xll, which contains a UDF called UNIQUEVALUES that I use in what follows:

    I'll assume that A1:D10 in sheet Data houses the sample data you provided:

    {"item","qty","value","supplier ";
    "Apple",30,3500,"company1";
    "Apple",50,4000,"company2";
    "Apple",60,7000,"company3";
    "Apple",70,2000,"company4";
    "Apple",70,2000,"company2";
    "Pear",150,6000,"smith";
    "Pear",90,7000,"vista";
    "Pear",30,7000,"a company";
    "Pear",20,2000,"vista"}

    More importantly, I'll also assume that your data is sorted on the "item'column, a prerequisite for the below system of formulas to operate correctly.

    On sheet X --not a very imaginative name, I know:

    In B1:J1 enter:

    {"Start","End","qty","value",1,2,3,4,5}

    Create a unique list of items (e.g., using Advanced Filter) in column A from A2 on.

    With respect to the sample you provided, in A2:A3 we get:

    {"Apple";"Pear"}

    In B2 enter:

    =MATCH(A2,Data!A:A,0)

    In C2 enter:

    =MATCH(A2,Data!A:A)

    Select B2:C2 and copy down till row 3.

    In D2 enter:

    =SUMIF(OFFSET(Data!$A$1,$B2-1,0,$C2-$B2+1,1),$A2,OFFSET(Data!$A$1,$B2-1,1,$C2-$B2+1,1))

    In E2 enter:

    =SUMIF(OFFSET(Data!$A$1,$B2-1,0,$C2-$B2+1,1),$A2,OFFSET(Data!$A$1,$B2-1,2,$C2-$B2+1,1))

    Yes, you said you know how to do this part, but I couldn't resist to take up that too.

    Now the thing you asked for:

    In F2 enter:

    =IF($C2-$B2+1>F$1,INDEX(UNIQUEVALUES(OFFSET(Data!$A$1,$B2-1,3,$C2-$B2+1,1)),F$1),"")

    Copy this across till J2.

    Select D2:J2 and copy it till row 3.

    The data processing area, A1:J3, in X will now look like this:

    {"","Start","End","qty","value",1,2,3,4,5;
    "Apple",2,6,280,18500,"company4","company3","company2","company1","";
    "Pear",7,10,290,22000,"vista","smith","a company","",""}

    You can get morefunc at:

    http://perso/wanadoo.fr/longre/excel/downloads/

    Aladin

    On 2002-03-12 15:24, andonny wrote:
    Hi,
    I just came accross another problem and I am hoping for help.

    I have many multiple items in column A(sheet1) from various suppliers. Just
    as an example: One of the items could have been purchased from four
    different suppliers (maybe three or five or only two suppliers). I have no
    problems to sumif the qty and total values of each item in sheet2 as a
    unique item. The problem I have is to list the various suppliers on the same
    row where the unique items are. I am dealing here with 40000 rows of data.
    Example sheet1:
    .................qty.............value........supplier
    Apple........30.............3500.......company1
    Apple........50.............4000......company2
    Apple........60............7000........company3
    Apple........70...........2000........company4
    Apple........70...........2000.........company2
    Pear...........150.........6000........smith
    Pear...........90...........7000.........vista
    Pear...........30...........7000.........a company
    Pear...........20...........2000.........vista

    Example sheet2:
    ......................qty.............value..........supplier1.............s
    upplier2...............supplier3...................supplier4
    Pear.............521............20100...........smith...............vista...
    ...................a company
    Apple...........450............15700..........company1.......company2.......
    ......company3....................company4
    etc.

    You help would be very much appreciated. This looks like a difficult
    one to me.
    Andonny


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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