Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Major Excel Sorting Issue


  • Please log in to reply

#1
noobdisaster

noobdisaster

    New Member

  • Member
  • Pip
  • 8 posts
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?
  • 0

Advertisements


#2
gerryf

gerryf

    Retired Staff

  • Retired Staff
  • 11,365 posts
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.....
  • 0

#3
noobdisaster

noobdisaster

    New Member

  • Topic Starter
  • Member
  • Pip
  • 8 posts
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.
  • 0

#4
gerryf

gerryf

    Retired Staff

  • Retired Staff
  • 11,365 posts
why doesn't it work? Well, I guess I will see if I happen to be around...
  • 0

#5
noobdisaster

noobdisaster

    New Member

  • Topic Starter
  • Member
  • Pip
  • 8 posts
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.
  • 0

#6
noobdisaster

noobdisaster

    New Member

  • Topic Starter
  • Member
  • Pip
  • 8 posts
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
  • 0

#7
gerryf

gerryf

    Retired Staff

  • Retired Staff
  • 11,365 posts
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....
  • 0

#8
noobdisaster

noobdisaster

    New Member

  • Topic Starter
  • Member
  • Pip
  • 8 posts
Very, very nice! Thank you!
  • 0

#9
noobdisaster

noobdisaster

    New Member

  • Topic Starter
  • Member
  • Pip
  • 8 posts
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=
  • 0

#10
gerryf

gerryf

    Retired Staff

  • Retired Staff
  • 11,365 posts
that's odd....mine did put it one per row....did you grab my code exactly?

Was there a vertical space between the employees
  • 0

#11
noobdisaster

noobdisaster

    New Member

  • Topic Starter
  • Member
  • Pip
  • 8 posts
I copied it exact, and made a macro button with the code. Did I do it wrong?
  • 0

#12
gerryf

gerryf

    Retired Staff

  • Retired Staff
  • 11,365 posts
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


  • 0

#13
noobdisaster

noobdisaster

    New Member

  • Topic Starter
  • Member
  • Pip
  • 8 posts
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 :tazz:

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.

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP