Order overview automation

Dear all,

I am looking for the option to automatically group new orders with the same order number in a stock overview with subtotals and possibly specific color per order.
Can this be automated using a script or something similar? Please see my added example. I work in Excel for Mac 16.83. Thanks in advance!

Kind regards,

Marnix

Hello Marrow83 and Welcome to Excel Forum.
In the file attached to post #1, it looks like we are given the expected result but not the setup of the data beforehand, which makes it hard to offer specific solutions.
That said, a pivot table would group by order number and give subtotals, although I don't believe that it will highlight per order number.
Let us know if you have any questions.

Hello JeteMc, thank you for your reply. Please see the attached screenshots of the data input and output of the desired result.

Attachment 864807Attachment 864806

Thanks in advance!

No - not screenshots, please. Mambers cannot test solutions with screenshots, so please add some source data to your sample workbook. Thanks.

Is your Excel version a 365 offering? If so, please add this to your forum profile.

I have extracted raw data from your workbook to another worksheet and added this:

=LET(u,UNIQUE(A2:A19),q,SUMIF(A2:A19,u,C2:C19),a,SUMIF(A2:A19,u,E2:E19),VSTACK(HSTACK({"ORDER","QUANTITY","AMOUNT"}),HSTACK(u,q,a),HSTACK({"TOTAL"},SUM(q),SUM(a))))

It produces this:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	H	I	J
2	ORDER	QUANTITY	AMOUNT
3	1000	8	160
4	1001	3	120
5	1002	5	60
6	1003	6	170
7	1004	3	40
8	1005	4	20
9	1006	2	60
10	TOTAL	31	630

Sheet: Sheet1

It will work only if you have Excel 365.

Seen, but no response ...

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.