Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Summarize excel data Results

I have lots of excel data in one format and I want to go to another format. See the attached "Formats" file. The data on the left is the current format and the data on the right is how I want to end up. It's almost a transpose but not quite. I think the only way to do this is VBA. I am not fluent in VBA to come up with the code on my own. I can only copy, paste, slightly modify pre-existing code.

The data is on several tabs within a workbook. I want to combine all the data from all the tabs into one tab.

Attached also, is a "sample data" file to work with.

If you can help me with this I will be extremely greatful

Hey guys,

I hope anyone out there has an idea for this because, although I'm fairly good with Excel (not VBA, sadly), I think this time I've bitten more than I can chew .

I'm trying to put together a dashboard in Excel 2010, for a HR project I'm working on. For this, I'm starting with two employee summaries, based on showing the employees as headcount or FTEs, for 3 different companies. Each list contains data related to employee's gender, level (management or staff), age bracket, level of education, status and company ID (Comp1, Comp2 or Comp3). In the FTE summary, I also have the FTE correspondence, in a separate column.

In the actual dashboard, I have 7 comboboxes that I would like to use as criteria to summarize the data in the lists and pass the result (count for the headcount list, sum of FTE for the FTE list) to a textbox. The criteria are:

1. The way the number of employees is viewed: Headcount or FTE;
2. Company: All; Comp1; Comp2; Comp3
3. Level of education: All; high school; university degree; masters' degree; PhD;
4. Position level: All;Director; manager; supervisor; staff;
5. Employee status: All; active; inactive
6. Gender: All; Male; Female.
7. Age Bracket: All; 45.

The default value for comboboxes 2-7 is "All".

I think that the corresponding list (headcount or FTE) should be filtered progressively based on the criteria that the user selects by changing the combobox values, but other than that, I'm lost

The result of the count/sum should go to sheet "Calcs", cell I12 (if the data is viewed as headcount) or J12 (if the data is viewed as FTE). From there, I'll take the result and pass it to the textbox.

Does anyone have an idea as to where I should begin? I've been working on this for the past week, trying and discarding option after option, and I still can't figure it out .

Sorry for the long post, and if you need a sample workbook, please let me know.

Thanks for your opinions and help,

Radu

Attached is a workbook, MonRental.xlsm, where I would like to use the sheet: PlaySpace and create summary lines of totals (Column C) for each Equipment# (Column L).

Excel's Consolidate and/Subtotal options seem to fall way short. When I arrive with two columns of summary data (Equip# & Equip Total) I will need to reference that data via VLOOKUP in another workbook so it is important that I get 1 line of data per Equipment#.

If I were working in my old AS/400 - RPG environment I would simply query the data, summarizied (control break) by Equip# and write the output to a workfile. However, .....

Thoughts and consideration appreciated,

Dan

Hello All,

I have been struggling with, what I thought was pretty straight forward, problem.

Basically, I have a large excel file with many many tabs, all with pivots that summarize similar data, and vary in number of rows. Lately I have been using a simple sumproduct equation to match values in the "volume" column with the minimum Market Share requirement. It works fine, but it is tiresome to update every tab all the time, I would like a more elegent way. I figured all I would need to do is a simple sumproduct statement with a dynamic offset embedded. But it didn't work.

I have attached a dummy data set with the pivot and how my file is generally structured...any thoughts??

Many thanks!
Matt

I have data on ground water level from 40 different sites that I am trying to put into one table, sorted by 'DateTime' with a column for the 'WaterElevation' readings from each site. Readings were taken at irregular intervals (daily, twice daily, or hourly; varying throughout the 5 years there are data for), so 'date:time' information does not match, wells were initiated at different times and there are sites with periods of missing data. I have the data stored in a Excel spreadsheet (each site as a worksheet) and in an Access database (each site as a table). There are 16,000 - 20,000 rows per site.

Currently data looks like this:
Site PW18
DateTime DTWBelowCasing DTWBelowGroundSurface WaterElevation 2/11/2009 9:30 756.55 755.58 4429.42 7/14/2009 18:11 756.04 755.07 4429.93
Site AG15
DateTime DTWBelowCasing DTWBelowGroundSurface WaterElevation 2/12/2009 8:30 756.55 755.58 4329.42 2/21/2009 9:30 756.04 755.07 4329.42

I would like to combine it to a table like this:
DateTime PW18_WaterElevation AG15_WaterElevation 2/11/2009 9:30 4429.42 2/12/2009 8:30 4329.42 2/12/2009 9:30 4329.42 7/14/2009 18:11 4429.42
Ultimately I would like to summarize this data in one pivot table.

I'm using Excel for bookkeeping and balancing a budget. I've created one sheet for all my raw data and the other is to summarize the data using a pivot table. On the raw data sheet I have labeled my columns for the pivot table. I was hoping that in the pivot table I can select a year and have all the months for that year and data of that year be available, but not other years. I was also trying to have only the month selected of a year selected and that data be available. I also wanted to show an accounting of money spent by item.

Instead, I have a pivot table that is confusing and very unattractive. The example of this will show my limited knowledge in pivot tables. I'm hoping some of you more affluent "guru" members may be able to help me get something usable and presentable.

I tried creating a dynamic named range for the pivot table "BookKeeping" and a dynamic named range for the money out and money in "Accounting", but I'm not sure if I did that right. I did this because more columns will be added over time. This is the reason I'm asking the above two paragraphs.

Should I be using filters some how?

Please someone help?

Thanks, Jimmy.

EDIT

The Summary Sheet also has the balance of available funds that I'm somehow looking to include in this pivot table. Can the pivot table keep a running balance?

Hi I am very new in excel VBA thus am not sure if it can do this.Am sorry if my expalantion is not clear.Will try my best to explain what I am looking for.Any help would be much appreciated.

I am collecting data for my company servicing which include Date, Job No,Model, and Fault). What I need is at the end of the month I will need to summarized the data.With just an execution, I can see which services which is stil pending or was service more than 7 days. Is there any VBA or simple functions to do this?

attach is the data example.

Thanks in advance!

I have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.

Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.

Hi,

I have the following two spreadsheet.

Table 1
Data

 BCDEFGH2No.DATEItem#Error PointsStart TimeEnd TimeCompletion Time313/17/2008M112135618.10 PM10.00 PM1.50423/17/2008M112135618.10 PM10.00 PM1.50533/18/2008M112147808.15 PM10.00 PM1.45643/18/2008M1131356010.20 PM11.50 PM1.30753/18/2008M112145601.20 AM3.00 AM1.40
Spreadsheet FormulasCellFormulaH3=G3-F3H4=G4-F4H5=G5-F5H6=G6-F6H7=G7-F7

Excel tables to the web >> Excel Jeanie HTML 4

Table 2
Summary

 BCDEFG2Time Monday 3 Start TimeEnd TimeCompletion TimeError Points43.00 PM     53.30 PM     64.00 PM     74.30 PM     85.00 PM     95.30 PM     106.00 PM     116.30 PM     127.00 PM     137.30 PM     148.00 PM     158.30 PM     169.00 PM     179.30 PM     1810.00 PM 8.10 PM10.00 PM1.511910.30 PM     2011.00 PM     2111.30 PM     

Excel tables to the web >> Excel Jeanie HTML 4

Based on Table 1, I need to summarize the data in table 2 based on day and time.

Example is based on the data for 3/17/2008 on table 1, the data is populated in cell D18 to G18 in table 2 for Monday.

In table 2, the data is populated based on end time. Since on 3/17/2008, item #M1121356
was completed at 10.00pm, therefore it is populated on cell D18 in table 2

The item# in table 1 sometimes may duplicate as shown in cell D3 and D4 in table 1.

Appreciate help how to populate this data in table 2. Thanks in advance.

HI

i have a workbook that contains 60 sheets, each sheets have at least 1500+rows., i am now trying to copy and paste the data to a single sheet..however i found that excel 2003 only handle 65516 rows(not going to use vista at the moment).

so i am now having a question, i heard that we can build database using access, but i am totaly novice to access, so will you suggest me to stick to excel and summarize the data sheet by sheet or...i can do this by using access ?

thank you

Hello Board,

I need a way to consolidate information from 5 plants with 7+ salesmen ea.who may have 25-30 customers each. I need to take a excel form I created for each salesperson with forcast and monthly customer info from each salesperson and summarize it for the regional sales manager with meanful data. There is currently a column for Forcast data, current month data, previous month data and last year at this time date. Should I consider using excel, excel pivot table or access. Each salesperson emails this data month to me and its my job to summarize it for the manager. Right now the only thing I can think of, is a spreadsht with tabs for each of the 5 plants and list the salesperson down a column etc...then somehow combine that information into a summary for the month after all 40-45 salespeople have turned in their info, but is there a better, quicker and more effective way to summarize this data...any suggestions? Thanks in advance

I have an excel sheet with each state in the first column (A3:A54). The column headers are also states. It is a matrix for us to view how many sales we have made in each state, and the state we shipped it to. See below:
AL AZ AR CA CO
AL 0 2 1 0 5
AZ 5 3 0 1 0
AR 0 1 2 1 0
CA 15 6 5 0 0
CO 0 0 0 5 6

I need a macro that will summarize the data like below:

AL AZ 2
AL AR 1
AL CO 5
AZ AL 5
AZ AZ 3
....AND SO ON... As you can see it would summarize on the state in column A, and show the state we shipped to, skipping over the null values. Any HELP WOULD BE GREATLY APPRECIATED. THANKS

I use an multi sheet Excel 2010 workbook to collect and analyze data. The workbook contains 15 sheets total and 3 sheets are dedicated for summarizing the data and printing a 3-page report. The 3 sheets are contiguous.
Two of the pages are strictly tables with data while the 3rd sheet contains both a table and a chart.
If I select the 3 sheets and then select Print Preview, everything looks fine. If I send the print job to my laser, everything comes out fine.
If i send the print job to Acrobat Distiller 8, the following happens:
If the chart sheet is first in line (preferable), the page with the chart will print and the balance of the report is dropped.
If the chart sheet is second in line, the first page will print and the balance is dropped.
If the chart is last in line, the first two pages will print and the last page is dropped.
I've also had the same problem with previous versions of both programs.
Any thoughts?

When I create a pivottable from some imported data Excel (2003 SP2) sometimes
chooses to summarize the data by using the function "count" instead of "sum",
which I then have change manually. What is it in the database that is
triggering Excel to choose either function?

Dear All:

First, allow me to express my deepest thanks for all of the help y'all have provided me over the years while working and figuring out my absolutely FAVORITE application - Microsoft Excel!! Hopefully I can add another notch on the "solved" column after this one..

Following this preface, you will find my original post [which was] under the subject "lookup stock symbol on worksheet and return summary data"

Basically, I may have asked my question the wrong way. What we are trying to do basically is create a form in excel or at least some type of function/macro [in Excel] that can query the other existing worksheet FULL of data. All I really need to know right now is how to return this data using whatever means necessary. I'd REALLY prefer to just use a text entry box like he and I discussed and enter for example "DELL" or "IBM" or "MSFT" as the ticker symbol, and have some fields on my main worksheet populated with numbers/data from the source worksheet. Of course I will want to make calculations on this data - I think I should be able to figure out how to incorporate them into the code after someone can enlighten me as to how to initially retrieve said data from the source worksheet.

Take a look at the correspondence below, or just see the original thread for reference. Biff was helping, but I think it may have just been a "right church..wrong pew" kindof thing. I have worked with pivotables, and I don't think that is the avenue I want to take. And like I said before, this is obviously (DEFINITELY) a solution better provided by a database, but unfortunately the ability to create or have someone create one at this point in time is not an option.

And hell, they have the "Control Toolbox" and "Forms Toolbox" options in Excel, so I figure they must be there for a reason.

If someone could please point me in the right direction, I'd be much obliged.

Thank you very much for your time and efforts in this matter.

Best Regards:

Greg Purnell

Jgpurnell13 - at - Verizon.net

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

HERE IS MY INITIAL POST:

>>>>>>>>>>>>>My friend tried to stump me, but he didn't say I couldn't use you guys to find an answer (not to mention if I do find a solution I can bill his firm)...so here it is:

He has this summary data on one worksheet (but with MUCH more data columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%

and this is on the other worksheet:

ACG Enter symbol here

-2.24% Returns the average

Obviously, the formula he has in the lower box (where result populated is -2.24%) is: =AVERAGE(Data!3:3) (keep in mind I hid 100's of columns so avg is incorrect)

Very basic, I know. So I called him after he sent the email to find out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the averages on the source worksheet and just using the =Cell() formula to
return the results in a more readable format.

He does, however, (and as I suspected) want to bring in a lot more than just averages (SDs, variances, etc., etc.). Basically, he wants
to type in a symbol and have it return a bunch of data. He has it figured out one way with an array formula, but with over 1000 symbols,
and market data dating back to early 2006, it is obvious he has way too much data. He did, of course, tell me that the array formulae did
take forever, so on to plan B.

I have some ideas, but I have other stuff I need to be working on, so I just figured I'd come to the place where I've always been helped
with Excel (immensly!).

Basically, he just wants to be able to type in a symbol (a market ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data or variations thereof.

I did tell him right off the bat that this is an obvious and definite application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he doesn't have the time to learn how to develop and implement one (a
db), nor the funds to have somebody else do so.

Also, as a side note - if you have any comments, every time I bring up how he should be doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.? I've never heard of anyone using it in the tech circles I am in. Is it a "Mom & Pop" type business app, or just a personal db for someone to arrange their CDs/DVDs, or do businesses actually use the software?

Thank you very much in advance all. You guys and gals have been absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

BIFF's INITIAL REPLY:

>>>>>>>>>>>>Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MY REPLY:

>>>>>>>>>>>>>>>>>>>>Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or something
and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2 columns
for examples' sake.

Any other ideas?

Thanks,

Greg

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Well crikey! "He" has to do some work!

Maybe a pivot table but I really can't help you with that 'cause I hate pivot tables!

Biff

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Client has some Excel spreadsheets on her desktop. Double-clicking the
spreadsheet icon opens Excel, but not the spreadseet. The spreadsheet can be
opened by using File, Open from within Excel. Same problem occurs when using
Quickbooks - trying to use the QB Reports "Summarize Payroll Data in Excel"
exhibits same behavior. Have removed and reinstalled Office XP Small
Business, reinstalled all Service Packs and patches, problem continues.
Help!?

Thanks in Advance, Neal

Hello all,

I am working on a spread sheet that has various columns of text. In each
column, it is important to note that there will be some duplicate data.
Under each column, I would like to have excel "summarize" the data. Consider
the following example:

column 1 column 2
-------------------------
A X
A Y
B Y
C X
B Z
A Y

Totals
------------------------
A 3 X 2
B 2 Y 3
C 1 Z 1

(I hope this formatting is posted correctly!)

As shown above, I would like to have excel sum the duplicate entries of each
column below. Is there anyway to do this? Thanks in advance!

Francis

I have an Excel File with ~4000 unique account numbers. For each
account, I need to create a single form letter with an attachment to
the letter for each. In the Word Document attachment, I need to
summarize data contained in the Excel Spreadsheet (Word and Excel Files
are attached). Is there a way to somehow import this data into Word or
to create the same table in Excel. Also, the data will need to be
subtotalled if the customer purchased the same security but on
different days. For example, Suzanne Mullens purchased OPPENHEIMER
LIMITED TERM GOV'T CLASS B on three different days. I would need all 3
purchases summarized in the same box with a subtotal of quantity
purchased and total dollar amount. Open the attached files for more
clarity. Thanks for any help.

+-------------------------------------------------------------------+
|Filename: Mail-File-Extract.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3885 |
+-------------------------------------------------------------------+

--
maacmaac
------------------------------------------------------------------------
maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959
View this thread: http://www.excelforum.com/showthread...hreadid=473812

Excel 2003
Since there is no way to refresh pivot tables in a shared workbook, i have
created a second (not shared) workbook, identical to the shared one.
It contains links to all the cells of the shared files and pivot tables
summarising the shared (linked) data.
Later I would like to link the pivot tables of the unshared file back to a
little summary table in the shared file.

My Problem:
The pivot tables of the unshared file should summarise the linked data,
since every cell contains a link (formula), empty cells are counted by the
pivot tables .

I've tried to use If functions to set empty cells empty (if(xy 0, xy, "")
but the pivot table still counts those cells.
Also, setting the pivot table count to countnum retrieves wrong values,
since some of the cess contain figures and some of them contain text.
Somtimes couloums dont contain empty cells at all.

any idea how to solve the problem? I guess I'm looking for an IF function
that
gives out a value, that is not counted by pivot tables.

thanks!

Hi All;

I need guidance on creating a formula that picks differences out of an
entire column of data and shows them in a single cell.

Example

1/1
1/1
1/1
1/1
1/1
1/2
1/2
1/3

I am trying to get Excel to summarize in one cell the different data sets in
a format similar to: 1/1;1/2;1/3. If all the data in the column is the same
(ex, 1/1), then it would just need to output: 1/1.


No luck finding an answer? You could always try Google.