I need to select 20 individuals with the highest balances in a workbook. My
problem is that the individuals are identified by an id number and each id
number can occur several times depending on the type of account an
individual holds. So if a person has 3 accounts, the total balance is what I
need to consider when sorting the column with id.

for example
col A has id, b has balance I need to find 20 unique A whose balances are
the highest. I need a macro to do this but as I am not very familiar with
vba, I am at a loss. Column A has about 3000 rows.

Any help would be appreciated. Thank you.


--
Jayen

add a third column, c, and use this to sum the values for any id

so say in C8 you'd have
=SUMIF(ID_List,A8,Values_List)
where ID_LIST are the cells in column A, Value_List the cells in column B

then sort by C then B





"Jayen" wrote:

I need to select 20 individuals with the highest balances in a workbook. My
problem is that the individuals are identified by an id number and each id
number can occur several times depending on the type of account an
individual holds. So if a person has 3 accounts, the total balance is what I
need to consider when sorting the column with id.

for example
col A has id, b has balance I need to find 20 unique A whose balances are
the highest. I need a macro to do this but as I am not very familiar with
vba, I am at a loss. Column A has about 3000 rows.

Any help would be appreciated. Thank you.


--
Jayen

Thanks Patrick, but this is a workbook that has 3000 records so far and will
grow to about 25000. I would like to do this using a macro instead of
formulas in a new column. It is a shared workbook and so i am not at liberty
to add more columns.
--
Jayen


"Patrick Molloy" wrote:

add a third column, c, and use this to sum the values for any id

so say in C8 you'd have
=SUMIF(ID_List,A8,Values_List)
where ID_LIST are the cells in column A, Value_List the cells in column B

then sort by C then B





"Jayen" wrote:

I need to select 20 individuals with the highest balances in a workbook. My
problem is that the individuals are identified by an id number and each id
number can occur several times depending on the type of account an
individual holds. So if a person has 3 accounts, the total balance is what I
need to consider when sorting the column with id.

for example
col A has id, b has balance I need to find 20 unique A whose balances are
the highest. I need a macro to do this but as I am not very familiar with
vba, I am at a loss. Column A has about 3000 rows.

Any help would be appreciated. Thank you.


--
Jayen

but you can do it in code. once sorted, you just delete it

"Jayen" wrote:

Thanks Patrick, but this is a workbook that has 3000 records so far and will
grow to about 25000. I would like to do this using a macro instead of
formulas in a new column. It is a shared workbook and so i am not at liberty
to add more columns.
--
Jayen


"Patrick Molloy" wrote:

add a third column, c, and use this to sum the values for any id

so say in C8 you'd have
=SUMIF(ID_List,A8,Values_List)
where ID_LIST are the cells in column A, Value_List the cells in column B

then sort by C then B





"Jayen" wrote:

I need to select 20 individuals with the highest balances in a workbook. My
problem is that the individuals are identified by an id number and each id
number can occur several times depending on the type of account an
individual holds. So if a person has 3 accounts, the total balance is what I
need to consider when sorting the column with id.

for example
col A has id, b has balance I need to find 20 unique A whose balances are
the highest. I need a macro to do this but as I am not very familiar with
vba, I am at a loss. Column A has about 3000 rows.

Any help would be appreciated. Thank you.


--
Jayen

am giving it a try. my vba is not that strong but if i run into problems I'll
let you know. thanks.
--
Jayen


"Patrick Molloy" wrote:

but you can do it in code. once sorted, you just delete it

"Jayen" wrote:

Thanks Patrick, but this is a workbook that has 3000 records so far and will
grow to about 25000. I would like to do this using a macro instead of
formulas in a new column. It is a shared workbook and so i am not at liberty
to add more columns.
--
Jayen


"Patrick Molloy" wrote:

add a third column, c, and use this to sum the values for any id

so say in C8 you'd have
=SUMIF(ID_List,A8,Values_List)
where ID_LIST are the cells in column A, Value_List the cells in column B

then sort by C then B





"Jayen" wrote:

I need to select 20 individuals with the highest balances in a workbook. My
problem is that the individuals are identified by an id number and each id
number can occur several times depending on the type of account an
individual holds. So if a person has 3 accounts, the total balance is what I
need to consider when sorting the column with id.

for example
col A has id, b has balance I need to find 20 unique A whose balances are
the highest. I need a macro to do this but as I am not very familiar with
vba, I am at a loss. Column A has about 3000 rows.

Any help would be appreciated. Thank you.


--
Jayen

here's a simple demo for you


Option Explicit
Sub StartDemo()
ActiveSheet.Cells.Delete
Range("A1:B1") = Array("ID", "Value")
CreateRandomData
SortData
End Sub
Sub CreateRandomData()
Dim index As Long
For index = 2 To 801
Cells(index, 1) = Chr(65 + Int(Rnd * 26))
Cells(index, 2) = Round(Rnd * 50, 2)
Next
End Sub
Sub SortData()
With Range("C1:C801")
.Formula = "=SUMIF(R1C1:R800C1,RC1,R1C2:R800C2)"
End With
With Range("A1:C801")
.Sort Range("C1"), xlAscending, Range("B1"), , xlDescending
End With
Range("C1:C801").Clear
End Sub

"Jayen" wrote:

am giving it a try. my vba is not that strong but if i run into problems I'll
let you know. thanks.
--
Jayen


"Patrick Molloy" wrote:

but you can do it in code. once sorted, you just delete it

"Jayen" wrote:

Thanks Patrick, but this is a workbook that has 3000 records so far and will
grow to about 25000. I would like to do this using a macro instead of
formulas in a new column. It is a shared workbook and so i am not at liberty
to add more columns.
--
Jayen


"Patrick Molloy" wrote:

add a third column, c, and use this to sum the values for any id

so say in C8 you'd have
=SUMIF(ID_List,A8,Values_List)
where ID_LIST are the cells in column A, Value_List the cells in column B

then sort by C then B





"Jayen" wrote:

I need to select 20 individuals with the highest balances in a workbook. My
problem is that the individuals are identified by an id number and each id
number can occur several times depending on the type of account an
individual holds. So if a person has 3 accounts, the total balance is what I
need to consider when sorting the column with id.

for example
col A has id, b has balance I need to find 20 unique A whose balances are
the highest. I need a macro to do this but as I am not very familiar with
vba, I am at a loss. Column A has about 3000 rows.

Any help would be appreciated. Thank you.


--
Jayen