Automatic vertical alignment of multiple cells into a single cell

**gomer121213** · 12-05-2012, 03:11 PM

I have 5 columns of data where each column of data has two number in it separate by a space where the headers for each column is c1, c2, c3, c4 and c5. for example

c1 c2 c3 c4 c5 c6 c7 etc
1 1 1 2 2 2 2 1 1 1
3 3 3 4 4 4 4 3 3 3
etc

where each of these number pairs is under a separate column. The preview option for this forum editor is showing quite a difference between intended presentation and actual...sorry

What I am looking to do is for each line item is to put the content of each row into a single cell with vertical alignment of the pairs of numbers. for example
c6
1 1
1 2
2 2
2 1
1 1

3 3
3 4
4 4
4 3
3 3

where each group of five pairs is in a single cell.

I am looking to do this in as automated an approach as possible. I dont want to have to ctrl-enter for example 4 times for each cell in c6 for 1000 different line items..
can anyone recommend a nice approach for this? thanks

I am noticing on preview that the formatting is all whacked..not sure how to address this. Sorry

**FDibbins** · 12-05-2012, 07:09 PM

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**gomer121213** · 12-05-2012, 07:45 PM

Thanks for the tips on clarifying the posting.

I have attached a sample workbook with the issue presented.
Columns O thru S contain the data pairs, that is two values separate by a space for each of these cells. Note that these pairs are simply obtained from the previous columns.
What I am looking to do is to operate on these columns and create an output which looks like that in Column U.
The main goal is to get to Column U, leveraging columns O thru S (or use any of the previous column source data is fine also).
I forced the output manually using 'Ctrl-enter' for these cells, but want to do this automatically so I can apply it to thousands of records.

thanks for your consideration.

**FDibbins** · 12-05-2012, 08:47 PM

try these 2 options. the 1st is from scratch and doesnt use O:S. the 2nd does use them.

=E2&" "&F2&CHAR(10)&G2&" "&H2&CHAR(10)&I2&" "&J2&CHAR(10)&K2&" "&L2&CHAR(10)&M2&" "&N2
=O2&CHAR(10)&P2&CHAR(10)&Q2&CHAR(10)&R2&CHAR(10)&S2

Note: make sure you format them as wrap text

**gomer121213** · 12-05-2012, 09:34 PM

Awesome.. perfect...thanks so much..

[SOLVED]

**FDibbins** · 12-05-2012, 09:38 PM

happy to help and thanks for marking the thread "solved" - it helps keep things neet and tidy lol, consider adding the reputation to those that helped (see point 3 below)