Excel: moving data from many columns into 3.

Whitebox / MINE
May 27, 2015 at 05:46:43
Specs: Windows 7, e6850/4GB
I have data in cells scattered across mutliple rows and columns, and I want to move them all into just three columns. So far, I've found one way to do this:

1. Highlight all the cells, and use the formula on the page below to move all the data into ONE column:

http://www.extendoffice.com/documen...

2. Then, use the formula on the page below to turn that ONE column of data into THREE columns.

http://www.extendoffice.com/documen...

Is there a quicker way? As in, just one formula where I can say "take all the data from cells A1 to G30, and move/paste them into three sorted columns?


See More: Excel: moving data from many columns into 3.

Report •

#1
May 27, 2015 at 07:41:31
It might help if we had a better idea of what you are starting with.

When I read the words "cells scattered across multiple rows and columns" I picture pieces of data appearing somewhat randomly, here and there, with no semblance of order. However, when I click on the first link in your post, I see a very neat table of data, 3 columns wide by 5 rows high - not what I would considered "scattered".

I don't believe that the formula offered at that site will work for data that is truly "scattered" in the manner that I am picturing it.

Assuming that you don't have a neat table of data as shown at that site, please click on the following line and read the instructions on how to post example data in this forum and then post a short example of your data layout so that we know what we are working with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 27, 2015 at 12:25:34
0178 RED/WHI OS									
0181 RED OS									
		1377 RED/BLK S	1377 RED/BLK M	1377 RED/BLK L	1377 RED/BLK XL	1377 RED/BLK S	1377 RED/BLK M	1377 RED/BLK L	1377 RED/BLK XL
		1378 BLK/RED S	1378 BLK/RED M	1378 BLK/RED L	1378 BLK/RED XL	1378 BLK/RED S	1378 BLK/RED M	1378 BLK/RED L	1378 BLK/RED XL
9114 RED OS									
9116 RED OS									
9117 RED/BLK OS									
9118 RED/BLK OS									
9121 FUCH OS									
									
9123 FUCH OS									
9124 RED/BLK OS									
9126 Royal OS									
9127 Royal OS									
									
									
9135 RED OS									
9140 BLK OS									
9144 GRN OS									
							0178x RED/WHI Plus		
							9114x RED Plus		
							9116x RED Plus		
							9127x Royal Plus		
							9140x BLK Plus		
							9144x GRN Plus		


Report •

#3
May 27, 2015 at 12:55:55
Basically, I highlight all the cells (including the blank ones), and use the formula in link #1 to move them all into one column. Then I tell Excel to select and delete all the zeroes (the formula changes the empty cells into "0").

Then, with all the cells in one tidy column, I use the formula in link #2 to make that one column into three columns. I have to Copy/Paste Special(values) a couple of times to make it work, though.


Report •

Related Solutions

#4
May 27, 2015 at 16:01:23
You may have noticed that the example table on the How To included Column letters and Row numbers. The example data that you have posted does not.

It's hard to tell what Columns your data is in, especially since the values ending in "x" don't seem to line up with the values above them.

In addition, without Column Letters, I can't tell if this data is in 1 Column or 2, or 3:

0178 RED/WHI OS

I also don't know what your desired output is supposed to look like.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 28, 2015 at 04:25:03
I've been trying for 1/2 hour now, trying to paste the data from my example spreadsheet into here using the PRE command... but the result is a garbled mess, since the Preview moves the data into weird places. Either it doesn't recognise blank cells, or the data is too long. Or something else entirely. I don't know. I tried.

Here's an image instead.

http://imgur.com/DXkP7uG

Data is in scattered cells, and I need to move them all into 3 columns. Again, using the links above, I can use the formula to move them all into 1 column, then use another formula to turn that 1 column into three. But again, I wish there was a more efficient way.


Report •

#6
May 28, 2015 at 06:19:41
re: Either it doesn't recognise blank cells, or the data is too long.

Data that is too long can be an issue when using the pre tags, but the pre tags are the only way for the text editor of this forum to recognize consecutive blank spaces. In addition, pasting data from an Excel spreadsheet directly into the editor typically inserts "tabs" as opposed to blank spaces. You can tell by trying to select the blank space between the data elements. If a large chunk gets selected, then it's a tab, not a series of spaces. Oft times, replacing those tabs with multiple spaces allows the user to line up the columns correctly.

While frustrating, it can often take multiple interactions of previewing and editing to get the data layout to post correctly.

Unfortunately, imgur.com is blocked by my company’s servers, so I cannot view your image. I can look at it this evening (EST) but I also have to add that my long weekend starts tonight and I will not have easy access to Excel nor have much time to work on your question. If you don’t hear back from me it is not because I am ignoring you, it’s simply because I will outdoors for the vast portion of the next 3 days.

Let me ask you this:

Does your data look more like this:

Input Option 1:

            A                   B                C
1     9135 RED OS
2     9140 BLK OS
3     9144 GRN OS
4                                          0178x RED/WHI Plus
5                                          9114x RED Plus
6                                          9116x RED Plus

or this?

Input Option 2:

        A     B    C       D       E       F        G  
1     9135   RED   OS
2     9140   BLK   OS
3     9144   GRN   OS
4				 0178x   RED/WHI   Plus
5				 9114x   RED       Plus
6				 9116x   RED       Plus

Are you trying to end up with this:

Output Option 1:

            A               B              C          
1     9135 RED OS     9140 BLK OS    9144  GRN OS
2     9140 BLK OS     9144 GRN OS    0178x RED/WHI Plus

or this?

Output Option 2

        A      B         C          
1     9135    RED        OS
2     9140    BLK        OS
3     9144    GRN        OS
4     0178x   RED/WHI   Plus
5     9114x   RED       Plus
6     9116x   RED       Plus

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 28, 2015 at 12:31:53
Input Option 1 and Output Option 1.

And thanks for sticking with this. I really appreciate it.

(imgur is blocked? That's... rather mean-spirited.)


Report •

#8
May 28, 2015 at 13:04:26
punistation

If you are doing a Copy/Paste directly from Excel,
then the site interprets the space between cells as TAB's

I have found that the < PRE > tags do not like TAB's
it can really make everything look awful.

Try an intermediate step of first changing the TAB's to SPACEs
I usually use a TEXT Editor, like JEDIT or Notepad to get everything
formatted, then I just need to tweek it a bit when I post it here.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#9
May 28, 2015 at 13:26:03
I also need to know this:

Is there some specific order that the output needs to be in?

For example, with the "scattered" data you posted in Response #2, I assume you want it all condensed into Columns A:C per Output Option #1.

The question is this: Does it matter which piece of the scattered data ends up in A1, B1, C1, then A2, B2, C2, etc.?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#10
May 30, 2015 at 02:35:58
"Is there some specific order that the output needs to be in? "

Yes, absolutely. Numerical.


Report •

#11
June 1, 2015 at 07:23:15
Please don't take this the wrong way, but we're 11 posts into this thread and we still don't have a clear understanding of your requirements. Since you are the only one that knows exactly what you are trying to to do, you have to be very specific and very detailed in your explanations. History has shown that when we make assumptions based on limited information, we usually end up doing a lot more work than necessary.

Regarding your latest response "Yes, absolutely. Numerical."...

You've asked us to build a table that is 3 columns wide. As soon as you have a table that is more than one column wide, "numerical" becomes an ambiguous answer.

Technically, we could consider both of these tables to be in "numerical order", yet the output is very different:

        A         B         C
1       1         2         3
2       4         5         6
3       7         8         9

and...

        A         B         C
1       1         4         7
2       2         5         8
3       3         6         9

In addition, you have values that start with 4 digits as well as values that start with 4 digits followed by an "x":

9114 RED OS
9114x RED Plus

We could easily assume that 9114 is numerically before 9114x but, as I said, once we start making assumptions, we may end up doing extra work if that assumption is wrong.

Finally, you have values that begin with the same 4 digits, but the text following the numbers is different:

1377 RED/BLK S	
1377 RED/BLK M	
1377 RED/BLK L	
1377 RED/BLK XL

Once again, we could assume that you want these in numerical and then alphabetical order, but once again we may make the wrong assumption.

It would be really helpful if you could figure out how to use the pre tags and post a short table that shows what your output should look like, making sure that you include every type (not every piece) of data that you have so that we know what you are trying to accomplish.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
June 2, 2015 at 12:47:59
"It would be really helpful if you could figure out how to use the pre tags"

Tried for an hour. Failed.

Never mind.

Thanks, all.


Report •

#13
June 2, 2015 at 15:38:10
If you can't get the pre tags to work, then post a spreadsheet with some sample data - input and output - at a site such as zippyshare and then post the link back here. We want to help, but until we know your requirements, we are at a loss.

The only problem with posting to external sites is that some corporate servers block access which means that there will be longer delays since they can't be viewed until we are home. That is why I suggested the pre tags, but if you can't get them to work, you'll have to post your data externally and then tell us where it is.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question