Solved

need code to pull data from one table and insert into another

Posted on 2014-09-08
14
223 Views
Last Modified: 2014-09-12
would like code for each cell in order to pull necessary data from the tables on the right and make the necessary calculations to achieve the results show as follows:

The sales person shown in B7 means that data for this area will be taken from the table labeled "weaver" to the right. The data corresponds as follows:

B6 is simply the DATA found in Z14
C5 is the sum of all data from X4:X13 found in          column "W"  hence the result 5
D5 is them sum of all data from Y4:Y13                      column "L"
E5 is the minimum number found in Z4:Z13       column 'ERA'
F5 is the maximum number found in AD4:AD13      COLUMN 'ER'
G5 is the average of all number found in AD4:AD13     column 'ER'
H5 is the minimum number found in AF4:AF13       column 'SO'
I5  is the maximum number found in AF4:AF13       COLUMN 'SO'

C7 is the sum of all data from X9:X13 found in          column "W"  hence the result 5
D7 is them sum of all data from Y9:Y13                      column "L"
E7 is the average of data found in Z9:Z13                  column 'ERA'
F7 is the minimum number found in AD9:AD13      COLUMN 'ER'
G7 is the maximum l number found in AD9:AD13   column 'ER'
H7 is the minimum number found in AF9:AF13       column 'SO'
I7  is the maximum number found in AF9:AF13       COLUMN 'SO'


The same will be done for the side of "Salazar" only this time the data will taken from table with his name (i.e. Salazar). and put into the correspoding cells within the table.
DataCalculations.xlsx
0
Comment
Question by:Pedrov664
  • 8
  • 6
14 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
The attached file is an updated version of your example workbook, containing literal Excel functions which satisfy your requirements.  There's nothing particularly difficult here, just MIN, MAX, and AVERAGE functions.

That said, your "sales person" data arrangement here is not open-ended and new formulas would have to be manually reconstructed if there are changes to the data tables in either size or position.  And if new "sales person" reporting is required, that would also mean new manual creation of formulas.

If this is merely an academic exercise, then this should suffice.  But if there is an actual work need that requires expandability, you'll need to provide a more concrete example.

Regards,
-Glenn
EE-DataCalculations.xlsx
0
 

Author Comment

by:Pedrov664
Comment Utility
Glen,

Thanks for your post. It makes me realize I forgot to metion one important factor.

The sales name will change from time to time and thus I require code that will look at the Name in fields B7 and J7 then find the table with that name and then fill out the fields as previously mentioned. The way you did it requires me to find the name and fill out the data myself each time. I am looking to automate the finding and filling process.

I hope this clarifies what is needed.

Thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
That's exactly what I was referring to in my previous post.  This data layout is not condusive to extracting the kinds of information you want easily.   To make this a more functional report, you want to organize your source information in a single data table.

You'd only have to make some minor changes - like adding a "sales person" name to a new column and inserting that name for each row/record.  Then you could use various modified functions to query that data and report.

So, your data would now look something like this:
data table
Now you can use new functions to return the results AND use PivotTables as well to summarize your data.  I've attached an example file that demonstrates both.   Note that the PivotTable shows overall results, but you could filter to the last five games by using either a report filter on "Games" or a slicer to pick the last five.  Also, note that the MIN and MAX functions are array functions (use [Ctrl]+[Alt]+[Enter] to complete) and show curly brackets {} to indicate as such.

{Edit:  I've updated all the functions and annotated them in the attached file}

Regards,
-Glenn
EE-DataCalculations.xlsx
0
 

Author Comment

by:Pedrov664
Comment Utility
Thanks for the input but the changes you propose would only serve to confuse since you've made changes to the original data fields and merged tables.

Once the amount of data required is input it will get confusing to do it that way.

I prefer to keep the tables separated since they will need updating and merged tables will be difficult to update. You,ve also eliminated the last row in each table.

I believe there is a way for it pull data from the table based on the table name I just do not know how to code it.

The code I need would use Table name and column or  row name to access data.

Someone has to know how to do this.
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
One can certainly use structure references to return results, but the inclusion of a manual total row makes it problematic.

For example, the win total for any given "sales person" - assuming that the table "name" is the same is:
=SUM(name[W])/2
You have to divide by two since the total row contains the sum of wins as well.  Same goes for the Losses.
=SUM(name[L])/2

The last ERA posted (in the Total row):
=INDEX(name,ROWS(name),6)

The Minimum ER and Minimum SO counts are easy enough:
=MIN(name[ER])
=MIN(name[SO])

But your Maximum ER and SO counts won't work because the manual total row is considered part of the #Data and those values will be returned if one uses the MAX functions.  So you have to revert to some indexing:
Max ER:
=MAX(OFFSET(INDIRECT(CELL("address",WEAVER)),0,9,ROWS(WEAVER)-1,1))
Max SO:
=MAX(OFFSET(INDIRECT(CELL("address",WEAVER)),0,11,ROWS(WEAVER)-1,1))

Then you have the second set of formulas that appear to focus on the last five games of results.  You can't really use structure references here because there is no provision for that in Excel.  As with the MAX functions, some indexing needs to be used.

And here's where I'll stop.  Because after doing several of these calculations, the example numbers you show don't match up with the stats.  Additionally, the total row ERA shows a value that does not correlate to the numbers above.

If you can clarify that, and state that this is indeed the direction you want to go, I'll provide all the remaining formulas for you in an un-modified workbook.

Regards,
-Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
PS.  If you also wanted to use the names in cells B7 and J7 as references to the table names, they would have to also be wrapped in INDIRECT functions in order to work.  For example (cell F5, maximum ER):
=MAX(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7 & "[#Data]"))),0,9,ROWS(INDIRECT($B$7 & "[#Data]"))-1,1))

It's not pretty, but it works.
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
PPS  Please review again your requests for columns E, F, and G.  You specify minimum, maximum, and average in various ways across each column that do not appear to relate to the column headers.

Again, if you have an actual data example - rather than baseball pitching statistics - that will greatly help providing you a solution.

-Glenn
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Pedrov664
Comment Utility
I think the best way to get the results I need is to use excel table and column names instead of using explicit cell references as it appears we've been doing up until now. For instance,

Instead of using explicit cell references                                         Excel uses table and column names

=Sum(C2:C7)                                                                                            =SUM(DeptSales[Sales Amount])



Using the data in the table provided I use the following formula in E5:

=AVERAGE(WEAVER[ERA])

Where weaver is the table name and ERA is the header label. The result I get is 3.61
 which is different from the manual average of cells Z4:Z13. Thus, one flaw is that it does not give me the same result and two I also need the table name to change based on the value in cell B7.

So Cell B7 contains the table name that should automatically be inserted into the formula.  I believe a variable or reference is the way but don't know how.

Also I need a way to specify the the second row of data (i.e. E7) should only account for the last 5 items of data. (i.e. Z9:Z13)

The link below may help to see the stuff I am  using, I just keep getting different calculations from the manual calculation using it.

http://office.microsoft.com/en-us/excel-help/use-structured-references-in-excel-table-formulas-HA102749547.aspx?CTT=1
DataCalculations.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Several things:

1) Your original post specifies that
E5 is the minimum number found in Z4:Z13       column 'ERA'
Now you're saying it's the average.  My previous post pointed this discrepancy out.  These requirements need to be double-checked and corrected before continuing.

2) As stated previously, the reason that structured referencing like:
=AVERAGE(WEAVER[ERA])
does not return the average of the 10 games is becuase you have added an 11th row of data.  Calling it a "Total" row does not distinguish it from the other data and its values are also being considered.  If you insist on keeping that row - rather than using a Table Total row - then you have to revert to using indexing functions to return the actual average:
=AVERAGE(OFFSET(INDIRECT(CELL("address",WEAVER)),0,5,ROWS(WEAVER)-1,1))

3) I also noted in my follow-up post that in order to reference the table name via the cell value (ex. B7) you have to use INDIRECT.  Using the above example:
=AVERAGE(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),0,5,ROWS(INDIRECT($B$7))-1,1))

Again, I strongly urge that you consider combining all your source data into one Excel Table so that you can greatly simplify the formulas needed to report.

Regards,
-Glenn
0
 

Author Comment

by:Pedrov664
Comment Utility
If E5 says "ERA"  then it is average I look for. Either way that is simple enough that I can correct it to say average, sum etc.

What I need help with is replacing "weaver" so it is a variable that fills in whatever is in that cell then looks for data in the table with that name.

Will the answer you have as # 3 above look for data in a table that has the name in cell "B7"?
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Will the answer you have as # 3 above look for data in a table that has the name in cell "B7"?

Yes.  
INDIRECT($B$7) returns "WEAVER" and can be used to reference the Table/Range with that same name.
0
 

Author Comment

by:Pedrov664
Comment Utility
Maybe I do not understand how this formula works. When I plug in the formula in 'E5' I get "0.20" instead of the expected 3.59. However, the same formula in 'M5' yields 4.79 which is fine considering I got 4.80 as the answer. (See attached file)

What am I missing? same formula different results?

P.S. I do not believe that combining all the source data into one Excel Table will work because each may need updating and it would nearly impossible to distinguish between data sets when they are combined as you suggested.
DataCalculations.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
Your formula does not work because you have an additional blank column in the table "WEAVER" that does not exist in the table "Salazar".  I removed this blank column in my example file in order to keep the tables consistent.  To make the formula work in your case:
=AVERAGE(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),0,6,ROWS(INDIRECT($B$7))-1,1))

If you are going to use separate tables in this manner, they should be identically constructed.  Furthermore, they should NOT have a manual total row added, but rather include a Table Total Row (Menu: Table Tools - Check "Total Row" box).

See the attached example to see how one would reference the tables in this manner.  

I can no longer provide assistance on this topic.  The best solution to your reporting issue will be to have one single data table that can be queried using simpler formulas (ex., not requiring INDIRECT referencing) and/or PivotTables.  It is not hard to use data filters on tables to isolate individual data sets as you require (ex., filter on player name to just see their records).  

Good Luck,
-Glenn
EE-DataCalculations2.xlsx
0
 

Author Closing Comment

by:Pedrov664
Comment Utility
Thanks for all your hard work
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now