suppliers in one row

[andonny]

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

[Aladin Akyurek]

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