# VBA help?

#### Brew

##### Well-known Member
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### phantom1975

##### MrExcel MVP
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
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
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
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

Replies
1
Views
184
Replies
3
Views
197
Replies
5
Views
266
Replies
1
Views
190
Replies
7
Views
256

1,171,801
Messages
5,877,603
Members
433,270
Latest member
Relight

### 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.

### Which adblocker are you using?

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

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