# New comer :-) Grouping question

#### garg29

##### New Member
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>

with best regards.

Last edited:

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
So..........is there no one who could put up some light on my problem

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.

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:
Rich (BB code):
=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:
Rich (BB code):
=SUMIF(Sheet1!\$C\$2:\$C\$100,\$A2,Sheet1!\$D\$2:\$D\$100)
[/code]

Replies
0
Views
138
Replies
15
Views
260
Replies
3
Views
92
Replies
12
Views
1K
Replies
5
Views
259

1,196,447
Messages
6,015,316
Members
441,887
Latest member
acquamarine

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

### Which adblocker are you using?

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

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