Combine data with identical name

Hello people!

Currently trying to figure out how to do this, but not quite sure on how to accomplish this. Any help is appreciated.

I basically have a order sheet, with quantity requested and a menu name. I'd like to copy all the menu's that are being ordered, and combine the menu's if any are identical.


Example:

Please Login or Register  to view this content.

I'd like to copy the whole Menu row to my other sheet, and combining MENU 3 (since there are 3) But, the quantity still has to fall under the specific order column like below:

Please Login or Register  to view this content.

I can't seem to paste the values properly in the code, look at the attached example if my question is still unclear.


The excel data I work with is massive, i have tried using macroes but that doesn't work since it doesn't combine any identical names. Surely there is a function that I can use? or do I have to work with vba.

Any suggestions on how to tackle this?


Thank you in advance,
-Nick

It works, but not really what I meant but that's my bad. Thanks!

Hi cana,

Basically wanted this:

Please Login or Register  to view this content.

I would like for the combined menu's to be copied to my Sheet2!A2. But I need the quantity that corresponds with the menu as well. Have a look at this new attachment, it's rather hard for me to explain it properly.(Sheet2 has the wanted outcome).

I do believe I can some use of your formula =Sumifs(Sheet1!B$3:B$17,Sheet1!B$5:B$19,$A2), just not quite sure how to implement it.


Thanks in advance!

If the sort order of the final list is flexible:

Hi FlameRetired,

Thank you for the reply.

That's one hell of a formula, thanks for that. I don't believe I can use that cause of the array, since there will be allot of data added and removed from my Sheet2, let me see if that's usable on my original file and hopefully wrap my head around the formula.

Any idea on how to get the quantity along as well?

Originally Posted by y0brah

Hi FlameRetired,

Thank you for the reply.

That's one hell of a formula, thanks for that. I don't believe I can use that cause of the array, since there will be allot of data added and removed from my Sheet2, let me see if that's usable on my original file and hopefully wrap my head around the formula.

Any idea on how to get the quantity along as well?

If you use this:
I failed to mention that with this particular formula: pre-select the entire range and array commit the range all at once.

BTW: this draws only from the first list where all the Menu# are listed. I don't follow how the second list was derived. If say Menu4 gets added to the second list only this formula will not do what you want. Do we need to start over, again?

I need to work on interpreting the rest of your question based upon the data.........i.e. I don't even know what the questions are, yet.

After a small struggle, I actually managed to get it to work using your formula and it does suffice my needs.

Thank you, FlameRetired, much appreciated.

Glad it worked.