Forum Discussion
combining names from multiple rows to single cells
SergeiBaklan I was playing with that - but you have just saved me tons of trial and error!! thank you Sergei, that's the query I was looking to accomplish!
thank you so much!
marie_pbi I suggest you look into PowerQuery (PQ) for such a task.
The table on the left contains the data from your example. Note that the bottom two share the same address, so they should end-up in one address field. The table on the right is the output created by PQ. Doing this for 33000 records will not be much of a problem. And the neat thing is that the records don't have to be ordered in any particular way. So, to answer your question, yes it is possible. The attached solution will work if:
1) you ar not using Excel for the Mac;
2) your real data is clean and structured exactly as in the example you provided;
3) you are willing to learn PQ.
Good morning, Riny - this may be the solution - thank you for your help. I am going to try and explain in a bit more detail. To answer your points, I am not using Mac, my data is cleaned through mailing software, yes I am willing to learn PQ!
The overview of this project is for a post-card mailing - if the residents of same household have the same last name, they are combined to recieve one postcard.
If residents have different last names but same address they each recieve a postcard.
How would I set those parameters using PQ? I would need these to export to cells; fname, lname, address1, city, prov, postal codeAs variant, with Power Query you may Group By without aggregation all columns by FNAME; extract values from FNAME with delimiter; split resulting column using this delimiter keeping only first two columns.
SergeiBaklan I was playing with that - but you have just saved me tons of trial and error!! thank you Sergei, that's the query I was looking to accomplish!
thank you so much!
marie_pbi Good (almost) evening!
Oops, I missed the "same name" bit. But you have changed the rules a bit. I remember from your original post (changed now) that the Name and Address information should all go in one cell with line feeds. Now you want all the name and address fields in separate cells, probably to do a mail merge to create address labels. True? Don't have time right now to look into this.
