Hey, everyone! Have a spreadsheet with 2 columns (attached): orders and items. Each row represents one item in an order, so orders w/ multiple items get multiple rows. I'm needing to show instead each order on a single row and if any orders have multiple items, I need them listed in the same cell, separated by a semi-colon. Can anyone please help with this? Formulas or Power Query preferred over VBA but all methods accepted.
Re: Combining Order Data from Multiple Rows Into Single Row
with Power Query
(XL2010/2013 require Power Query add-in)
Order List ABC124 bread,milk ABC125 bread,candy,cheese,cookies,crackers,juice,meat,milk,pizza ABC126 donuts ABC127 apples,oranges
I would prefer to use a function but that is a great suggestion.
so use a worksheet functions and show itOriginally Posted by wherdzik
I am sorry Sandy, I posted in the wrong thread. I am not sure how this happend.
@wherdzik
I don't know either
Sorry, sandy666, I see Power Query lists steps of "Source", "Grp", "List", and "Ext", but I don't understand how these are applied or what they do. I have Power Query but am unfamiliar w/ it - could you please explain the steps you took?
Hi everyone, if still interesting in a formula solution try this:
I hope it results useful
Please Login or Register to view this content.
2013 VERSION Power Query Or VBA
The formula needs to use two different
Please refer to the VBA CODE regardless of whether column B is repeated
Please Login or Register to view this content.
sure, here is
for more information read here: Learn Power QueryPlease Login or Register to view this content.
if the problem is solved, make me happy and hit Add Reputation (bottom left corner next to the post that was helpful)
Last edited by sandy666; 05-26-2023 at 08:49 PM.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
This solution won't work with Excel 2013. See the OP's forum profile on the left.Hi everyone, if still interesting in a formula solution try this:
I hope it results usefulPlease Login or Register to view this content.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
D3
copied down to D10.Please Login or Register to view this content.
E3
copied down and across to Z10Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks