SOLVED

combining names from multiple rows to single cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2713080%22%20slang%3D%22en-US%22%3Ecombining%20names%20from%20multiple%20rows%20to%20single%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2713080%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20even%20sure%20how%20to%20tackle%20this%20inquiry%2C%20it%20is%20something%20I%20have%20never%20had%20to%20perform%20before.%20I%20was%20provided%20a%20mailing%20list%20of%20over%2033%2C000%20records%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20multiple%20people%20at%20%3CSTRONG%3Esame%3C%2FSTRONG%3E%20address%20with%20same%20last%20name%20%3D%20combine%20to%20one%20row%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20wondering%20if%20the%20following%20scenario%20is%20possible.%20E.g.%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2297px%22%3E%3CP%3EFNAME%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3ELNAME%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E%3CP%3EADD1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22107px%22%3E%3CP%3EADD2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2291px%22%3E%3CP%3EPROV%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2289px%22%3E%3CP%3EPC%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22142px%22%3E%3CP%3EOUTCOME%20FOR%20PRINT%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297px%22%3E%3CP%3EMarie%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3EButler%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E%3CP%3E4850%20Union%20Rd.%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22107px%22%3E%3CP%3EBeamsville%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2291px%22%3E%3CP%3EON%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2289px%22%3E%3CP%3EL0R%201B4%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22142px%22%3E%3CP%3E%3D%20Marie%20Butler%2C%26nbsp%3B%20Jeromy%20Butler%3C%2FP%3E%3CP%3E4850%20Union%20Rd%3C%2FP%3E%3CP%3EBeamsville%20ON%20L0R%201B4%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297px%22%3E%3CP%3EJeromy%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3EButler%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E%3CP%3E4850%20Union%20Rd.%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22107px%22%3E%3CP%3EBeamsville%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2291px%22%3E%3CP%3EON%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2289px%22%3E%3CP%3EL0R%201B4%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297px%22%3E%3CP%3ECarolyn%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3EVanAndel%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E%3CP%3E123%20Main%20St.%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22107px%22%3E%3CP%3EToronto%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2291px%22%3E%3CP%3EON%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2289px%22%3E%3CP%3EM4A%202B2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22142px%22%3E%3CP%3E%3DCarolyn%20VanAndel%3C%2FP%3E%3CP%3E123%20Main%20St.%3C%2FP%3E%3CP%3EToronto%20ON%20M4A%202B2%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297px%22%3E%3CP%3EMissy%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3EPignatelli%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E%3CP%3E123%20Main%20St.%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22107px%22%3E%3CP%3EToronto%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2291px%22%3E%3CP%3EON%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2289px%22%3E%3CP%3EM4A%202B2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22142px%22%3E%3CP%3E%3D%20Missy%20Pignatelli%3C%2FP%3E%3CP%3E123%20Main%20St.%3C%2FP%3E%3CP%3EToronto%20ON%20M4A%202B2%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2713080%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2713227%22%20slang%3D%22en-US%22%3ERe%3A%20combining%20names%20from%20multiple%20rows%20to%20single%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2713227%22%20slang%3D%22en-US%22%3E%3DTEXTJOIN(%22%20%22%2CTRUE%2CA2%3AF2)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2715598%22%20slang%3D%22en-US%22%3ERe%3A%20combining%20names%20from%20multiple%20rows%20to%20single%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2715598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F265210%22%20target%3D%22_blank%22%3E%40marie_pbi%3C%2FA%3E%26nbsp%3BI%20suggest%20you%20look%20into%20PowerQuery%20(PQ)%20for%20such%20a%20task.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-09-03%20at%2007.57.47.png%22%20style%3D%22width%3A%20942px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307883iDE339CA9BCC07CA4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-09-03%20at%2007.57.47.png%22%20alt%3D%22Screenshot%202021-09-03%20at%2007.57.47.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20table%20on%20the%20left%20contains%20the%20data%20from%20your%20example.%20Note%20that%20the%20bottom%20two%20share%20the%20same%20address%2C%20so%20they%20should%20end-up%20in%20one%20address%20field.%20The%20table%20on%20the%20right%20is%20the%20output%20created%20by%20PQ.%20Doing%20this%20for%2033000%20records%20will%20not%20be%20much%20of%20a%20problem.%20And%20the%20neat%20thing%20is%20that%20the%20records%20don't%20have%20to%20be%20ordered%20in%20any%20particular%20way.%20So%2C%20to%20answer%20your%20question%2C%20yes%20it%20is%20possible.%20The%20attached%20solution%20will%20work%20if%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20you%20ar%20not%20using%20Excel%20for%20the%20Mac%3B%3C%2FP%3E%3CP%3E2)%20your%20real%20data%20is%20clean%20and%20structured%20exactly%20as%20in%20the%20example%20you%20provided%3B%3C%2FP%3E%3CP%3E3)%20you%20are%20willing%20to%20learn%20PQ.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2717012%22%20slang%3D%22en-US%22%3ERe%3A%20combining%20names%20from%20multiple%20rows%20to%20single%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2717012%22%20slang%3D%22en-US%22%3EGood%20morning%2C%20Riny%20-%20this%20may%20be%20the%20solution%20-%20thank%20you%20for%20your%20help.%20I%20am%20going%20to%20try%20and%20explain%20in%20a%20bit%20more%20detail.%20To%20answer%20your%20points%2C%20I%20am%20not%20using%20Mac%2C%20my%20data%20is%20cleaned%20through%20mailing%20software%2C%20yes%20I%20am%20willing%20to%20learn%20PQ!%3CBR%20%2F%3EThe%20overview%20of%20this%20project%20is%20for%20a%20post-card%20mailing%20-%20if%20the%20residents%20of%20same%20household%20have%20the%20same%20last%20name%2C%20they%20are%20combined%20to%20recieve%20one%20postcard.%3CBR%20%2F%3EIf%20residents%20have%20different%20last%20names%20but%20same%20address%20they%20each%20recieve%20a%20postcard.%3CBR%20%2F%3EHow%20would%20I%20set%20those%20parameters%20using%20PQ%3F%20I%20would%20need%20these%20to%20export%20to%20cells%3B%20fname%2C%20lname%2C%20address1%2C%20address2%2C%20city%2C%20prov%2C%20postal%20code%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2717378%22%20slang%3D%22en-US%22%3ERe%3A%20combining%20names%20from%20multiple%20rows%20to%20single%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2717378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F265210%22%20target%3D%22_blank%22%3E%40marie_pbi%3C%2FA%3E%26nbsp%3BGood%20(almost)%20evening!%3C%2FP%3E%3CP%3EOops%2C%20I%20missed%20the%20%22same%20name%22%20bit.%20But%20you%20have%20changed%20the%20rules%20a%20bit.%20I%20remember%20from%20your%20original%20post%20(changed%20now)%20that%20the%20Name%20and%20Address%20information%20should%20all%20go%20in%20one%20cell%20with%20line%20feeds.%20Now%20you%20want%20all%20the%20name%20and%20address%20fields%20in%20separate%20cells%2C%20probably%20to%20do%20a%20mail%20merge%20to%20create%20address%20labels.%20True%3F%26nbsp%3BDon't%20have%20time%20right%20now%20to%20look%20into%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2718610%22%20slang%3D%22en-US%22%3ERe%3A%20combining%20names%20from%20multiple%20rows%20to%20single%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2718610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F265210%22%20target%3D%22_blank%22%3E%40marie_pbi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%2C%20with%20Power%20Query%20you%20may%20Group%20By%20without%20aggregation%20all%20columns%20by%20FNAME%3B%20extract%20values%20from%20FNAME%20with%20delimiter%3B%20split%20resulting%20column%20using%20this%20delimiter%20keeping%20only%20first%20two%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

7 Replies
=TEXTJOIN(" ",TRUE,A2:F2)

@marie_pbi I suggest you look into PowerQuery (PQ) for such a task. 

Screenshot 2021-09-03 at 07.57.47.png

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 code

@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.

 

 

@marie_pbi 

As 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.

best response confirmed by marie_pbi (New Contributor)
Solution

@Sergei Baklan 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 , you are welcome, glad to help