Excel help

Excel help

Postby PadawanLotto » Mon Jan 11, 2010 3:55 am

Is it possible to divide 2 numbers and round the result in the same cell?

Never mind, I found the solution at MrExcel.com.

Thanks anyway.
PadawanLotto
 
Posts: 1584
Joined: Sat Mar 25, 2006 10:33 pm
Location: Dayton, OH

Re: Excel help

Postby PadawanLotto » Sun Jul 11, 2010 2:21 pm

Ok, new question and Mr, Excel was no help. Can you sort numbers in a row of cells? Example: 48391 each number in it's own cell sorted numerically 13489.
I tried the Sort left to right by row on values order smallest to largest and it sorts one row but not the other rows.

Thanks
Padawan
PadawanLotto
 
Posts: 1584
Joined: Sat Mar 25, 2006 10:33 pm
Location: Dayton, OH

Re: Excel help

Postby Bobijohn » Mon Jul 12, 2010 4:07 am

Hi PadwanLotto

You may find the attached text file helpful. I copied this to myself several years ago and used it extensively in Excel 2003.

Pay particular attention to to the last main paragraph to do multiple rows using the "small" function.

Ofcourse, credit to the original poster.

Cheers
You do not have the required permissions to view the files attached to this post.
Bobijohn
 
Posts: 212
Joined: Tue Feb 10, 2009 3:27 pm

Re: Excel help

Postby PadawanLotto » Mon Jul 12, 2010 4:30 am

Thanks Bobijohn, I'll give it shot.
PadawanLotto
 
Posts: 1584
Joined: Sat Mar 25, 2006 10:33 pm
Location: Dayton, OH

Re: Excel help

Postby Mallard » Mon Jul 12, 2010 12:20 pm

Are you trying sort each row individually from left to right?

If so, you would need a macro to do it or do each row manually one at a time.

Hi Padawanlotto
Are you trying sort each row individually from left to right?
If so, you would need a macro to do it or do each row manually one at a time.


this worked for me:
Just create your own Macro as follows .............

Sub sortData()
Dim r As Range, cell As Range, r1 As Range
Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cell In r
Set r1 = cell.Resize(1, 6)
r1.Sort Key1:=r1, _
Order1:=xlAscending, _
Orientation:=xlSortRows
Next
End Sub

this assumes your data starts in A1.
Mallard
 
Posts: 18
Joined: Sun Mar 23, 2008 2:21 pm

Re: Excel help

Postby PadawanLotto » Mon Jul 12, 2010 12:31 pm

Thanks Mallard, I'll give that a shot also. I haven't tried the first suggestion yet, I sort of did a work around where I didn't need to sort the numbers in order any more. I'll put this in my book of Excel notes though, funny thing is I haven't worked with excel for awhile and forgot how to do a lot things. In my younger days my math instructor wanted me to take algebra, he said I'd be good at it, I asked what was it good for what can it be applied to? He had no idea so, I didn't take any classes. Now Excel learning algebra on the fly. :D

Thanks again

Padawan
PadawanLotto
 
Posts: 1584
Joined: Sat Mar 25, 2006 10:33 pm
Location: Dayton, OH

Re: Excel help

Postby PadawanLotto » Tue Jul 13, 2010 11:12 am

Mallard wrote:Are you trying sort each row individually from left to right?

If so, you would need a macro to do it or do each row manually one at a time.

Hi Padawanlotto
Are you trying sort each row individually from left to right?
If so, you would need a macro to do it or do each row manually one at a time.


this worked for me:
Just create your own Macro as follows .............

Sub sortData()
Dim r As Range, cell As Range, r1 As Range
Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cell In r
Set r1 = cell.Resize(1, 6)
r1.Sort Key1:=r1, _
Order1:=xlAscending, _
Orientation:=xlSortRows
Next
End Sub

this assumes your data starts in A1.

Nice, worked great, no mus no fus. Thanks Mallard
PadawanLotto
 
Posts: 1584
Joined: Sat Mar 25, 2006 10:33 pm
Location: Dayton, OH

Re: Excel help

Postby PadawanLotto » Tue Jul 13, 2010 8:02 pm

Bobijohn wrote:Hi PadwanLotto

You may find the attached text file helpful. I copied this to myself several years ago and used it extensively in Excel 2003.

Pay particular attention to to the last main paragraph to do multiple rows using the "small" function.

Ofcourse, credit to the original poster.

Cheers

Thanks Bobijohn, I like this better than the macro because of the speed and I think it would be a lot safer to use than a macro.
PadawanLotto
 
Posts: 1584
Joined: Sat Mar 25, 2006 10:33 pm
Location: Dayton, OH

Re: Excel help

Postby Bobijohn » Tue Jul 13, 2010 11:08 pm

Hi Padawanlotto

Yep - it is pretty slick once you get the hang of it. Although, if you are building a permanent spreadsheet it is probably better to use the macro approach and build it in.

Anyway, glad to be of help and, BTW, thanks for all your excellent posts and contribution to this forum over the years.

Bobijohn
Bobijohn
 
Posts: 212
Joined: Tue Feb 10, 2009 3:27 pm

Re: Excel help

Postby PadawanLotto » Mon Aug 02, 2010 10:49 pm

New challenge, is it possible to find and count the duplicates in a list of numbers?
I have a list of 376,992 5 digit numbers that contain duplicates in sets of 6, 21, and 56. I want to find those sets without doing it manually. I have already found the unique numbers, there are 91,713.

Thanks
Padawan
PadawanLotto
 
Posts: 1584
Joined: Sat Mar 25, 2006 10:33 pm
Location: Dayton, OH


Return to Anything Else

Who is online

Users browsing this forum: No registered users and 2 guests