Code to find copy and paste highest and lowest values.

[asyamonique]

Good Day,
I have a data list which show the results from column A to column AD all the way down.
Column B is the customer ID numbers.
Column C is the customer Name.
Column D is the customer Surname.
Column G is the customer Nationality.
Column I is the customer points which from negative to positive numbers.


Is it possible to create a code which will find the top 10 lowest and highest values from column I and will paste the related datas from column BH7 all the way down like below?

BH7: ID
BI7: NAME
BJ7: SURNAME
BK7: NAT.
BL7: POINTS

Many thanks

 

[footoo]

If it is OK to sort the data, a macro could be created from the macro recorder :
• Sort by column I ascending
• Select first 10 rows of data in B:I and copy
• Select BH7 and paste
• Sort by column I descending
• Select first 10 rows of data in B:I and copy
• Select BH17 and paste

 

[Peter_SSs]

Is it possible to create a code which will find the top 10 lowest and highest values from column I and will paste the related datas from column BH7 all the way down like below?

BH7: ID
BI7: NAME
BJ7: SURNAME
BK7: NAT.
BL7: POINTS

1. The underlined part is not clear to me.
Do you mean, say, top 10 highest in rows 7 to 16 and the lowest 10 in, say, rows 20 to 29?
Please clarify.

2. What should happen if several rows have the equal 10th highest (or lowest) points?

3. Are you specifically looking for a macro to copy/paste the values, or might a formula solution be acceptable?

 

[asyamonique]

I will try.
Thank you

 

[asyamonique]

Hello Peter,
It can be like that...
BH7 to BH16 top 10 highest
BH17 to BH26 top 10 lowest

If several similar highest or lowest can be in the list its not problem.
I'm looking for Macro since I have the formulas already.

Many Thanks

 

[Peter_SSs]

It can be like that...
BH7 to BH16 top 10 highest
BH17 to BH26 top 10 lowest

If several similar highest or lowest can be in the list its not problem.

.. but if there were, say, 5 equal 10th highest then there would be 14 rows required for the 'highest' list & so it would not fit in the range BH7 to BH16 and then also the 'lowest' list could not start in BH17. Please clarify exactly what you would want.

... since I have the formulas already.

In that case, one fairly simple approach would be to have the macro enter the formulas & then just "fix" the formula results (unless you are wanting formatting etc copied too?). So what are the formulas that you have for this task?

 

[asyamonique]

Hi Peter,
I have the below formulas, so far it works well.
I think using the formulas would be much better.
Thanks again.

BH7 :

=IF($BN7="","",INDEX(B:B,SMALL(IF($I$3:$I$999=$BN7,ROW($I$3:$I$999)),COUNTIF(BJ$7:BJ7,BJ7))))

BI1 :

=IF($BN7="","",INDEX(C:C,SMALL(IF($I$3:$I$999=$BN7,ROW($I$3:$I$999)),COUNTIF(BN$7:BN7,BN7))))

BJ7 :

=IF($BN7="","",INDEX(D:D,SMALL(IF($I$3:$I$999=$BN7,ROW($I$3:$I$999)),COUNTIF(BN$7:BN7,BN7))))

BN7:

=IF(ROWS(BN$17:BN17)<=$BZ$1,LARGE($I$3:$I$999,ROWS(BN$17:BN17)),"")

BY1:

=COUNTIFS(I3:I999,"<0")

BZ1:

=COUNTIFS(I3:I999,">0")