Hello again I have another challenge for you experts. Same spreadsheet ....DEV1 but we'd like to be able to get a total figure (Quantity) for each type of veg for all the orders which are basically in Column B; there's also an alternative spreadsheet also attached ....DEV2 whether that makes any difference I don't know but again thanks in advance for your kind help as ever..
Unfortunately, you did not include expected answers. So a guess, based on the limited description:
=TOROW(TEXTBEFORE(TEXTSPLIT(C2,"Quantity: "),")"),3)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I'd probably clean the data up using Power Query, and then pivot it - see attached.
Rory
Or maybe this is what you want... you still haven't told us:
Formula:
Please Login or Register to view this content.
Apologies Glenn what we'd like to have is the total number of orders for each type of veg so for example the total number of Beetroot orders is 7..does that help??
Last edited by WAULKMILLFH; 03-25-2024 at 11:38 AM.
Another guess,
Please Login or Register to view this content.
Wow thank you that's very much appreciated I will look at this in greater depth later
Thank you all for your replies I am in awe of your expertise as always and will spend time later in earnest looking at your solutions
My take on it:
Formula:Please Login or Register to view this content.
Many thanks again Glenn I really appreciate your kind help
Hi Glenn sorry to trouble you again...i find as the number of submissions increases in my spreadsheet that there are 'duplicates' in the Product/Total amount columns which your code kindly calculates....may well be something I've done wrong in which case apologies but if you have time could you have a look at it for me?
Many thanksPlant Preorder Form 2024 - Excel Report(2024-03-29) dev.xlsx
There are items that look the same, but are not - Broccoli Green Sprouting, for instance, where one is preceede by a CHAR(10) and the other is not.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I think this fixes it:
Please Login or Register to view this content.
This works as well:
Please Login or Register to view this content.
Here is another solution with a shorter formula (for users Excel 365 V2402 or newer):
Please try:Please Login or Register to view this content.
Many thanks Ali for your 3 replies I appreciate your help. I will have a closer look later this evening but not quite sure what you mean by 'There are items that look the same, but are not - Broccoli Green Sprouting, for instance, where one is preceede by a CHAR(10) and the other is not.'
Many thanks HansDouwe for your reply I appreciate your help. I will have a closer look later this evening.
What that means is this: I used the LEN function to check the length of the two Broccoli Green Sprouting entries - one was 24 characters and the other 25. It turned out that the first of the two had a carriage return (CHAR(10)) in front of the B. I determined this by copying and pasting values from those two entries.
The addition of TRIM() to the formula gets rid of that leading carriage return.
Glad to have helped.
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.
Many thanks again I tried your second solution and it returns #Name? but the first works beautifully thank you I will look at the Broccoli entries to see whats going on there too..
Open the attachment! But you have 365, so there is nothing in the formula that should return a #NAME? error. I have ONLY added the TRIM function: twice in the first formula and once in the second.
For Hans' solution, he told you that you'll need Excel 365 V2402 or newer (because of GROUPBY). Update your Office to the latest version and it should work.
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.
Many thanks I have marked this as solved and again appreciate your kind help
Ali I updated my Excel to v 2403 but Hans solution still returns #Name?...but no bother your solution is great
You'll need v2404 (which is still in the beta phase) for GROUPBY. If you join the BETA CHANNEL, you can get it now.
Ah Okay thank you!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks