find largest data
 

Hi,

I have data in column:

Kolumn1 Kolumn2

name1 5,5
name2 6,3
name1 7,9
name1 8,4
name1 3,2
name1 9,9
name2 1,2
name2 2,2
name3....etc.(a few thousand records)

How make choice in VBA data in Kolumn1?
Result operation:
(in another sheet)
Kolumn1 Kolumn2
name1 .... (sum = 2 largest data of name1 in kolumn2)
name2 .... (sum = 2 largest data of name2 in kolumn2)
name3 .... (sum = 2 largest data of name3 in kolumn2)
etc....

Thanks for any help

Regards
Mark

find largest data
 

=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!$B$1:$B $200),{1,2}))

Entered in B1 on the second sheet using Ctrl+Shift+Enter Rather than just
enter. (since this is an array formula) Then drag fill down the column

Adjust the reference to rows 1 to 200 to match your data.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi,

I have data in column:

Kolumn1 Kolumn2

name1 5,5
name2 6,3
name1 7,9
name1 8,4
name1 3,2
name1 9,9
name2 1,2
name2 2,2
name3....etc.(a few thousand records)

How make choice in VBA data in Kolumn1?
Result operation:
(in another sheet)
Kolumn1 Kolumn2
name1 .... (sum = 2 largest data of name1 in kolumn2)
name2 .... (sum = 2 largest data of name2 in kolumn2)
name3 .... (sum = 2 largest data of name3 in kolumn2)
etc....

Thanks for any help

Regards
Mark

find largest data
 

Tom,
I can't use your formula.
Function return only secend largest data (i want sum first
and secend)
I'd like in result operation haven't repeat the same name.
I have excel 2k.
Can do it in VBA?

Many thanks in anticipation!
Best regards
mark


-----Original Message-----
=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!$B$1:$ B$200),
{1,2}))

Entered in B1 on the second sheet using Ctrl+Shift+Enter
Rather than just
enter. (since this is an array formula) Then drag fill
down the column

Adjust the reference to rows 1 to 200 to match your data.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi,

I have data in column:

Kolumn1 Kolumn2

name1 5,5
name2 6,3
name1 7,9
name1 8,4
name1 3,2
name1 9,9
name2 1,2
name2 2,2
name3....etc.(a few thousand records)

How make choice in VBA data in Kolumn1?
Result operation:
(in another sheet)
Kolumn1 Kolumn2
name1 .... (sum = 2 largest data of name1 in
kolumn2)
name2 .... (sum = 2 largest data of name2 in
kolumn2)
name3 .... (sum = 2 largest data of name3 in
kolumn2)
etc....

Thanks for any help

Regards
Mark








.

find largest data
 

Ctrl+Shift+Enter doesn't work.
I check it on another PC - work.
Where is set-up Ctrl+Shift+Enter in Excel?


-----Original Message-----
=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!$B$1:$ B$200),
{1,2}))

Entered in B1 on the second sheet using Ctrl+Shift+Enter
Rather than just
enter. (since this is an array formula) Then drag fill
down the column

Adjust the reference to rows 1 to 200 to match your data.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi,

I have data in column:

Kolumn1 Kolumn2

name1 5,5
name2 6,3
name1 7,9
name1 8,4
name1 3,2
name1 9,9
name2 1,2
name2 2,2
name3....etc.(a few thousand records)

How make choice in VBA data in Kolumn1?
Result operation:
(in another sheet)
Kolumn1 Kolumn2
name1 .... (sum = 2 largest data of name1 in
kolumn2)
name2 .... (sum = 2 largest data of name2 in
kolumn2)
name3 .... (sum = 2 largest data of name3 in
kolumn2)
etc....

Thanks for any help

Regards
Mark








.

find largest data
 

If you enter it as I instructed, it will do what you describe. It is
usually not advisable to use VBA if a worksheet function can do the job.

If you want code:

Sub GetTop2()
Dim rng As Range
Dim cell as Range
Dim dblTot as Double
With Worksheets("Sheet2")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
dblTot = Evaluate("SUM(LARGE(IF(Sheet1!$A$1:$A$200=" _
& Chr(34) & cell.Value & Chr(34) & _
",Sheet1!$B$1:$B$200),{1,2}))")
cell.Offset(0, 1).Value = dblTot
Next


End Sub

--
Regards,
Tom Ogilvy


"Mark" wrote in message
...
Tom,
I can't use your formula.
Function return only secend largest data (i want sum first
and secend)
I'd like in result operation haven't repeat the same name.
I have excel 2k.
Can do it in VBA?

Many thanks in anticipation!
Best regards
mark


-----Original Message-----
=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!$B$1:$ B$200),
{1,2}))

Entered in B1 on the second sheet using Ctrl+Shift+Enter
Rather than just
enter. (since this is an array formula) Then drag fill
down the column

Adjust the reference to rows 1 to 200 to match your data.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi,

I have data in column:

Kolumn1 Kolumn2

name1 5,5
name2 6,3
name1 7,9
name1 8,4
name1 3,2
name1 9,9
name2 1,2
name2 2,2
name3....etc.(a few thousand records)

How make choice in VBA data in Kolumn1?
Result operation:
(in another sheet)
Kolumn1 Kolumn2
name1 .... (sum = 2 largest data of name1 in
kolumn2)
name2 .... (sum = 2 largest data of name2 in
kolumn2)
name3 .... (sum = 2 largest data of name3 in
kolumn2)
etc....

Thanks for any help

Regards
Mark








.

find largest data
 

Ctrl+Shift+Enter works fine. I tested the formula again and it works just
fine.

By the way, my code assumes on sheet2 you have a list of the unique names in
column A starting in A1.



--
Regards,
Tom Ogilvy


"Mark" wrote in message
...
Ctrl+Shift+Enter doesn't work.
I check it on another PC - work.
Where is set-up Ctrl+Shift+Enter in Excel?


-----Original Message-----
=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!$B$1:$ B$200),
{1,2}))

Entered in B1 on the second sheet using Ctrl+Shift+Enter
Rather than just
enter. (since this is an array formula) Then drag fill
down the column

Adjust the reference to rows 1 to 200 to match your data.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi,

I have data in column:

Kolumn1 Kolumn2

name1 5,5
name2 6,3
name1 7,9
name1 8,4
name1 3,2
name1 9,9
name2 1,2
name2 2,2
name3....etc.(a few thousand records)

How make choice in VBA data in Kolumn1?
Result operation:
(in another sheet)
Kolumn1 Kolumn2
name1 .... (sum = 2 largest data of name1 in
kolumn2)
name2 .... (sum = 2 largest data of name2 in
kolumn2)
name3 .... (sum = 2 largest data of name3 in
kolumn2)
etc....

Thanks for any help

Regards
Mark








.

find largest data
 

Tom!
It's work excellant in VBA! Thanks a lot!!!
I wonder why I can't use function in the form:
=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!$B$1:$B $200),
{1,2}))
My excel don't accept comma(,)only semicolon (;).
When I change comma for semicolon and use array formula
function result is only second largest name.
Help, please.
Regards
Mark


-----Original Message-----
If you enter it as I instructed, it will do what you
describe. It is
usually not advisable to use VBA if a worksheet function
can do the job.

If you want code:

Sub GetTop2()
Dim rng As Range
Dim cell as Range
Dim dblTot as Double
With Worksheets("Sheet2")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
dblTot = Evaluate("SUM(LARGE(IF(Sheet1!$A$1:$A$200=" _
& Chr(34) & cell.Value & Chr(34) & _
",Sheet1!$B$1:$B$200),{1,2}))")
cell.Offset(0, 1).Value = dblTot
Next


End Sub

--
Regards,
Tom Ogilvy


"Mark" wrote in message
...
Tom,
I can't use your formula.
Function return only secend largest data (i want sum
first
and secend)
I'd like in result operation haven't repeat the same
name.
I have excel 2k.
Can do it in VBA?

Many thanks in anticipation!
Best regards
mark


-----Original Message-----
=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!
$B$1:$B$200),
{1,2}))

Entered in B1 on the second sheet using
Ctrl+Shift+Enter
Rather than just
enter. (since this is an array formula) Then drag fill
down the column

Adjust the reference to rows 1 to 200 to match your
data.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi,

I have data in column:

Kolumn1 Kolumn2

name1 5,5
name2 6,3
name1 7,9
name1 8,4
name1 3,2
name1 9,9
name2 1,2
name2 2,2
name3....etc.(a few thousand records)

How make choice in VBA data in Kolumn1?
Result operation:
(in another sheet)
Kolumn1 Kolumn2
name1 .... (sum = 2 largest data of name1 in
kolumn2)
name2 .... (sum = 2 largest data of name2 in
kolumn2)
name3 .... (sum = 2 largest data of name3 in
kolumn2)
etc....

Thanks for any help

Regards
Mark








.



.

find largest data
 

Try like this...
the row is a trick to force the array issue...

=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1;Sheet1!$B$1:$B $200);ROW(INDIRECT
("1:2"))))

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Mark" wrote:

=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!$B$1:$B $200),
{1,2}))

find largest data
 

I know why I can't used Ctrl+Shift+Enter.
After open Excel i had hotkeys Ctrl+Shift+Enter in another
application.
I managed with it when i disconnect hotkeys before open
Excel.

Best regards
Mark


-----Original Message-----
Ctrl+Shift+Enter works fine. I tested the formula again
and it works just
fine.

By the way, my code assumes on sheet2 you have a list of
the unique names in
column A starting in A1.



--
Regards,
Tom Ogilvy


"Mark" wrote in message
...
Ctrl+Shift+Enter doesn't work.
I check it on another PC - work.
Where is set-up Ctrl+Shift+Enter in Excel?


-----Original Message-----
=SUM(LARGE(IF(Sheet1!$A$1:$A$200=A1,Sheet1!
$B$1:$B$200),
{1,2}))

Entered in B1 on the second sheet using
Ctrl+Shift+Enter
Rather than just
enter. (since this is an array formula) Then drag fill
down the column

Adjust the reference to rows 1 to 200 to match your
data.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi,

I have data in column:

Kolumn1 Kolumn2

name1 5,5
name2 6,3
name1 7,9
name1 8,4
name1 3,2
name1 9,9
name2 1,2
name2 2,2
name3....etc.(a few thousand records)

How make choice in VBA data in Kolumn1?
Result operation:
(in another sheet)
Kolumn1 Kolumn2
name1 .... (sum = 2 largest data of name1 in
kolumn2)
name2 .... (sum = 2 largest data of name2 in
kolumn2)
name3 .... (sum = 2 largest data of name3 in
kolumn2)
etc....

Thanks for any help

Regards
Mark








.



.