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?
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
RE: Sorting Functions
RE: Sorting Functions
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
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
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
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
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
OK, I'd certainly agree with that.
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
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
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
Good luck!
RE: Sorting Functions
Cheers!