Basic Data Arrangement

[JosBorg]

I have been given some data in a text file that I need to enter into Excel which is in the following format...the values i am entering here are random ones;

1. abcdefg
2. hijklmn
3. opqrst
...
...
223. uvwxyz

1. 0.00001
2. 0.03446
3. 0.34467
...
...
223. 0.75647

1. 0.23344
2. 0.23464
3. 0.98463
...
...
223. 0.23443

etc. etc.

This means that I have a list of sets, each set containing 223 values, where the first value in one set corresponds to the other first value in the other sets and so on. There are about 100 of these sets, each containing 223 values. The sets are currently separated with an empty row (this might be of help, I don't know). I would like an efficient way to get all the corresponding values in a row next to each other; i.e. i want to start a new column with every set.

Any ideas anyone?

Thanks for reading and best regards

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG11Sep22
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
  Ac = Ac + 1
  Dn.Cut Cells(1, Ac)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hiker95]

JosBorg,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?


The below macro will only work correctly if each group in column A contains 223 rows or data.


Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	1. abcdefg
2	2. hijklmn
3	3. opqrst
4	...
5	...
6	223. uvwxyz
7
8	1. 0.00001
9	2. 0.03446
10	3. 0.34467
11	...
12	...
13	223. 0.75647
14
15	1. 0.23344
16	2. 0.23464
17	3. 0.98463
18	...
19	...
20	223. 0.23443
21
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	1. abcdefg		1. abcdefg	2. hijklmn	3. opqrst	...	...	223. uvwxyz
2	2. hijklmn		1. 0.00001	2. 0.03446	3. 0.34467	...	...	223. 0.75647
3	3. opqrst		1. 0.23344	2. 0.23464	3. 0.98463	...	...	223. 0.23443
4	...
5	...
6	223. uvwxyz
7
8	1. 0.00001
9	2. 0.03446
10	3. 0.34467
11	...
12	...
13	223. 0.75647
14
15	1. 0.23344
16	2. 0.23464
17	3. 0.98463
18	...
19	...
20	223. 0.23443
21
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()
' hiker9, 09/11/2014, ME804750
Dim Area As Range, sr As Long, er As Long, nr As Long
Application.ScreenUpdating = False
nr = 0
For Each Area In Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    nr = nr + 1
    Range("C" & nr).Resize(, .Rows.Count).Value = Application.Transpose(Range("A" & sr & ":A" & er))
  End With
Next Area
Columns.AutoFit
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.

 

[JosBorg]

Several thanks to both! Both solutions worked! I am using a Mac and MS Excel for Mac 2011, which means that I had to use the developer tool to enter code and then simply run it...thanks a lot for your replies and apologies for my late feedback!

 

[hiker95]

JosBorg,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.