I Need an Array Formula?

[roboticskid]

Hey Everyone!

I am new to VBA in general but am really excited to use it to make the most out of Excel spreadsheets. Currently I am working on a Scouting Sheet for my robotics team and ran into an issue that is far beyond my understanding of knowledge.

(I attached the Excel file below in case since I am not sure what you might need- Using Excel 2013 to edit)

So far I have created a User Form that deposits information into a large table on the Worksheet "Data"
I am now working on analyzing that data on a separate Worksheet "Autonomous Analysis" The idea is to get count the occurrences of each team starting at a particular obstacle to tally in the chart labelled "Starting Position Tendencies"

For Example:
Right now in the table in the "Data" Worksheet, Team Number 558 starts at the Low Bar. So in the "Autonomous Analysis" Worksheet there should be a one in the table called "Starting Position Tendencies" where 558 and Low Bar intersect. This starts to get complicated because I need the VBA code to cycle through the table in "Data" Worksheet looking for a particular team (say Team 195) and then find each occurrence of that Team number and then tally whatever value occuring in the Starting Position column into the table on Worksheet "Table Analysis" (perhaps there is a betteer way?)

I have currently messed around with For and While functions to loop through but I can't figure out to get it to go through each team (right now I have three, need to make it support about 100)

Please feel free to leave any comments or questions, especially if you need a better explanation of what I am trying to do (just in a rush right now).

If you go to the end, Thanks for reading!
15368

[p45cal]

You're discovering how difficult things can be when trying to summarise data!
There's a more efficient way with a pivot table(s).
It needs a little adjustment to your Data sheet headers; I've made a copy of your Data sheet in the attached (Data (2)) and altered the headers (and hid your original Data sheet). The idea is to put all the headers into a single row, and make them all different from each other (so far that's not the case since I've only adjusted the Autonomous half of the table (and I've only used that half for the pivot table)). I envisage you might pre- or post-fix the headers later with A or T to make the entire table headers unique.
The pivot table I added is on the Autonomous Analysis sheet at cell O3. I added a little data to the Data (2) table to illustrate the result. If you make changes to the Data (2) sheet and want to see it in the pivot table, right-click a cell in the pivot table and choose Refresh to update it.
I think it shows what you want - you can play with the filters. I'm not sure what you wanted in the column headed Percentage of Starts so left it.
There is no VBA needed with this pivot table.
At the moment it's very raw, the source data for the pivot is only about 8 rows of data (make that data table a true Excel Table and you won't have to change the pivot's source data range in the future).
Is it a route you could consider going down?

An initially seemingly easier solution is using formulae in that analysis sheet. Try pasting this formula into cell C5 of the attached:
=COUNTIFS('Data (2)'!$A$5:$A$36,$A5,'Data (2)'!$E$5:$E$36,C$4)
then copy across and down. You'll see that it mirrors the pivot table.

One line of VBA will put those formulae in for you:
Sheets("Autonomous Analysis").Range("C5:K17").FormulaR1C1 = "=COUNTIFS('Data (2)'!R5C1:R36C1,RC1,'Data (2)'!R5C5:R36C5,R4C)"and the formulae will update their own values…
or if you want no formulae:
With Sheets("Autonomous Analysis").Range("C5:K17")
.FormulaR1C1 = "=COUNTIFS('Data (2)'!R5C1:R36C1,RC1,'Data (2)'!R5C5:R36C5,R4C)"
.Value = .Value
End Withbut these lines would need to be run everytime you wanted an update.

[roboticskid]

Thanks for the suggestion p45cal.

I haven't really ever experimented with pivot tables before, but after playing around with it a little it seems like it will do exactly what I need it to thanks for all your help!


Came across another issue as I working to convert my data table into an excel table-> for some reason my userform keeps entering new data outside the table, the code tells it to place in in first empty row. For example, table ends at row 104, new data appears in row 105 or greater

[p45cal]

the code which determines which row things are to be written to is:
eRow = Sheet4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Which does the equivalent of selecting the bottom-most cell of column A of sheet4, then on the keyboard pressing End, then the up arrow, then after that pressing the down arrow key once and reading off the row it's on.
Do this on the sheet in question. Does it end up on the right row? If not, check for the presence of something in column A of what looks to be the first empty row.
If you've created a new data sheet it won't be Sheet4 anymore, check in the VBE what the codename for the new sheet is, or use a different way of referring to that sheet such as:
eRow = Sheets("TheNewSheetTabNamePlacedHere").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

[roboticskid]

Yup I tested out what you asked me to, turns out the VBA code senses the empty values in the table not as empty but rather blank. I was able to fix it by cutting the table length, and then just letting Excel extend the table for each new entry.

[SamT]

Interesting Project. I kinda rewrote it as an aid to understanding it. You may be interested in my style of coding.
15370

Anyway, I thimk you need an Array Formula, which I have no experience with, but I will change the Title of the thread to attract those who are.

Edited to add: I started on this before Post #2. :hi:

[p45cal]

Yup I tested out what you asked me to, turns out the VBA code senses the empty values in the table not as empty but rather blank. I was able to fix it by cutting the table length, and then just letting Excel extend the table for each new entry.very good!