I am trying to build something in excel to help with my grocery shopping this year.
I am using some other macros to populate a shopping list from recipes I have found online.
In this shopping list table I would like to try to figure out how to write a macro that would compare the Ingredient and the Measure values from Column A and C and if there are duplicates in the list add the Quantity Values together from the rows into the list.
Here is an example of what I am trying to do in an example:
Original List:
| Ingredient | Quantity | Measure |
|---|---|---|
| Bacon | 1 | |
| Garlic | 1 | tablespoons |
| Basil | 1 | tablespoons |
| Butter | 1 | tablespoons |
| Chicken | 1 | |
| Cayenne Pepper | 1 | tablespoons |
| Garlic | 1 | tablespoons |
| Butter | 1 | tablespoons |
I Want to End up with a List that Looks Like This:
| Ingredient | Quantity | Measure |
|---|---|---|
| Bacon | 1 | |
| Garlic | 3 | tablespoons |
| Basil | 1 | tablespoons |
| Butter | 5 | tablespoons |
| Chicken | 1 | |
| Cayenne Pepper | 1 | tablespoons |
I figured out a way to do this with a Pivot Table but am really struggling on where to start on this in VBA.
CodePudding user response：
You can do this easily with a SUMIF formula.
In cell F5 (Garlic Quantity):
=SUMIF(A4:A11,E5,B4:B11)
CodePudding user response：
If you use a helper column to get the unique identifiers, you can use INDEX(MATCH()) and SUMIF() to generate that list:
Helper Column: =A2&C2
Unique List of helper column: =UNIQUE(FILTER(D2:D10000,D2:D10000<>""))
Index, Match : =INDEX($A:$A,MATCH($F$2#,$D:$D,0))
Sumifs: =SUMIF($D:$D,$F$2#,$B:$B)
Result:
