Sorting Functions 4

[eit09]

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?

 

[eit09]

I had trouble opening the above attached file. Hopefully this file will work!

 

[flash3780]

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.

 

[zelgar]

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

 

[magoo2]

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.

 

[IDS]

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/

 

[flash3780]

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?

 

[IDS]

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.

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/

 

[IDS]

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/

 

[eit09]

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!!

 

[magoo2]

It's another way of writing not equal to.

Good luck!

 

[Enginerd9]

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!