Forum Discussion
combining names from multiple rows to single cells
I am not even sure how to tackle this inquiry, it is something I have never had to perform before.
I was provided a mailing list of over 33,000 records, I have filtered the common records out to work with.
If multiple people at same address with same last name = combine to one row for mailing
Just wondering if the enclosed scenario is possible.
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!
7 Replies
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.
- =TEXTJOIN(" ",TRUE,A2:F2)
