1. Please take 30 seconds to register your free account to remove most ads, post topics, make friends, earn reward points at our store, and more!  

Excel help

Discussion in 'Off Topic [BG]' started by SoComSurfing, Jun 2, 2014.


  1. SoComSurfing

    SoComSurfing Mercedes Benz Superdome. S 127. R 22. S 12-13.

    Feb 15, 2002
    Mobile, Al
    I want column A to number my rows, but have that number refresh when sorting for the data in column B. Anyone able to follow that?

    I want the numbers to be 1 through whatever when the data is unsorted, numbering all rows that are shown, but when I sort to compress the sheet and only show the set from column B or column D or whatever that I want, I don’t want column A to still show 4, 17, 53, etc. I want it to go back to 1 through whatever, so I can see the total number for that data set. Does that make sense?
     
  2. Gorn

    Gorn

    Dec 15, 2011
    Queens, NY
    I got really excited about answering an excel question, till I saw it. Thanks for making me feel bad about my excel expertise.
    I'm asking one of the IT guys. I'll post his response unless someone else answers first.
     
  3. If i understand correctly, you want to sort each row individually so the column values do not stay aligned after the sort). The following macro should do this (producing an ascending sort in each row, from row 2 to the last row having an entry in column A (column 1). It's included in the attachment.

    VB:

    Sub SortEachRow()

    Dim r AsLong

    Application.ScreenUpdating = False

    For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row

    Range(Cells(r, 2), Cells(r, Columns.Count)).Select

    Selection.Sort Key1:=Cells(r, 2), Order1:=xlAscending, Header:=xlGuess, _

    OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _

    DataOption1:=xlSortNormal

    Next r

    Range("A1").Select

    Application.ScreenUpdating = True

    End Sub
     
  4. machine gewehr

    machine gewehr

    Sep 17, 2005
    Istanbul
    Excel understands VB?o_O

    Never been intimate with Excel, I'm surprised and that's cool.
     
  5. Selta

    Selta

    Feb 6, 2002
    Pacific Northwet
    Total fanboi of: Fractal Audio, AudiKinesis Cabs, Dingwall basses
    Yup, has for a long time now too. If I the same way rtslinger and I both interperted SoCom's post correctly, I believe that VB will get the job done.
     
  6. fisticuffs

    fisticuffs Commercial User

    May 3, 2011
    Madison, WI
    Just higlight everything except row A (where your numbering is) and sort that. A will remain A and everything else will sort.

    I just discovered =SUMIFS Oh so much fun to be had... as in
    =SUMIFS(Sheet1!$I$2:$I$7828,Sheet1!$B$2:$B$7828,Sheet2!$B54)
     
  7. Exactly how I would do it. You can also use a combination of the "if" function and the "rows" function to do it.

    lowsound
     
    Last edited: Jun 3, 2014
  8. machine gewehr

    machine gewehr

    Sep 17, 2005
    Istanbul
    So VB has become a scripting language rather than a programming language? Aww.:)
     
  9. gttim

    gttim

    Dec 12, 2009
    Atlanta, GA

    Use =Row(). If the column starts in row 3, use =Row()-2. No need for any VBA or anything.
     
  10. gttim

    gttim

    Dec 12, 2009
    Atlanta, GA
    And if by "sort" you mean "filter", then it will not work.

    You can use =SUBTOTAL(2,A4:A14) to get a count of the rows that are in the filtered set. The range should be the A column and include all rows in the initial set.
     
    Last edited: Jun 3, 2014
  11. Suggested reading
    upload_2014-6-3_12-22-18.




    for anyone who wants to really learn Excel. (I build spreadsheets as part of my job using VB and SQL) my Bible so to speak was Using Excel Version 5 for Windows back in 1993 it is expert level it has everything you would need if learning VB is you goal these are two great books imo I'm not really a IT guy I just play one at work.
     
  12. sissy kathy

    sissy kathy Back to Bass-ics Gold Supporting Member

    Apr 21, 2014
    Arbutus, MD
    won't the record number do that?
     
  13. Tat2dHeart

    Tat2dHeart Only two strings away from an attitude problem.

    I'd answer this, but you've already got some workable solutions. :)
     
  14. Read the book?
     
  15. sissy kathy

    sissy kathy Back to Bass-ics Gold Supporting Member

    Apr 21, 2014
    Arbutus, MD
    WHAT!? and get EDUCATED? :roflmao:
     
  16. Gravedigger Dav

    Gravedigger Dav Supporting Member

    Mar 13, 2014
    Fort Worth, Texas
    not really a scripting language and not really VB. It uses VBA (Visual Basic for Applications) which is a version of VB tailored for the Office suite. There are some minor code differences (for example, in VB you have to specifiy the .text property of a control but in VBA it is not necessary), but mostly it is the extensive object model for each product that makes them different. Excel switched from the older macro model to VBA in version 5 (1993). All Microsoft Office modules have VBA. So does Outlook.
     
  17. Gravedigger Dav

    Gravedigger Dav Supporting Member

    Mar 13, 2014
    Fort Worth, Texas
    There is no such thing as a record number in Excel. Excel is a spreadsheet, not a database.
    And even it if were, absolute record numbers would be meaningless. You would need to resort and use relative record numbers.
     
  18. machine gewehr

    machine gewehr

    Sep 17, 2005
    Istanbul
    Cool. I used a variant of VB just like VBA. It was VB modified in a way to work with the ERP pioneers of Turkey's desktop application. Basicly, I could modify the desktop application with their variant of VB. Good ol' times...
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.