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
.
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!
Get http://www.asap-utilities.com it´s a great add-in that has the function you want.
I cant live without this addin I feel. :flower:
Keep on XLing! 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!
Very interesting, are they true? Cross your heart or swear on the good book!:flower:
BTW what happend to the fourth king?.
Thanks / Mats :cheers:
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
Thanks Dave and Dennis!
Now it works perfect!
I am constantly amazed of the creativity and knowledge you have and share with the rest of us. I am (as always) thankfull. Remember lunch and/or coffee is on me if you come to the lovely town of Halmstad Sweden!
Kidn regards Mats:cheers:
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:
Hi!
Anyone know of a way to insert rows (as much as is needed from data that comes from the clipboard) above data and then insert data from clipboard. The amount of new rows needed is different from one paste to another.
/Mats
