New comer :-) Grouping question

[garg29]

Good Morning everybody, this is my first post in this forum, hope I'll be able to express myself properly.

I'm trying to make a excel sheet with following features:
There are lets say 3 groups, each group having particular Items with different quantities (there can be common items) see the following:-

		Item1	1
Group1	Item2	5
		Item3	8
		Item4	2
Group2	Item2	1
		Item3	4
		Item7	7
		Item5	6
		Item6	1
		Item1	4
Group3	Item5	10
		Item3	7

<tbody>
</tbody>

Now there are different products using these groups with different quantities, lets say:-

	Group1 * 2
Product 1	Group2 * 3
	Group3 * 1
Product 2	Group2 * 7
	Group3 * 5
	Group1 * 5
Product 3	Group3 * 2

<tbody>
</tbody>

Now I want a common list to be generated of "Items" used & their quantities.

	Quantities
Item1
Item2
Item3
Item4
Item5
Item6
Item7

<colgroup><col width="96" span="2" style="width:72pt"> </colgroup><tbody>
</tbody>

Please help me out. Thanks.

with best regards.

 

[garg29]

So..........is there no one who could put up some light on my problem

 

[Verald]

Hey Garg, Welcome. I'm pretty new her too, and have learned a lot from reading other's responses. But ya need to give people a chance to reply. Most people sleep between midnight and 8:00.

With that said, look into the sumif() function. ie, the total of "Item1" would be =sumif(c1:c25,"Item1",d1:d25) Of course, you may need to modify your ranges.

Hopefully this helps.

 

[Aladin Akyurek]

So..........is there no one who could put up some light on my problem

Let A1:D100 on Sheet1 house the data of interest with the first row housing the headers.

A2, Sheet2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$C$2:$C$100,
  SMALL(IF(IF(FREQUENCY(IF(Sheet1!$C$2:$C$100<>"",
  MATCH("~"&Sheet1!$C$2:$C$100,Sheet1!$C$2:$C$100&"",0)),
  ROW(Sheet1!$C$2:$C$100)-ROW(Sheet1!$C$2)+1),1),
  ROW(Sheet1!$C$2:$C$100)-ROW(Sheet1!$C$2)+1),
  ROWS(A$2:$A2))),"")

B2, just enter and copy down:

=SUMIF(Sheet1!$C$2:$C$100,$A2,Sheet1!$D$2:$D$100)

[/code]