How to move info from one cell into another, big column several cells. Please help

[LoyalLegend]

So hello to all forum members, and I want to point out that while it is pretty lame of me to ask assistance of members immediately after registering for the forum, I must say in my defense that I have a time limit on a dead line and it would be insanely helpful if you could just assist me for my class in data base statistic. Every opinion will be appreciate it and I promise to provide with what little I may know on excel or related knowledge. It's for a statistic assignment and its not like I don't wanna do the work, it's just darn hard as hell, I mean this is kind of working on it since I'm posting just about everywhere for help lol.


So here it goes:
I have a sheet with a column of about 1,200 cells with series of numbers like this, five in total:
01 04 12 35 45
01 02 09 75 46
02 08 12 45 55
08 45 12 32 48
16 65 12 32 87

I need to do a couple things with this long list.
1. Is there any way to divide each pair of numbers so that each pair occupies a column of its own, giving thus each pair a column each? What I want to do then is, re-order each column in ascending order to know how many of each exact pair of numbers there are, for example:
01 08 16
01 08 16
01 08
01 08
01
So then I could clearly count six "01"'s, four "08"'s and three "16"'s that originally where in the first pairs out of the six total, originally. So now, I would only have to move on the next pair of that initially 'six' pair. So if I had twelve thousand cells in a column of six-paired numbers(eg 03 12 34 54 66) now I have five that I would need to give a column to each to know how many of each pair of numbers there were in the second series of these..(so if i had 03 12 34 54 66, and I successfully took out the first in each cell, I would now respectively have 12 34 54 66, meaning from the second and on on each cell.) Please see attached video

2. The last piece of the puzzle would be to perform, if at any way or fashion possible, a sorting in order of each individual pair of number in their new column by the date they were populated on that database.
So again:
If,
01 04 12 35 45 3/1/14
01 02 09 75 46 3/2/14
02 08 12 45 55 3/3/14
08 45 12 32 48 3/4/14
16 65 12 32 87 3/4/14

then I want to try and divide yes, each of those numbers, into separate columns, which I will later copy paste unto a new sheet, but now, eureka, now they have dates! Hehe, So now, I could perform my other menial tasks on each sheet more closely and clearly as I would have something like:
01 3/1/14 04 3/1/14 12 3/1/14
01 3/2/14 02 3/2/14 09 3/2/14
02 3/3/14... 08 3/3/14... 12 3/3/14...

And so forth. My ideal goal would be to not only be able to divide the 1st,2nd,3rd,4th,5th pairs into a column each, but if at all possible, to do so accompanied by their populated date in a cell next to them that also allows me sort the numbers in ascending order, while keeping that original date next to them.

Thank you guys, I'll owe you so much!

 

[hiker95]

LoyalLegend,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


We can not tell where your raw data is located, cells, rows, columns, and, we can not tell where the results should be, cells, rows, columns.


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[István Hirsch]

First, select column B, go to Edit/Replace and replace each single space with double space (enter one space into the Find box and two spaces into the Replace box).


Go to Data/Text to columns/Delimited then select "Space" as delimiter and make sure "Treat consecutive delimiters as one" is not checked. On the next window enter in the "Destinaton" box: $D$1 (or the cell address where you want the table to start).


Now press Finish; the numbers will be separated into columns D, F, H J and L. Copy column A and paste it into column E, G, I, K and M.


Then you can perform the required ordering.

 

[LoyalLegend]

LoyalLegend,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


We can not tell where your raw data is located, cells, rows, columns, and, we can not tell where the results should be, cells, rows, columns.


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

I tried but this darn thing... I even posted a vid of youtube.
https://www.youtube.com/watch?v=bKEA-5xVS2c&list=UU7kA2yL_LEhyWNu4vWGz56w&index=1

 

[hiker95]

LoyalLegend,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.


I will need to see your actual workbook with before, and, after worksheets.


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[LoyalLegend]

First I thought, GREAT! This is it! but the darn edit and replace wont search for a blank space or two blank spaces at that. But it worked! I tried a variation of it setting manually the break spaces then setting the target space for the result, and it worked! By George that it did! Now if only there'd be a way of getting these fine new numbers i've separated to have them each connected to the original date of the numbers in series.

 

[hiker95]

LoyalLegend,

When you respond to your helper, please use their site ID/username/handle.

This will keep thread clutter to a minimum and make the discussion easier to follow.

 

[István Hirsch]

@LoyalLegend

If you are referring to post #3, please have a look at the illustration below:

Excel Workbook
	A	D	E	F	G	H	I	J	K	L	M
24	Original state with the numbers
25	01 04 12 35 45
26	01 02 09 75 46
27	02 08 12 45 55
28	08 45 12 32 48
29	16 65 12 32 87
30
31	After replacing each space with double space
32	01 04 12 35 45
33	01 02 09 75 46
34	02 08 12 45 55
35	08 45 12 32 48
36	16 65 12 32 8
37
38	After Text to columns: Delimited with space, select a suitable destination (now $D$39)
39	01 04 12 35 45	1		4		12		35		45
40	01 02 09 75 46	1		2		9		75		46
41	02 08 12 45 55	2		8		12		45		55
42	08 45 12 32 48	8		45		12		32		48
43	16 65 12 32 87	16		65		12		32		87
44
45	After pasting the dates next to the numbers
46		1	3/1/14	4	3/1/14	12	3/1/14	35	3/1/14	45	3/1/14
47		1	3/2/14	2	3/2/14	9	3/2/14	75	3/2/14	46	3/2/14
48		2	3/3/14	8	3/3/14	12	3/3/14	45	3/3/14	55	3/3/14
49		8	3/4/14	45	3/4/14	12	3/4/14	32	3/4/14	48	3/4/14
50		16	3/4/14	65	3/4/14	12	3/4/14	32	3/4/14	87	3/4/14
Sheet

 

[hiker95]

LoyalLegend,

I must say in my defense that I have a time limit on a dead line and it would be insanely helpful if you could just assist me for my class in data base statistic.

Is this a homework assignment?


This site is a perfect place to further ones education in Excel, NOT, repeat NOT a place to provide FREE Applications. Show us your desire to "learn", not just "Can someone do my homework for me?"

We don't normally do homework .....but I'll try to get you started in the right direction.....


1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Can you post a screenshot (NOT the flat text you have displayed) of the actual raw data worksheet?

And, can you post a screenshot (NOT the flat text you have displayed) of the worksheet results (manually formatted by you) that you are looking for?

To post small screenshots try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[LoyalLegend]

LoyalLegend,

When you respond to your helper, please use their site ID/username/handle.

This will keep thread clutter to a minimum and make the discussion easier to follow.

got it