VBA help?

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
How do I create a VBA that will compare the list of numbers in EV70:EV79
for the 3 highest and the 3 lowest values in the column.
But in either case, if the highest numbers nor the lowest numbers cannot
exceed more than 5 numbers when values are equal.

If EV70, then add to EX71=0
If EV71, then add to EX71=1
If EV72, then add to EX71=2
If EV73, then add to EX71=3
If EV74, then add to EX71=4
If EV75, then add to EX71=5
If EV76, then add to EX71=6
If EV77, then add to EX71=7
If EV78, then add to EX71=8
If EV79, then add to EX71=9

Example

EU.....EV.................EX71.......EW71.......EX71
0...... 2
1...... 3................2865........................370
2...... 6
3...... 1
4...... 4
5...... 3
6...... 5
7...... 1
8...... 6
9...... 5
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
I'm afraid that your post doesn't make a whole lot of sense. From what I can tell, you want a code that will identify the 3 highest and 3 lowest numbers in a range of 10 cells. After that, I'm lost.
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
Yes, I want a codee that will identify the 3 highest and 3 lowest numbers in a range of 10 cells. The ET column
70-79, list the digits #0 through #9. The EU column,
70-79 list how many times each number has been entered
in the last 12 data row in my chart. So I want to know which
3 numbers have been entered the most and which 3 numbers have entered the least.
The results for the 3 highest numbers should be in EV71
The results for the 3 lowest numbers should be in EX71
The only other conditions is that some of the 0 through 9 digits
have the same amount of enteries in the last 12 data rows,
so in those cases I do not want EV71 to have more than 5 numbers and no less than 2, while EX71 has no less than 2 numbers and no more than 5 numbers.

So in otherwords, I am looking for the highest 2 to 5 digit occurrences and the lowest 2 to 5 digit occurrences in my chart over the last 12 DATA rows, within cells AF:AH. I do not
want the code to ever return and all 5 for the highest and the
remaining 5 for the lowest at the same time. Always leave at
least 2 digits out, which are neither the highest nor the lowest
occurrences.
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
Maybe an easier approach to this problem is to create a code
I have a list of 10 digits (0 through 9) in ET70:ET79.
I have listed in EU70:EU79 how many times each digit has
been entered in the last 12 data rows.

I want a code that pick from the ET column which 3 digits have the highest occurrences
in the EU column, and place answer in EV71.
Also,
I want a code that pick from the ET column which 3 digits have the lowest occurrences
in the EU column, and place answer in EX71.
Also,
I want a code that pick from the ET column which 4 digits are neither the highest nor
the lowest occurrences in the EU column, and place answer in EW71.

The problem is whenever this list is updated some of the 0 through 9 digits
have equal occurrences, so the key is to a keep a near balanced output from the 3 cells
(3 digits highest, 4 digits in the middle and 3 digit lowest). I need to prevent
the 3 cells EV71:EW71 from having less than 2 digits and no more than 5 digits
as a return answer in any of the cells. Also, if one has 2 digit answer returned,
then the other 2 cells can return either (the remaining 2 cells have 4 digit
answer) or (one cell has 3 digit answer and the other 5 digit answer).
Therefore, the valid amount of digit combination answers for all 10 digits returned in
the 3 cells in any order is the following:
2 digits-5 digits-3 digits (idea) example: EV71=03, EW71=96815, EX71=274
or
4 digits-2 digits-4 digits (idea) example: EV71=0396, EW71=15, EX71=2748
or
3 digits-4 digits-3 digits (idea) example: EV71=039, EW71=8156, EX71=274

Example
digits...Occurrences......Highest.....Middle......Lowest
EU.........EV...............EX71.......EW71........EX71
0.......... 2
1.......... 3................2865.......149...........370
2.......... 6
3.......... 1
4.......... 4
5.......... 3
6.......... 5
7.......... 1
8.......... 6
9.......... 5
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,087
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top