Combining Cells in columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1674924%22%20slang%3D%22en-US%22%3ECombining%20Cells%20in%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1674924%22%20slang%3D%22en-US%22%3E%3CP%3ENeed%20help%20in%20combining%20three%20columns%2C%20each%20with%20three%20cells%20with%20different%20text%20-%20that%20share%20the%20same%20part%20number%20in%20a%20different%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWant%20to%20combine%20(in%20this%20example)%2C%20the%20three%20cells%20per%20column%20in%20its%20own%20cell%3B%20the%20info%20from%20the%20three%20cells%20combined%20in%20one%20cell%20in%20a%20vertical%20fashion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20before%20%26amp%3B%20after%20screenshots%20of%20example%3C%2FP%3E%3CP%3Ebefore%20combining%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dcd2020_0-1600281497235.png%22%20style%3D%22width%3A%20656px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218806i715BFDE59153F132%2Fimage-dimensions%2F656x64%3Fv%3D1.0%22%20width%3D%22656%22%20height%3D%2264%22%20title%3D%22dcd2020_0-1600281497235.png%22%20alt%3D%22dcd2020_0-1600281497235.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eafter%20combining%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dcd2020_1-1600281715460.png%22%20style%3D%22width%3A%20656px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218809i18B58072EA0F0933%2Fimage-dimensions%2F656x69%3Fv%3D1.0%22%20width%3D%22656%22%20height%3D%2269%22%20title%3D%22dcd2020_1-1600281715460.png%22%20alt%3D%22dcd2020_1-1600281715460.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1674924%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1675124%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20Cells%20in%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1675124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796189%22%20target%3D%22_blank%22%3E%40dcd2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%3F%20So%20it%20looks%20prettier%3F%20That's%20not%20really%20a%20sufficient%20reason.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou'd%20be%20messing%20up%20your%20ability%20to%20take%20advantage%20of%20Excel's%20ability%20to%20work%20with%20a%20data%20table%20to%20produce%20summary%20reports%2C%20Pivot%20Tables%2C%20whatever...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I'd%20advise%20against%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20insist%20on%20doing%20it%2C%20%3CSTRONG%3EFormat....Cells....Alignment%3C%2FSTRONG%3E%20and%20select%20%22Merge%20Cells%22%20and%20then%20deal%20with%20where%20you%20want%20the%20text%20to%20appear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20as%20noted%2C%20it's%20not%20generally%20a%20good%20practice.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1675268%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20Cells%20in%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1675268%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%2C%20i'm%20sure%20I%20didn't%20explain%20good%20enough%3B%3C%2FP%3E%3CP%3EWe%20periodically%20receive%20customer%20Boms%20(bill%20of%20materials)%20with%20alternate%20part%20numbers%20on%20each%20row%20(for%20the%20same%20part%20number%20%26amp%3B%20reference%20designator)%3B%20I%20however%2C%20need%20them%20in%20ONE%20cell%3B%20not%20in%20merged%20cells.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAgain%2C%20what%20I'm%20doing%20isn't%20merging%20at%20all%2C%20its%20combining%20data%20from%20three%20cells%20in%20a%20column%2C%20that%20each%20refer%20to%20a%20same%20part%20number%2C%20and%20then%20placing%20them%20into%20one%20cell%20in%20the%20same%20column.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20need%20to%20place%20multiple%20info%2C%20for%20example%20-%20part%20numbers%2C%20vendor%20names%2C%20customer%20part%20numbers%2C%20etc%2C%20into%20their%20own%20separate%20cells%20which%20are%20NOT%20merged%3B%20on%20one%20row%20for%20each%20unique%20part%20number%20%26amp%3B%20its%20Reference%20Designator%20group.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20screenshots%20i%20provided%20did%20not%20have%20any%20merged%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps%3CBR%20%2F%3Ethanks%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1677907%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20Cells%20in%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1677907%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796189%22%20target%3D%22_blank%22%3E%40dcd2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3EThe%20screenshots%20i%20provided%20did%20not%20have%20any%20merged%20cells.%3C%2FEM%3E%20So%20maybe%20this%20is%20a%20good%20illustration%20of%20why%20an%20image%20conveys%20confusing%20information%20compared%20with%20the%20upload%20of%20the%20actual%20spreadsheet.%20I%20trust%20you%20can%20see%20why%20they%20look%20to%20be%20merged.%20(It%20is%20possible%20to%20merge%20cells%20vertically%20as%20well%20as%20horizontally%2C%20and%20that%20is%20definitely%20how%20these%20appear.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22merge.png%22%20style%3D%22width%3A%20542px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219117i36583F84A026A6FA%2Fimage-dimensions%2F542x57%3Fv%3D1.0%22%20width%3D%22542%22%20height%3D%2257%22%20title%3D%22merge.png%22%20alt%3D%22merge.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20of%20that%20having%20been%20said%2C%20it%20still%20isn't%20clear%20then%20what%20you%20are%20wanting%20to%20do.%20Maybe%20more%2C%20what's%20the%20outcome%20you're%20expecting%3F%20What%20will%20you%20be%20doing%20WITH%20this%20data%3F%20Why%20is%20it%20that%20you're%20putting%20such%20emphasis%20on%20combining%20those%20three%20rows%20into%20one%20(OK%2C%20not%20%22merging%22%20per%20se%2C%20but%20%3CEM%3E%22combining%20data%20from%20three%20cells%20in%20a%20column%2C%20that%20each%20refer%20to%20a%20same%20part%20number%2C%20and%20then%20placing%20them%20into%20one%20cell%20in%20the%20same%20column%22%3C%2FEM%3E%3F)%26nbsp%3B%20WHY%3F%20What%20does%20that%20get%20you%20that%20leaving%20that%20redundant%20data%20on%20each%20row%2C%20where%20it%20is%20differentiated%20from%20the%20other%20very%20similar%20parts%20by%20the%20other%20codes%2C%20doesn't%20get%20you%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20the%20perspective%20of%20a%20good%20database%2C%20I%20don't%20understand%20the%20reason%20for%20doing%20it%2C%20beyond%20a%20certain%20visual%20clarity.%20And%20as%20I%20noted%20at%20the%20start%2C%20it%20would%20interfere%20with%20other%20data%20retrieval%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1678338%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20Cells%20in%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1678338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20all%20due%20respect%2C%20you%20don't%20need%20to%20know%20WHY%20I%20need%20to%20do%20what%20I've%20already%20explained%20-%20just%20that%20I%20need%20to%20do%20it.This%20is%20a%20work%20related%20requirement%2C%20not%20mine.%3CBR%20%2F%3Ethanks%20anyway%20for%20your%20help%20-%20i'll%20look%20elsewhere%20for%20my%20answers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1678725%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20Cells%20in%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1678725%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796189%22%20target%3D%22_blank%22%3E%40dcd2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20realize%20that%20asking%20%22Why%3F%22%20may%20sound%20impertinent.%20I%20have%20learned%20through%20many%20years%20of%20process%20consulting%20that%20asking%20%22Why%3F%22%20is%20often%20very%20helpful%2C%20getting%20at%20underlying%20objectives%2C%20%3CU%3E%3CSTRONG%3Eends%2C%20results%20or%20outcomes%3C%2FSTRONG%3E%3C%2FU%3E%20that%20may%20well%20be%20better%20served%20by%20different%20%3CU%3E%3CSTRONG%3Emeans%3C%2FSTRONG%3E%3C%2FU%3E%20than%20have%20been%20assumed.%20So%20although%20I%20apologize%20for%20not%20asking%20it%20graciously%20enough%2C%20I%20would%20suggest%20that%20%3CEM%3E%3CSTRONG%3Eyou%3C%2FSTRONG%3E%3C%2FEM%3E%20might%20want%20to%20ask%20those%20who've%20given%20it%20to%20you%20as%20a%20%22work-related%20requirement.%22%20I've%20been%20in%20that%20situation%2C%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20in%20asking%2C%20in%20other%20words%2C%20was%20to%20help%20you%20(and%20those%20you%20work%20for)%20serve%20the%20underlying%20objective%2C%20which%20hasn't%20yet%20been%20articulated%20in%20this%20exchange.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Need help in combining three columns, each with three cells with different text - that share the same part number in a different column.

 

Want to combine (in this example), the three cells per column in its own cell; the info from the three cells combined in one cell in a vertical fashion.

 

Please see the before & after screenshots of example

before combining:

dcd2020_0-1600281497235.png

 

after combining:

dcd2020_1-1600281715460.png

 

Thanks!

 

 

8 Replies
Highlighted

@dcd2020 

 

Why? So it looks prettier? That's not really a sufficient reason.

 

You'd be messing up your ability to take advantage of Excel's ability to work with a data table to produce summary reports, Pivot Tables, whatever...

 

So I'd advise against it.

 

If you insist on doing it, Format....Cells....Alignment and select "Merge Cells" and then deal with where you want the text to appear.

 

But as noted, it's not generally a good practice.

Highlighted

@mathetes 


Thanks, i'm sure I didn't explain good enough;

We periodically receive customer Boms (bill of materials) with alternate part numbers on each row (for the same part number & reference designator); I however, need them in ONE cell; not in merged cells.


Again, what I'm doing isn't merging at all, its combining data from three cells in a column, that each refer to a same part number, and then placing them into one cell in the same column.


I need to place multiple info, for example - part numbers, vendor names, customer part numbers, etc, into their own separate cells which are NOT merged; on one row for each unique part number & its Reference Designator group.

 

The screenshots i provided did not have any merged cells.

 

Hope this helps
thanks again

Highlighted

@dcd2020 

You wrote: The screenshots i provided did not have any merged cells. So maybe this is a good illustration of why an image conveys confusing information compared with the upload of the actual spreadsheet. I trust you can see why they look to be merged. (It is possible to merge cells vertically as well as horizontally, and that is definitely how these appear.)

 

merge.png

 

All of that having been said, it still isn't clear then what you are wanting to do. Maybe more, what's the outcome you're expecting? What will you be doing WITH this data? Why is it that you're putting such emphasis on combining those three rows into one (OK, not "merging" per se, but "combining data from three cells in a column, that each refer to a same part number, and then placing them into one cell in the same column"?)  WHY? What does that get you that leaving that redundant data on each row, where it is differentiated from the other very similar parts by the other codes, doesn't get you?

 

From the perspective of a good database, I don't understand the reason for doing it, beyond a certain visual clarity. And as I noted at the start, it would interfere with other data retrieval functions.

Highlighted

@mathetes 

With all due respect, you don't need to know WHY I need to do what I've already explained - just that I need to do it.This is a work related requirement, not mine.
thanks anyway for your help - i'll look elsewhere for my answers.

Highlighted

@dcd2020 

 

I realize that asking "Why?" may sound impertinent. I have learned through many years of process consulting that asking "Why?" is often very helpful, getting at underlying objectives, ends, results or outcomes that may well be better served by different means than have been assumed. So although I apologize for not asking it graciously enough, I would suggest that you might want to ask those who've given it to you as a "work-related requirement." I've been in that situation, too.

 

My goal in asking, in other words, was to help you (and those you work for) serve the underlying objective, which hasn't yet been articulated in this exchange.

Highlighted

@mathetes 

 

Understood - thanks again!

Highlighted

@dcd2020 

As options you may use

- PivotTable using the measure to concatenate texts . Sample is here https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/ , the only to use line feed (char(10)) instead of comma as delimeter

- PowerQuery - query main table, reference on it and in second keep only unique values, merge both and combine texts where needed with line feed as separator

- with formula like here to combine texts in additional columns, after that remove duplicates for entire range on ID

image.png

Don't forget to set Wrap Text in alignments for combined cells

Highlighted

@Sergei Baklan 

 

Thanks for your help - I'll check this out and see how it goes.