[Solved] Script to selectively populate data in a column

[anachro]

Hi, I analyze baseball statistics using spreadsheets, but it takes many hours of work to enter data for each team. I know there must be an better way to do it than manually. What I'd like to be able to do is have a particular performance statistic for a given team or player entered into a column. I have lists of players, teams, or games in Column A, and I'd like to have a script that searches that column for every row that contains a bit of text (such as "oak" for Oakland Athletics) and then places a statistic in the same row in another column which will be devoted to that criteria. I'm thinking something along the lines of Nested IFs, but I'm a rank amateur when it comes to coding, and any guidance will be appreciated, as it will save me huge amounts of time.

[John_Ha]

Please upload a small file showing what you want to do (ie the column of names, and what results you want to extract from it into the other column) as so much depends on the original data.

Press POSTREPLY and click the Upload attachment tab below where you type (128 kB max); or use a file share site such as mediafire, Dropbox or Google Drive for a larger file.

[anachro]

In column A is a list of match-ups with the team abbreviations. In part of column B I've placed the pitching stat earned run average (ERA) for that teams' pitching staff for a given season. So far, for this type of list, ive been going team by team, and using the search feature to find instances of the team abbreviation then pasting the figure into the correct cell. I'd like to be able to place the team data in the appropriate cell without having to type the figures in individually. For example, maybe there is a way of filling in the data for each stat for each team once, then running a script that completes the rest. Alternatively, perhaps a script could be developed that has the figures incorporated in the code. Either way would be a considerable savings of effort. Thanks very much.

[John_Ha]

I don't know precisely how to do what you want so I would have to look it up. See Using regular expressions in functions in the Calc Guide (page 191) which shows how you can search for text like Oak.

For example =COUNTIF(A1:A6;"r.d") with Enable regular expressions in formulas selected will count cells in A1:A6 which contain red and ROD.

See [Tutorial] How to record a macro (and Regular Expressions) for an introduction to regular expressions.

[John_Ha]

I'm thinking something along the lines of Nested IFs

See Easy way to make highly nested conditionals at viewforum.php?f=75

See Calc Functions listed by category at https://wiki.openoffice.org/wiki/Docume ... y_category. How about VLOOKUP?

VLOOKUP

Returns a value from a table column, in the row found by lookup in the first column.

[anachro]

Thanks, I will study those links.

[LeslieS]

Hello, I have been looking at this and I really don't think you need a programming solution. At least not yet.
Start by making a Team List page that includes a list of all the teams and their corresponding pitching stat.
Add a Matchups page that uses two columns; Column A is the team name and Column B is the pitching stat.
For column B use the formula

Code: Select all

=INDEX($'Team List'.$C$1:$C$30, MATCH(A2,$'Team List'.$B$1:$B$30,0))

where:
- TeamList is the page with the names of the teams. Column C on that page contains the team name initials.
- Column A on the Matchups sheet is where you type the team name for the matchup you are analyzing
- Column B on the Matchups sheet is where the formula resides.
Then you just drag-copy that formula down as needed.
I have attached a spreadsheet to better understand what I am getting at.