ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

3 Q & A posts tagged with LARGE

Tue
Apr 6
2010

Excel formula help. In a list of numbers, is there a formula that will tell me the top 3 numbers?

I have a variety of numbers in a vertical list. Is there a formula to input that will tell me the greatest 3 numbers in the list? And another to tell me the least 3 numbers?


Answer

Just like siti Vi said
use this to get the largest three numbers in cells B2, B3 and B4
=LARGE( A:A, 1)
=LARGE( A:A, 2)
=LARGE( A:A, 3)

But adding the second request

Paste this formula in C2 to get smallest one
=SMALL( A:A, 1)
And this in C3 to get 2nd smallest number
=SMALL( A:A,2)
and this to get 3rd smallest
=SMALL( A:A, 3)

VBAXLMan

Tuesday 4/6/2010 9:09:48 AM
Wed
Dec 20
2006

Matching with Ms Excel?

I have a spreadsheet which contains 5-6 columns of info, with about 200-300 rows. Column D contains totals. Some of the totals in column D will match with one another, just with a minus sign. Say if there is a 54300, then there is another -54300, just its with a minus sign (this is actually a ballance of a clearing account).
What I want is to write a formula or macro that would take column D value from the first row, search through all column D, find matching value, cut those two rows and paste in another sheet within the same file. Then repeat this with all the rows, cutting rows with matching value in column D, so that all the matching values are pasted one below another in another sheet. This way the original sheet will only contain unmatched items.
I work as an accountant so have to go through account statements like that very frequently. Having a formula or a macro like that would make my life a lot easier :)

Thanks for any responses!

Happy Xmas!


Answer

You can solve it in one of these ways
1- Formula
2- Macro
I can post here the formula solution, because it is easier to understand.
Now, leave column E as seperator, go to F2 and paste this
=ABS( D2)
and fill it down to all the other cells. In G2, paste this
=LARGE( F:F, ROW()-1)
And fill the G2 down to all the cells below
Hay, it is your exact list, but sorted in Descending order

Yes
That is because VBAXLMan show you how to do it

You can cut and paste this G column cells to what ever you want

Enjoy it and..

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 5:36:13 PM
Sun
Dec 3
2006

How to find the largest non-empty row of an Excel column with VBA code?


Answer

I guess you mean the largest value in a range of cells, right?

If so, then you can do one of the following:
1- Use another cell with the function of
=Max( B:B)
then call that cell using the object [Range]
MaximumValue = Range("A1").Value

2- Use the powerfull feature WorksheetFunction that will enables you to use some of Excel built-in functions in VBA, example:
MaximumValue = WorksheetFunction.Max( "Sheet1$B:$B")

Or...
You may want to find which row have the largest no of non-empty cells.
If so, then you will need som fomrula combination, I will need you to mail me here if you are looking for that in particular.
I also will need you to answer some questions like (Do these rows are one after one? means they don't have empty rows in between?)



mail me here for any further info

Enjoy my profile, I am the VBAXLMan

Sunday 12/3/2006 3:19:22 AM

Tags

History