[noobdisaster]

Ok, this will not be easy to explain, but I will try my best. I have data that is listed something like this:

Job=123
time=345
name=john doe
department=payment
blue
green badge

This data is all in one column and there may be more lines (not all entries have four rows as provided in the example, some may have 5, 10 13, etc.) of data, and all the data is not necessarily in the same order. There may be hundreds of these "packets" (for lack of a better term). I would like to be able to split the data into columns. Example:
JOB TIME Name Department
123 345 John Doe payment

Any ideas on how this can be accomplished?

[Advertisements]

Normally, one selects the data, and uses DATA > TEXT TO COLUMNS, but I may be misunderstanding you

not following you exactly....is this information confidential, or can you duplicate some dummy info and attach the excel file.....

[gerryf]

Normally, one selects the data, and uses DATA > TEXT TO COLUMNS, but I may be misunderstanding you

not following you exactly....is this information confidential, or can you duplicate some dummy info and attach the excel file.....

[noobdisaster]

Text to columns will not work for this application. I will post the particular data in question later today, as I do not have access to it at this moment.

Thanks.

[gerryf]

why doesn't it work? Well, I guess I will see if I happen to be around...

[noobdisaster]

Let me rephrase, I don't think it will work for my knowledge level of text to columns. I guess it does not mean that it can't work with text to columns. I could very well be wrong. I simply do not have the data here with me. I'm sorry.

[noobdisaster]

I managed to get some sample data. This is what it looks like. I removed some confidential info, bet this is essentially what it is.

[employee 1]
year=2000
average_score=1.27
hire date=0 1
class=1
time left=0
model=262
name=
pc data=0.91

[employee 3]
year=2000
score_curve=0.533
average_score=1.5
hire date=26 33
faction=21
class=24
curve=3
ddt=1024
time left=0
name=
ave score=0
pc data=1.09
type=6

[employee 6]
year=2000
score_curve=0.462
average_score=0.87
hire date=2 2
class=1
curve=6
ddt=104
time left=0
name=
ave score=0
size=0.700000
pc data=0.92
type=7

[gerryf]

the non-exact nature of your data makes this complicated....

you can write a vba script to transpose each dataset (employee and data) to automatically convert each set to a row, loop it, then sort the row to end up with this

[employee 1] year=2000 average_score=1.27 hire date=0 1 class=1 time left=0 model=262 name= pc data=0.91
[employee 3] year=2000 score_curve=0.533 average_score=1.5 hire date=26 33 faction=21 class=24 curve=3 ddt=1024 time left=0 name= ave score=0 pc data=1.09 type=6
[employee 6] year=2000 score_curve=0.462 average_score=0.87 hire date=2 2 class=1 curve=6 ddt=104 time left=0 name= ave score=0 size=0.700000 pc data=0.92 type=7
-----------------------------------------------------------------------------------------------------------


the code without the loop looks like this

Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveCell.Offset(0, -1).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Selection.End(xlDown).Select

took about 2 seconds to run through several hundred employees as I tested it.

Transpose is a function of excel--it is built in....you often access it by selecting text, copying, then pasting special, and selecting TRANSPOSE

You could expand the vba code to remove the undesirable field names (average_score, etc), leaving you with just the data/number, whatever.

Because your data alters from person to person, though, that would be a little dicey as the vba code cannot account for variations....

[noobdisaster]

Very, very nice! Thank you!

[noobdisaster]

One more thing. the data is transposed like this (all in one row):

year=2000 average_score=1.27 hire date=0 1 class=1 time left=0 model=262 name= pc data=0.91 [employee 3] year=2000 score_curve=0.533 average_score=1.5 hire date=26 33 faction=21 class=24 curve=3 ddt=1024 time left=0 name= ave score=0 pc data=1.09 type=6 [employee 6] year=2000 score_curve=0.462 average_score=0.87 hire date=2 2 class=1 curve=6 ddt=104 time left=0 name= ave score=0 size=0.700000 pc data=0.92 type=7


Is there a way to get the data like this (one employee per row)?

[employee 1] year=2000 average_score=1.27 hire date=0 1 class=1 time left=0 model=262 name= pc data=0.91
[employee 3] year=2000 score_curve=0.533 average_score=1.5 hire date=26 33 faction=21 class=24 curve=3 ddt=1024 time left=0
[employee 6] year=2000 score_curve=0.462 average_score=0.87 hire date=2 2 class=1 curve=6 ddt=104 time left=0 name=

[gerryf]

that's odd....mine did put it one per row....did you grab my code exactly?

Was there a vertical space between the employees

[noobdisaster]

I copied it exact, and made a macro button with the code. Did I do it wrong?

[gerryf]

hmm, don't know....worked for me....what version of excel are you using....should work in 2003, xp and 2000.....

In this case, it's set to start in the first cell selected, so only select a1....


are you looping it, or just holding down a macro key?


I've exported the module,,,,import it through the VBA editor and see if it makes a difference

Attached Files

•  Module1.zip   470bytes   90 downloads

[noobdisaster]

Ooo! It works! I was not holding the button down! OK, after the data is transposed, and it is nicely put in a row, it leaves empty cells (where the data was), is there a script that will delete these empty cells as the data is transposed and "pull" the rows up? I hope I'm not asking too much, as you have been brilliantly helpful, thus far

Edit:

Also, the button only seems to do one packet at a time. Is this how it should work, or should it do all at the same time?

Thanks!

Edited by noobdisaster, 22 July 2005 - 04:17 PM.