Sep 16 2020 11:52 AM
Sep 16 2020 11:52 AM
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
Sep 16 2020 12:31 PM
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.
Sep 16 2020 01:06 PM
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
Sep 17 2020 05:01 AM
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.)
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.
Sep 17 2020 07:00 AM
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.
Sep 17 2020 07:38 AM
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
Don't forget to set Wrap Text in alignments for combined cells