suppliers in one row

andonny

Board Regular
Joined
Mar 11, 2002
Messages
220
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top