Posts by Mats Carlsson

    Hi!


    I hope I understand this right.?:duh:
    1.I would add another column called "group"
    2 mark the posts with group I-III (maybee you will need an =if(a1=then....to mark them) and
    3 .then sort by group first ant then the data column.


    Keep on XLing! /Mats

    Hi!


    I am answering my own post, what a great way to upgrade my position on the board:eureka:. I must get a really cool alias and ask all kinds of silly questions that I know the answer to:saint:.


    THE Solution.
    1. Ctrl a
    2. Ctrl c
    3. Insert worksheet.
    4. Paste special values.
    5. To check if the row is a "Totals" row or not. '=IF(ISERROR(FIND("Total",A1)),FALSE,TRUE)
    6. Sort on the "check" column.
    7. Delete False values.
    8. And :tongue: we have subtotals only from an area that are to complicated to XL.


    Now I´m of to get an alias.


    Take care! /Mats

    Hello!


    I have a large database of 46489 posts. I have used subtotals to add them up. In the past have I used Edit-Got to-special-visible cells only, to copy the subtotals to another sheet, but this time I get "too complicated area" from XL. Do anyone know of a workaround apart from the obvious move to Access. I use W2k, XL2K and have 512 mb ram.


    TIA :flower: Mats

    Hi!


    Will and Bill have valid points to remember, but it can be done. See this formula.


    =if(mid(A2;3;1)="0";left(A2;2)&"-"&mid(A2;3;1)&"-"&mid(A2;4;6)&"-"&right(A2;1);"")&if(mid(A2;3;1)="1";left(A2;2)&"-"&mid(A2;3;1)&"-"&mid(A2;4;6)&"-"&right(A2;1);"")&if(mid(A2;3;1)="2";left(A2;2)&"-"&mid(A2;3;2)&"-"&mid(A2;5;5)&"-"&right(A2;1);"")&if(mid(A2;3;1)="3";left(A2;2)&"-"&mid(A2;3;2)&"-"&mid(A2;5;5)&"-"&right(A2;1);"")&if(mid(A2;3;1)="4";left(A2;2)&"-"&mid(A2;3;2)&"-"&mid(A2;5;5)&"-"&right(A2;1);"")&if(mid(A2;3;1)="5";left(A2;2)&"-"&mid(A2;3;3)&"-"&mid(A2;6;4)&"-"&right(A2;1);"")&if(mid(A2;3;1)="6";left(A2;2)&"-"&mid(A2;3;3)&"-"&mid(A2;6;4)&"-"&right(A2;1);"")&if(mid(A2;3;1)="7";left(A2;2)&"-"&mid(A2;3;4)&"-"&mid(A2;7;3)&"-"&right(A2;1);"")&if(mid(A2;3;1)="8";left(A2;2)&"-"&mid(A2;3;5)&"-"&mid(A2;8;2)&"-"&right(A2;1);"")&if(mid(A2;3;1)="9";left(A2;2)&"-"&mid(A2;3;6)&"-"&mid(A2;9;1)&"-"&right(A2;1);"")


    Maybe not the most beautifull formula in the world but it works. The formula formats a Swedish ISBN correctly (put the dashes in the right places). I have got help from XL-Dennis making this formula.


    :beergrin: Mats


    PS I have an Swedish version of XL I hope I have made the right translations to english.

    Hi!


    I got this code on the G-list from Bob Umlas. Thanks!


    Finally a way to get the little anoying paperclipp help "office assistant" to do some real work! Now you can be a real pain in ....too! :nana:


    Have a great weekend :beergrin: /Mats



    Sub PaperClipTalk()
    With Assistant
    .Visible = True
    With .NewBalloon
    .Heading = "THIS IS WHAT I WANT TO SAY"
    .Text = "AND THIS, TOO"
    .Show
    End With
    End With
    End Sub



    Bob Umlas
    KPMG Tax Technology
    Excel MVP

    Hi!


    Dennis gave me a tip of using the machintosh date system. Tools-alternative-calculations. It enables me to have negative time etc. I find it very usefull all though is a bit "dirty"....
    :flame:


    Thanks Dennis!:cheers:


    Mats

    Hi!


    I´m not a math expert, but shouldn´t this "31.35" be rounded up to "31.4" just like it is? I can recal something of "rounding up/down to even number" from school. That ought to be 31.4


    Kind regards Mats

    Hi!


    Thanks Dave. Nice solution. But why do I get a runtime error no 9 "index is out of reach" (I have a Swedish version so maybe translation is out of touch) at the line marked with an X. I just want to take what I have on the clipborad so I dont need the 'your copy code here section. Any help greatly appriciated!


    Sub DoIt()
    Dim lRows As Long


    '
    On Error Resume Next
    Sheets("CopyCheck").Delete
    On Error GoTo 0

    Sheets.Add().Name = "CopyCheck"

    With Sheets("CopyCheck")
    .Range("A1").PasteSpecial
    lRows = .UsedRange.Rows.Count
    End With
    Application.CutCopyMode = False
    X Sheets("PasteSheet").Rows("1:" & lRows).Insert
    'Paste
    ActiveSheet.Paste
    Application.DisplayAlerts = False
    Sheets("CopyCheck").Delete
    Application.DisplayAlerts = True
    End Sub


    Hello Dennis! Nice to see you to. Now can I make all kind of silly questions in English as well as Swedish to you. BTW I voted for Deep Purple yesterday I´m a bit of a traditionalist. I saw them a couple of years ago. Ian Gillan is still running around in his pyjamas on stage!


    :cheers::guitar::flower: