×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sorting Functions
4

Sorting Functions

Sorting Functions

(OP)
  I have attached an Excel file to show a sample of the raw data output I need to sort. This output data I use could be from 20 rows to a couple of thousand rows so you can understand why I would like to find a better way than I currently use.

  The monotonous routine currently use is along Column C (the member column). As shown on the attached file down column C is M1800 and the subsequent 9 cells below are blank. What I usually do is copy M1800 and paste it in the 9 blank cells. I do this all the way down the rows until I reach the end of my data.

  As stated, the data I use never has the same amount of rows so I don't know how to use excel to auto fill all the data in the blank cell down column C. The reason I need to fill in all the blank cells in column C is so I can use the sort features of excel to keep all the correct member data with the correct values. Does anyone know a function in excel I could look into or explain some way of making this routine in Excel quicker?
 

RE: Sorting Functions

You could write a simple Octave or Matlab script to sort your data. I've used the xlsread and xlswrite functions for this sort of thing before. Spreadsheet programs become unwieldy when dealing with large amounts of data.

RE: Sorting Functions

you can use a simple macro to do this

Step 1:  Determine the numner of records
Option A:  in Cell F2 insert the following: =COUNT(F3:F65536)-1
and have the macro reference Cell F2 (e.g., num = Range("F2")

Option B:  the macro begins with a count
start = 4    'the row where the data starts
num = -1     '
for i = start to 65536
   if Range("F"+trim(str(i)))>0 then num=num+1
next i

Step 2:  Copying the Member's name
Start = 4
member$ = Range("c" + Trim(Str(Start)))
For x = Start To Start + num
    If Range("c" + Trim(Str(x))) = "" Then
        Range("c" + Trim(Str(x))) = member$
    Else
        member$ = Range("c" + Trim(Str(x)))
    End If
Next x
End Sub

(Now, if you want to fill up both Columns C and D so that there aren't any blanks so that the Sec column might not get messed up in the sort; have the macro look like this:)
Start = 4
member$ = Range("c" + Trim(Str(Start)))
sec = Range("d" + Trim(Str(Start)))
For x = Start To Start + num
    If Range("c" + Trim(Str(x))) = "" Then
        Range("c" + Trim(Str(x))) = member$
        If Range("d" + Trim(Str(x))) = "" Then
            Range("d" + Trim(Str(x))) = sec
        Else
            sec = Range("d" + Trim(Str(x)))
        End If
    Else
        member$ = Range("c" + Trim(Str(x)))
        sec = Range("d" + Trim(Str(x)))
    End If
Next x

RE: Sorting Functions

3
I think we're getting too complicated.

Why not insert this in G4

=if(C4<>"",C4,G3)

Copy it down the column and you can now use the G column to do your sort.

RE: Sorting Functions

Quote:

You could write a simple Octave or Matlab script to sort your data. I've used the xlsread and xlswrite functions for this sort of thing before. Spreadsheet programs become unwieldy when dealing with large amounts of data.

Yes, if only Excel had a built in programming language so we didn't have to use Octave or Matlab to do a simple customised sort.

Oh, wait a minute, it does.  There are loads of VBA sort routines available, here's one of mine:
http://newtonexcelbach.wordpress.com/2009/03/23/a-sort-function/

But in this case I think magoo2's suggestion may well be all you need anyway (depending on how much time you want to spend on automating it).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Sorting Functions

Quote (IDS):

Yes, if only Excel had a built in programming language so we didn't have to use Octave or Matlab to do a simple customized sort.
Haha, do I sense sarcasm? Yeah, I suppose that using Matlab is sort of like using a sledge hammer as a fly swatter in this case. But, at the end of the day it depends whether you're more comfortable in VBA or Matlab.

I suppose that what I was getting at is you can write a simple program to sort your data any way you want. Don't be afraid to stray away from the GUI. :)

On a somewhat related note: Did I read that Microsoft stopped providing VBA with Excel in the latest release?

RE: Sorting Functions

Quote:

I suppose that what I was getting at is you can write a simple program to sort your data any way you want. Don't be afraid to stray away from the GUI. :)

OK, I'd certainly agree with that.

Quote:


On a somewhat related note: Did I read that Microsoft stopped providing VBA with Excel in the latest release?

VBA was dropped from the Mac version (2008 I think) and will be reintroduced in the next one.

It wasn't dropped from the Windows version, and I can't see them doing that, although they don't seem to be keen to spend any resources developing it either.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Sorting Functions

Quote:

VBA was dropped from the Mac version (2008 I think) and will be reintroduced in the next one.

Just noticed that there is now Office 2011 for Mac, with VBA.


I also notice that the Cnet (fairly lengthy) review of the product did not think it worth mentioning this fact.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Sorting Functions

(OP)
magoo2,

That is exactly the little trick I needed! Could you explain what the <> in the formula is doing. Just want to understand the formula I will now be using so often.

Thanks again!!

RE: Sorting Functions

It's another way of writing not equal to.

Good luck!

RE: Sorting Functions

I wrote a very similar sort macro before in one of my undergraduate classes. If I can find it, I'll load it up for you guys. Basically, it takes blank rows out and re-fills the data table with continuous data, then it sorts by whatever parameter you need (with some editing... mine sorted checmicals by the number of carbons in their empiricle formula). It will take some doing, but I might be able to find that file on one of my old hard drives.

Cheers!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources