# Automating Tables

#### Brew

##### Well-known Member
I would like to program 3 tables within excel that automatically updates the following data:

in U column
how many 3s, 2s 1s, and 0s in the last 10 entries and the percentages for each.
Also, how many for the last 100 entries and their percentages.
I have a 2nd column (column EM) that produces 0s, 1s, 2s and 3s. I need the last 10 entries and the percentages for each.
Also, how many for the last 100 entries and their percentages.
I would like to do the same for the data in a 3rd column (AV column) which has only 1s, 2s and 3s.

Some cells in the columns are blank, so ingnore those as non-entries.
PC Pick3 System Chart3.xls
ECEDEEEFEGEHEIEJEKEL
21E/O(ColumnU)D/S(ColumnAV)
223210123
23Last10Last10
24%%
25Last100Last100
26%%
27
28
29H/Low(ColumnEM)
303210
31Last10
32%
33Last100
34%
combo
PC Pick3 System Chart3.xls
EMENEOEP
51
52
532
54
552
56
571
58
590
60
611
62
631
64
combo

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Brew

##### Well-known Member
In otherwords,
I want the problem the total of #3, #2, #1 and #0 in the U column since the last
10 and 100 numerical entries in the column.
example:
last 10: ED23=2, EE23=5, Ef23=3, EG23=0
% ED24=20%, EE24=50%, Ef24%=30, EG24=0%
last 100: ED25=19, EE25=30, Ef25=30, EG25=21
% ED26=19%, EE26=30%, Ef26%=30, EG26=21%

Maybe, a couple of formulas will work, that would look at the entire column
for the last 10 or 100 numerical entries and return how many are 3, 2, 1 or 0 numbers
are in both categories (10 and 100 entries).

#### Brew

##### Well-known Member
I want the problem the total the number of #3, #2, #1 and #0 in the U column since the last 10 and 100 numerical entries in the column.
example:
...................3................2................1...............0
last 10:.....ED23=2,......EE23=4,......Ef23=3,........EG23=1
%..........ED24=20%,.EE24=40%,..Ef24%=30,....EG24=10%
last 100:...ED25=19,.....EE25=30,....Ef25=30,......EG25=21
%..........ED26=19%,..EE26=30%,..Ef26%=30,...EG26=21%

Example of last 10 numerical entries, relating to the above example
Column U
U13= blank
U14= blank
U15= 3
U16= blank
u17= 2
u18= blank
u19= 2
u20= blank
u21= 1
u22= blank
u23= 3
u24= blank
u25= 1
u26= blank
u27= blank
u28= 1
u29= blank
u30= 0
u31= blank
u32= 2
u33= blank
u34= 2

Maybe, a couple of formulas will work, that would look at the entire column
for the last 10 or 100 numerical entries and return how many are 3, 2, 1 or 0 numbers are in both categories (10 and 100 numerical entries).

#### Yogi Anand

##### MrExcel MVP
Hi Brew:

In the following illustration I have first used Advanced Filter to first filter the source data for the last 10 numeric entries and then counted the number 0f 3's, 2's, 1's and 0's ...
Book2
BCDEFGHIJ
1CountLast
210FALSEFALSE
3numericentries
4
5entryentryNumberOf3210
6blank34312
7text1
823
932source
100criteria
1112coppyto
1233
13text0
1413
15text2
163
172
180
192
203
210
223
232
Sheet3

I hope this helps!

#### Brew

##### Well-known Member

Yes this the results, I am looking for place upon the sample data you have illustrated, but it's a little cumbersome, and still has to be manually updated will I add a new row.
I want the table to update every time I add a new row or two.
I am only adding one row of data at a time. Also some of the cells in the column are blank or empty and I will probably have to look at the last 25 rows or less to get the last 10 numerical entries and 220 rows to get the last 100 numerical entries, then count how many of those are the number 3, 2, 1, and 0 exist.

So if I have 150 rows, I may have to go back 22 to 25 rows back to get the last 10 numerical entries to calculate, which would possibly be 125 to 150, then calculate. Next, I add 2 rows with one row of data and the other empty, My table should update by calculating going back to rows 129 to 152 to get the nesxt last 10 numerical entries.

I want the problem the total the number of #3, #2, #1 and #0 in the U column
since the last 10 and 100 numerical entries in the column.
Below is an example of the table I want to automatically update after every numerical entry:
....................3.................2...............1..................0
last 10:.....ED23=2,;......EE23=4,......Ef23=3,...........EG23=1
%..........ED24=20%,. EE24=40%,.. Ef24%=30,....EG24=10%
last 100:... ED25=19,.....EE25=30,.... Ef25=30,....... .EG25=21
%..........ED26=19%,.. EE26=30%,.. Ef26%=30,... EG26=21%

Example of last the 10 numerical entries, relating to the above example
Column U
U13= blank
U14= blank
U15= 3
U16= blank
u17= 2
u18= blank
u19= 2
u20= blank
u21= 1
u22= blank
u23= 3
u24= blank
u25= 1
u26= blank
u27= blank
u28= 1
u29= blank
u30= 0
u31= blank
u32= 2
u33= blank
u34= 2

Maybe, a couple of formulas or function will work, that would look at the entire column
for the last 10 or 100 numerical entries and return how many are 3, 2, 1 or 0
numbers are in both categories (10 and 100 numerical entries). Then give the percentages.

#### Yogi Anand

##### MrExcel MVP
Hi Brew:

I would suggest that you first try to incorporate the formulations I suggested into your actual ranges, and the number of numeric entries to be counted.

Once everything is working the way you intend it to work, then you can look into automating the process.

#### Brew

##### Well-known Member

Thanks Yogi, I've incorporated your formulations to my actual ranges and the numeric enteries to be counted. It works as you illustrated. I see how to repeat it for all of my remaining table calculations.
I have 2 remaining questions:
I did not add your legend (source, criteria and coppyto with its colors). Is it critical to calculations or a legend to use as a reminder.
Also, now that everything is working the way I intend it to work, how do I automate the process, so that the update update everytime a row or two is added.

#### Brew

##### Well-known Member
Now that I have Yogi's formulation setup. I need a function probably that
will automatically update the coppyto data list and the number of 3, 2, 1, and 0 table,
whenever an additional numeric entry is added to another row in the entry column cells.
This is the last 10 numeric and last 100 numeric entries.

...................3................2................1...............0
last 10:.....ED23=2,...... EE23=4,......Ef23=3,........EG23=1
%..........ED24=20%,. EE24=40%,..Ef24%=30,....EG24=10%
last 100:...ED25=19,.....EE25=30,.... Ef25=30,......EG25=21
%..........ED26=19%,..EE26=30%,..Ef26%=30,... EG26=21%

#### Yogi Anand

##### MrExcel MVP
Brew said:
Thanks Yogi, I've incorporated your formulations to my actual ranges and the numeric enteries to be counted. It works as you illustrated. I see how to repeat it for all of my remaining table calculations.
I have 2 remaining questions:

I did not add your legend (source, criteria and coppyto with its colors). Is it critical to calculations or a legend to use as a reminder.
This is only legend -- and as you have stated to be used as a reminder

Also, now that everything is working the way I intend it to work, how do I automate the process, so that the update update everytime a row or two is added.
To automate it we have to ...

1. create and use dynamic range(s) instead of static range(s)
and
2. create a Worksheet_Change event that will automatically execute the AdvancedFilter when you add a numeric entry and then do the affiliated calculations

I hope this helps. And if you want to discuss this futher -- please post back and then let us take it from there.

#### Brew

##### Well-known Member
Thanks Yogi for the update.
Yes, I need further help to develop these 2 remaining steps you suggested. Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,943
Messages
5,856,438
Members
431,814
Latest member
qualitypavingstone ### 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