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!

 

[LoyalLegend]

LoyalLegend,



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%21189

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.

Thank you so much for the feedback, but no, im not having a bunch of good people doing my homework, if anything I am learning greatly from the examples, and actually if you look back, the first thing I stated was that I wanted to learn how to do things myself, which I am, anyone cant just NOT learn from the examples given in today, dont you think? @hiker95

 

[LoyalLegend]

@István, exactly what I need it thanks, Do I just copy paste them, or did you do anything before to link them to each number? I want to be able to have that date next to it when I do the sorting.

 

[István Hirsch]

@István, exactly what I need it thanks, Do I just copy paste them, or did you do anything before to link them to each number? I want to be able to have that date next to it when I do the sorting.

I think you should copy the date column (column A in your video) and paste them into the blank columns next to the number columns. As for me, I had to type them as I am in another date system, so the dates after pasting were displayed in „Hungarian”. If the dates are not displayed correctly in your system, you may have to change the number type of the cells where the dates are pasted.

 

[hiker95]

LoyalLegend,

For the following macro will work correctly now based on my screenshots, if the date is in cell B1. I can change this later.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	01 04 12 35 45	3/1/14
2	01 02 09 75 46
3	02 08 12 45 55
4	08 45 12 32 48
5	16 65 12 32 87
6
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	01 04 12 35 45	3/1/14		01	3/1/14	02	3/1/14	09	3/1/14	32	3/1/14	45	3/1/14
2	01 02 09 75 46			01	3/2/14	04	3/2/14	12	3/2/14	32	3/2/14	46	3/2/14
3	02 08 12 45 55			02	3/3/14	08	3/3/14	12	3/3/14	35	3/3/14	48	3/3/14
4	08 45 12 32 48			08	3/4/14	45	3/4/14	12	3/4/14	45	3/4/14	55	3/4/14
5	16 65 12 32 87			16	3/5/14	65	3/5/14	12	3/5/14	75	3/5/14	87	3/5/14
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 11/03/2014, ME815560
Dim lr As Long, d As Range, s, t As Long, c As Long, sd As Date
Application.ScreenUpdating = False
With Sheets("Sheet1")
  sd = .Cells(1, 2)
  .Columns("D:M").ClearContents
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each d In .Range("A1:A" & lr)
    s = Split(d, " ")
    c = 4
    For t = LBound(s) To UBound(s)
      With .Cells(d.Row, c)
        .Value = s(t)
        .NumberFormat = "00"
      End With
      If d.Row = 1 Then
        With .Cells(d.Row, c).Offset(, 1)
          .Value = sd
          .NumberFormat = "m/d/yy"
        End With
      End If
      c = c + 2
    Next t
  Next d
  For c = 4 To 12 Step 2
    .Range(.Cells(1, c), .Cells(lr, c)).Sort key1:=.Cells(1, c), order1:=1
    With .Range(.Cells(2, c + 1), .Cells(lr, c + 1))
      .FormulaR1C1 = "=R[-1]C+1"
      .Value = .Value
      .NumberFormat = "m/d/yy"
    End With
  Next c
  .Columns("D:M").AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.