How to sort number follow cell accordingly

Hi, everyone!
I need to create macro that can sort numbers as below:
Column : Before
A   B  C  D  E  F
 1   2  3   4  5   6
 3  4   5
 1  3   4  5
 3  5   6
After
A   B  C  D  E  F
1    2  3   4  5   6
          3  4  5
1       3   4  5
        3        5   6
which mean same number follow the same column but not necessary follow
column A,B,C.....
Pls help with vba code.Thanks
0
geniusideas
1/29/2010 4:28:15 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
637 Views

Similar Articles

[PageSpeed] 56

GeniusIdeas,

You have to evaluate the assumptions in the program (i.e. read the comments 
and evaluate the code).  Basically, Integers are assumed for the sort, and 
the Integers start at 1 and each consecutive value is 1 plus the previous 
value.  If you are not familiar with the MATCH function, look it up with the 
Excel help.

Best,

Matthew Herbert

Sub CustomSortRoutine()
Dim Rng As Range
Dim rngCell As Range
Dim rngTemp As Range
Dim intArrHdr() As Integer
Dim varArr As Variant
Dim varItem As Variant
Dim intCnt As Integer
Dim lngCnt As Long
Dim varMatch As Variant

Application.ScreenUpdating = False

'assumes you are using Integers only
Set Rng = Selection

'create a lookup array and fill it will consecutive
'   numbers, starting at 1; assumes Option Base 0
'   which is the default setting
ReDim intArrHdr(Rng.Columns.Count - 1)
For intCnt = LBound(intArrHdr) To UBound(intArrHdr)
    intArrHdr(intCnt) = intCnt + 1
Next intCnt

'loop through each row of Rng
For lngCnt = 1 To Rng.Rows.Count
    
    'create a temporary range that references the
    '   entire row
    Set rngTemp = Rng.Rows(lngCnt)
    
    'load the current row's values into an array
    varArr = rngTemp.Value
    
    'clear the temporary range
    rngTemp.Clear
    
    'loop through each item in the array
    For Each varItem In varArr
        
        'match the varItem with intArrHdr.  If a match exists,
        '   offset the value to the appropriate position in the
        '   row.  (If no match, do nothing).
        varMatch = Application.Match(varItem, intArrHdr, 0)
        
        If Not IsError(varMatch) Then
            
            'offset from the far-left cell of the row, placing
            '   the matching value into the offset cell
            rngTemp.Cells(1, 1).Offset(0, varMatch - 1).Value = varItem
        End If
        
    Next varItem

Next lngCnt

End Sub


"geniusideas" wrote:

> Hi, everyone!
> I need to create macro that can sort numbers as below:
> Column : Before
> A   B  C  D  E  F
>  1   2  3   4  5   6
>  3  4   5
>  1  3   4  5
>  3  5   6
> After
> A   B  C  D  E  F
> 1    2  3   4  5   6
>           3  4  5
> 1       3   4  5
>         3        5   6
> which mean same number follow the same column but not necessary follow
> column A,B,C.....
> Pls help with vba code.Thanks
> .
> 
0
Utf
1/29/2010 7:45:47 PM
Thank matthew..Its work fine
0
geniusideas
1/29/2010 11:04:35 PM
Reply:

Similar Artilces:

Creating Numbered Tickets
I need 300 sequentionally numbered tickets created in Publisher 2003 on Win XP. I tried catalog merge but when I copied the ticket five down, all I got was five copies of ticket 10001. I don't need 300 sheets of paper! I need five tickets per sheet times 60 sheets. Can this be done in MS Publisher? Sure it can be done in Publisher. Figure out how large your ticket will be. You can only have one ticket on your screen for a merge to work. If you use the catalog merge, fill in only one catalog block. Lots of help here http://office.microsoft.com/en-us/publisher/CH062524751033.aspx I h...

How do I start an Excel entry with then number 0?
How do I start an Excel cell entry with the number 0? (i.e. a pin number or zip code). Every time I enter it, then move out of the cell, the computer automatically removes the 0 in front of the remaining numbers. format the cells text to begin with, or put a single quote ' in front of the zero before entering data go to Format-Cells-Text This will now allow you to enter the data with 0 prefixed to a number OR Enter ' before your number "cajundoc" <cajundoc@discussions.microsoft.com> wrote in message news:6AC74DB6-8F34-41C8-B532-F8B683DA74A4@microsoft.com... >...

Follow up folder
In Outlook 2003 there is a "Follow up" folder that collects all the flagged items. Is there a way of creating this in Outlook 2000? Thanks Tony Not really since Search Folders were added in OL2003. You might be able to create a saved search for Advanced Find. "Tony Williams" <tw@invalid.com> wrote in message news:%23pvjmG%23qGHA.4112@TK2MSFTNGP02.phx.gbl... > In Outlook 2003 there is a "Follow up" folder that collects all the > flagged items. Is there a way of creating this in Outlook 2000? Thanks Vince, is there something I could read to g...

Sheet protection causes cells to be linked
I'm using Excel 2000 and I've got a sheet which I want to protect, so can lock the non-input cells. I've also got a button on the shee which runs a macro. When I protect the sheet, all cells look as they are hyperlinked, o macro-linked (I get the hand pointer). If I click on the cell, it the goes off to do something (looks like a call to a macro - which ISN'T th one on the sheet!). If I use freeze pane, it's only the cells above the pane marker whic operate this way - the ones below the pane operate normally! Finally - if I open the sheet in Excel 2003 - all the cells op...

empty cell in formula
I have an Excel book but it's for advanced users and, I guess, this is so basic that the answer is not there. I have a really simple spreadsheet I use to replicate my time cards from work. for this example I have: 1 2 3 A report time start time stop time Sometimes there is a report time which is earlier than the actual work start time and sometime not. The difference between report time and start time is shown later as "stand by." I add the stand by time to the difference between start and stop time to get ...

Convert all cells contents in the sheet to values
I have a spread sheet at work that is linked up to other sheets. We keep the sheets on a shared drive so that everyone can make their updates. The main sheet which I operate is just linked to the others and picks up everybody's updates. Twice a week, I need to email this out to my boss. Is there a general function which converts all cells contents in the sheet to values that I can use before I sent this? I know the Paste Special feature can be used for this, but I have a lot of sheets and I want to get it done with a single keystroke. Isn't there a Save feature that simply writes out a...

colour cells, same numbers different worksheets
Hi, I need to colour cells that are the same number (10 digit cellphone number) if this number appears on sheet 2 it must colour cell with the same number in sheet one. If both sheets with the same number can be coloured. They can all be one colour, the point would be to see what numbers are not on both sheets. I hope that makes sense. Thanks If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what...

Adding data only if adjacent to cell labeled "male"/"female"
I have a spreadsheet that has this general format: column A column B Column C Column D 3 M 5 F 2 M 6 F Total Females: 11 Total Males: 5 For all the cells in Column A that are adjacent to a cell in column B containing a letter "F", I would a formula to add those numbers, and place the sum in column D (where I've t...

"bad file name or number"
Hi, After trying to install an add-in, Manager started giving me this error message: "bad file name or number". Mind you the add-in didn't install correctly, i un-installed RMS, i un-installed MSDE, installed it all again and Manager STILL doesn't work. All the other machines work fine with the add-in and all the SO apps. Administrator and POS still work fine on my machine. Any ideas? Thanks. Easy one. The registry entries don't change unless you delete them with regedit (not recommended). Fix: Go into Store Operations Administrator, File, Configuration, a...

How do I create a formula that searchs 1-cell for a range of numbe
I'm looking to search 1-cell for a range of numbers i.e I want to enter a formula in cell C-1 if cell A-1 is >16 or <30, I want the formula to provide me the info in cell B-1. If the criteria is not met, I want to see a null value. Let me know. Thanks! =IF(AND(A1>16,A1<30),A1,"") -- HTH RP (remove nothere from the email address if mailing direct) "Greg-B" <Greg-B@discussions.microsoft.com> wrote in message news:3C27CD0E-6536-4EAE-9BB5-0F4443970FBB@microsoft.com... > I'm looking to search 1-cell for a range of numbers i.e > I wan...

< of two cells result
Ok, 2 year break from excel and the grey cell has gone on holiday! I need to return a lesser than of two cells in cell g6 if c6 < h6, =c6, or if h6 < c6, = h6 tried several different ways, but to no avail, any help would b greatly appreciated . /--- Paul: -- Message posted from http://www.ExcelForum.com Have a look in HELP index for MIN -- Don Guillett SalesAid Software donaldb@281.com "Peeps >" <<Peeps.19p1g3@excelforum-nospam.com> wrote in message news:Peeps.19p1g3@excelforum-nospam.com... > Ok, 2 year break from excel and the grey cell has gone on holi...

Count cells based upon criteria in other cells
The cells in column J contains donations made by various individuals. Cells in column R contain certain member classifications. Am looking for a formula that looks at column R, then counts the number of entries in the corresponding cells in colum J for each member classsification. EX: in column R there are three classifications: RB1, RB2, RB3. Need to count how many donations in column J are made by each classification. Many thanks! Try the below formula =COUNTIF(J:J,"RB1") -- Jacob "JT" wrote: > The cells in column J contains donati...

Merged Cells #2
I have a worksheet that someone else set up but doesn't work here anymore. I keep getting an error message saying that there are merged cells and I can't sort or do anything like that. I can't find any merged cells but I am sure there must be some. Is there any way to find them or get rid of the problem in another way? Any help would be appreciated!! Amy Amy one way is to select all the cells on the sheet (click on the grey square at the top of the row numbers and to the left of the column headings). Then select Format | Cells... | Alignment Tab and then clear the Merge Cel...

Count Problem- cells with diagonal line
Hi I'm trying to count all cells in a range that have a diagonal lin accross the cell but so far no luck! :confused: Background: The Worksheet is an audit schedule and when an audit is completed diagonal (xldiagonalup) line is placed in the cell, for reporting I' like to obtain the total number of cells with the line. Would be sooooo grateful for a reply, I'm starting to loose the wil the live!!!!! Thanks to anyone who has taken the time to read this.... Ada -- Message posted from http://www.ExcelForum.com Hi Adam Push Alt+F11 and go to Insert>Module and in here past...

minimizing space between cell content for printing
Hi, Files printed from Excel 97 in Win98 allowed consequtive single line cells to overlap and the letters to touch which economized on space for volume printings. Printing on the same HP printer from Excel 2003 in Win XP looses half of the height of the letters. If it was a printer driver bug, I would like to figure out if I can force the same style of printing - minimizing space between rows to zero so the letters touch. (For example, a "b" would touch a "g" on the line above.) TIA. You can adjust the row height - highlight the rows in question and Format | Row | Height...

Line and row numbers
Line and row numbers ......... I took these out some time ago so that my sheet does not display them, how do i get them back in again thanks Tools-->Options, View tab. Check Row & Column Headings. ************ Anne Troy www.OfficeArticles.com "Scudo" <me@nospam.fictional> wrote in message news:OX8bf.43372$7x2.32069@fe1.news.blueyonder.co.uk... > Line and row numbers ......... I took these out some time ago so that my > sheet does not display them, how do i get them back in again > > thanks > > > On the menu, select Tools > Options. C...

Sorting Multiple Columns
We have a phone list that I think needs some improvement. The perso that maintains the company phone list and sends it via email is no very proficient in excel. This leads to many lists that need to b resent to everybody twice or three times every month because of errors Here is the way the phone list is set up and all the users have Exce 2002. A4:A64 has the employees first name, B has the last name, C ha the dept., D has the ext. then, there is a divider in the page and th same format is used starting at G4:G64 with the next first name in th group. We have some of our company logos in...

How do I enter a formula in a cell so that letters= a number i.e..
How do I enter a formula in a cell so that letters= a number i.e Y=5, N=2. Not very sure on your intents, but you might be thinking along these lines Set-up the reference table in say, Sheet2, cols A and B, from row1 down Y 2 N 5 Z 9 etc Then in Sheet1, if you input letters* in say, A1:A3 Z Y N *assumed case-insensitive you could put in B1: =VLOOKUP(A1,Sheet2!A:B,2,0) and copy B1 down to return the numbers corresponding to the letters in col A Z 9 Y 2 N 5 Alternatively, if you're entering single alphabets in col A, then putting in B1: =CODE(A1) and copying B1 down will also drive...

Can anyone explain to me why built in Cells.Find() is faster than a loop?
When coding in VB, why are the built in applications, particularly Cells.Find(), faster than loops? If you had to check 64k or 1MB cells in a column for a certain string that appears 3 times, .find would be lots faster. If you that string appears in every cell, then I bet the loop would be just as fast or faster (not tested, though). Will wrote: > > When coding in VB, why are the built in applications, particularly > Cells.Find(), faster than loops? -- Dave Peterson Part of the reason is because they're fully compiled and generally pretty well optimized. VB code is inter...

Dates to week numbers help.
I have a list of dates.....10/7/06, 17/7/06, 1/10/06 etc... These are in column B. I would like to add a week number into column c where 10/7/06 = week10 and 17/7/06=week 11 and 1/10/06 could be week 22. How can this be done please? Have been trying for ages to work this out. TiA mag()() http://www.cpearson.com/excel/weeknum.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Mag()()" <mrmagoo_ukNOSPAM@hotmail.com> wrote in message news:c_6dndzYydWioPrYnZ2dnUVZ8sOdnZ2d@eclipse.net.uk... |I have a list of dates.....10/7/06, 17/7/06, 1/10/06 etc... | | These...

Downloading Debit Transactions
I now have two debit cards attached to one account. When downloading my back account to Money, Money records the debit card number as the Payee and not the retail store. Prior to recieving the second debit card, the dowloading process worked just fine. Can anyone tell me how to solve this problem. It is very time consuming to go in and change each debit card transaction. Thanks. ...

Formatting 1 cell depending on a value of another.
Heres what I have: I have 1 sheet called TeamData and another sheet called MonthlyGain I want to format cells on MonthlyGain dependent on the values of cells on TeamData. Cells on TeamData are either numbers or blank cells. Heres the 1st cell and what I want it to do. I want MonthlyGain!A7 to, if TeamData!C2="", turn the text to red. I have tried the conditional formatting and setting my own formulae but it doesn't work. Any ideas would be appreciated and thankyou in advance. Got it figured. I can't access cells from another sheet. I had to copy the nee...

Converting numbers to text?
Here is what I am doing: I am opening some files in textpad(mainly because it will open and concatenate multiple files) and then sorting the data without deleting dupes. I then copy that data to excel and import as text. My problem is that there are several cells that have .01 and .010 extensions that excel still treats as the same even though they were imported as text and it throws off my sumIF formula. Has anyone had this problem, and if so please help? Thanks Sean -- apoc330 ------------------------------------------------------------------------ apoc330's Profile: http://www.exce...

Special Formatting of Number Field
I have a column that contains 5 digit numbers (12345) and I would like to have this column formatted to add 2 leading zeros, then the first digit, a dash, then the remaining 4 digits. For example, 12345 would be 001-2345. I would to be able to format this column of numbers automatically, and not have to do this for each cell. Select the column and choose Format/Cells/Number/Custom. Enter 000-0000 in the textbox. In article <29B63283-62A4-432E-9C32-AE090B4E1601@microsoft.com>, "weelhow" <weelhow@discussions.microsoft.com> wrote: > I have a column that con...

right justify number column within listbox
Hello everybody, Is there a way to right justify a column within a listbox containing number type data? thanks in advance, George. "George" <George@discussions.microsoft.com> wrote in message news:F0ADD978-D72C-4AF6-A8C0-5D228FE21214@microsoft.com... > Hello everybody, > > Is there a way to right justify a column within a listbox containing > number > type data? > > thanks in advance, George. There's no built-in way, but I have a (rather kludgey) method which calculates the number of "left padding" spaces to insert. Let me know if you...