Averages Of Different Groups Of Data In A Single Sheet
Jul 15, 2014
I have a fairly large set of data on one sheet containing numbers for 60 different people. The data is set up similarly to the set below:
John Smith 75
John Smith 80
John Smith 62
Jane Doe 49
Jane Doe 89
Jane Doe 66
So on and so forth until the 60th person. My question is: Is there a way to average the numbers for each different person at one time? What about median and mode as well? Do I need to set up a different sheet for that?
I'm looking to do some simple maths with ranges in Excel but I'd like a way to be able to input all these formulas automatically although I am pretty sure a single formula per column could do it all automatically. I've got to put the sums in AD to AF on the first line of each location, but the maths is for the entire location's range.
I have another little conumdrum I'm trying to work out.
I'm using one of Ron's scripts as an add-in which I've just amended the output path for.
http://www.rondebruin.nl/tips.htm
This script essentially creates a new workbook from each worksheet in the active workbook.
As I have a workbook of about 500 worksheets, I am trying to group the worksheets opposed to having to split all and rejoin them manually.
Luckily all of my worksheets names are initials with numbers after them (as assigned by excel when merged):
e.g., RH, RH (2), RH (3), RH (4), AG, AG (2), AG (3) and so on.
That said there are some random ones too, but I'm happy for them to be kicked out as individual files.
Anyway I'm pretty sure there is a way this can be added into this existing script by addressing Sheet.Name perhaps but I'm a bit unsure exactly..
Sorted in order (i.e. 2,3,4) would also be a bonus but not essential as they need to manually checked and I have a separate macro for sorting worksheets anyway.
I am trying to find a way to sort a string of numbers within a cell in numerical order as they are entered,regardless in what order they are entered. Example 123,45,478,12 entered cell, and want it to sort in numerical order as it gets entered in the cell...12,45,123,478,4564
I've workbook contain Sheets 2716 having same column heading data in each sheet in 1st Row, also having some columns blank in between these heading so can you give me solution on it on the basis of column heading names below data in single sheet of all these sheets data.
I need a formula that will provide monthly and quaterly averages from another worksheet, but only include the months that have occurred. I have the formula to obtain both the averages and quarter totals, but it is counting all months w/in the quarterly range before the months have occurred.
I have a bunch of workbooks in a folder and I'm basically trying to take the average of the same specific range for each file. I have somewhat of a method for doing this where I separate it into several steps and grab bits of code for each step (there's descriptions on the code). How I can improve or streamline it?
Code: 'STEP 1 'run this first to combine multiple files/workbooks into one file 'Change MyPath to the folder location Sub Merge2MultiSheets()
I'm loving these new formulas that i'm learning here and applying them to whatever sheets i come across to make my life easier at work...
So now i've created another worksheet, this one is to help my colleague who wastes atleast an hour everyday to generate a consolidated report of our bank accounts...
Now i've sorted out half of the report where he can give an overview of the position of cash flow on a daily basis.
Theres however 1 more addition i wanted to make to make this workbook absolutely perfect!
On sheet 2 (which is my consolidated report sheet) I want to add all the transactions that occur on a particular date from all the different bank accounts (Each account having a seperate worksheet)
I'm not sure if advanced filters can achieve this or not or even easier/short macros... Any tips/hints as to how i can achieve this would be awsome..
I've attached a sample workbook which has sheet1 for a bank account and sheet2 for report generation...
I want to add all the rows having "Clearing date" (column A) in Sheet1 to report date in Sheet2 ..And this for all the account sheets i can add...
Originally I had a file with multiple tabs to sort data, this was becoming very ineffective so what I have done is modified the original WB posted into the desired new format. The WS PSM-04-03A is a vlookup based report that used to pull data from each tab based upon the WS Name (the code is still their) what I have done is combined the WS and made a column(ColumnD) for what used to be the WS Name. All WS have besides "Home" and "PSM-04-03A" have been combined into 1 WS and the title for the data from the original sheet names was added to columnD of "Recommendations" in the new file. (i.e. I did not add all files just some so you could see what I was getting at).
So using the two WBs as an example the Isomeration Unit/MEK Dewaxing Unit/ROSE Unit tabs have been combined into one tab and a new columnD added to signify what used to be done with the "tab". Now if you look at the validation on WS "PSM-04-03A" on the first WB it utilizes the WS Names to populate a list in cell A4 and cell C4 then populates with all columnA items from that WS. Now that I have combined the data I still need the validations to populate the same. So A4 in the new WB would populate with all non repeats of the unit in ColumnD of the WS "Recommendations" and then cell C4 would populate with the filtered columnA results based upon the columnD choice.
OLD FILE : (PSM-04-03) PHAR Tracking v2.xlsm
NEW FILE : (PSM-04-03) PHAR Tracking Modified.xlsm
trying to copy data from multiple sheets to one single sheet. I am pretty sure this is possible. The problem I am running into is that the number of sheets at any given time is dynamic. The numbering of the sheets is from 000 to 999 (they must be a three-digit code). The other issue I am having is I only want to copy the rows in each sheet that have an "x" in column "A". Also, the row in which the first "x" occurs can differ from sheet to sheet. I have attached a copy of the spreadsheet that indicates how the spreadsheet is layed out.
I recieve a daily spreadsheet with ~25,000 rows of data and is 4 columns wide. Each day I need to break the data equally up into 19 or 20 different sheets within the same workbook. This is very time consuming as I need to copy the first ~1,000 rows and paste it into the first sheet, then I need to take the next ~1,000 rows and paste it into the second sheet, and so on.
I have attached a small example with desired output. In my example, the raw data file contains 19 records. For these 19 records, I need to distribute the records evenly between 5 individual sheets. In this case, each sheet will contain 4 records or less.
i do need a macro which would solve in which i had to feed
Id Name a1 a2 a3
id is being repeated numerous times so i had to fill all the remaining fields also this is done many time so i need a macro which can check the id match it upwards in the data and copy the 4 fields paste those copied fields in front of teh id which is being inserted.
The worksheet is formatted as follows: It contains data in columns A-L. I want to breakdown the data further into sheets according to the data that is found in column G. As an example, say I have 1,000 rows of data and in column G there are a number of different branch numbers in each row. So there may be 250 rows with branch 450, another 250 rows with branch 360, another 200 rows with branch 777, and finally 200 rows with branch 888. In this case I want to create 4 new sheets (450, 360, 777, and 888) and copy only the data in the main sheet that corresponds to same branch.
Actions would be as follows: copy 250 rows to tab "450" copy 250 rows to tab "360" copy 200 rows to tab "777" copy 300 rows to tab "888"
Any comments are much appreciate. I have attached a data sample along with desired output, if needed. One note: the data comes in each day as one single sheet of data and the number of branches varies day-to-day, as such, code would need to create new sheets for each branch found.
Call all data in new workbook or in single sheet from different sheets (except 1 or 2 sheet) of workbook. i already use some macro to hide some data from sheets so i want only filtered data.
The above is sample data and the rows will change depending on data. Based on email id the range should be picked up and the email should be send as a attchment.
I have 2 sheets which validates data using vlookup.
Sheet1
Table 1ABResult1121232313
=VLOOKUP(A7,B:B,1,FALSE) is the formula in result1 column. Sheet 2
Table 2DEResult21020102040203010#N/A
=VLOOKUP(F7,G:G,1,FALSE) is the formula in result2 column.
The basic idea of these vlookup is to check whether the data present in A or D column is present in B or E. If the data is there then it will be retrieved in "Result1" or "Result2" columns. If not then the result would return the value "#N/A".
What im trying to do is to copy the resultant data(from Result1, Result2) other than "#N/A" from individual sheets to a final consolidated sheet.
I have a workbook ("CaTr") Sheet1 has data ranging ("B2:I41").
There are about 30 cells values (scattered) which needs to be copied in a workbook "CA_Log" in one single next available row .
As soon as the CaTr.sheet1 is filled I want the operator to click a button to trigger this event. Then the sheet1 in workbook CaTr should be saved as "G3" cell value.
I am trying to create a single input sheet that automatically feeds data into several other worksheets within the same workbook. Some of the sheets use the same data, some are completely independent of each other. The point of having this is so that users can go in, fill in the input sheet, and the other sheets will automatically be filled with data from the input sheet.
I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
I have a workbook containing about 20 sheets. I need to find a way of pasting all the numbers in the A column in each sheet (sheets 1-20) to the last sheet so I get a great big list of all the numbers in the 20 sheets. How would I go about doing this?
Right now I'm cutting and pasting from sheet 1 to my last sheet, then from sheet 2 to my last sheet, then from sheet 3 to my last sheet... Is there a quicker way? The ranges in each of the sheets start at A2 and go down a few cells. Sheet 4 might have 4 numbers in the A column, sheet 12 might have 47, sheet 17 might have 8 and so on.
How would I go about getting all the A columns in the sheets to the last sheet?
I'll throw up a few screenshots if the problem is unclear
Workbook1 has only 1 sheet (Sheet name is Final) with multiple rows Tiger,Lion,Goat etc..
Workbook2 has multiple sheets (Tiger,Lion,Goat .... so on)
Each sheet Tiger, Lion, Goat has different no of rows but same no.of coloums.
I want to copy the data from all the sheets in Workbook2 to one sheet Workbook1
ie., once i execute my macro the final output in Workbook1 should contain Tiger under that all the rows from sheet tiger(Workbook2),Goat under that all the rows from sheet Goat(Workbook2) ,Lion under that all the rows from sheet Lion(Workbook2).
Here rows should be inserted in Workbook1 and in these inserted rows we should copy data from respective sheets of Workbook2
I need to import the data from a specific sheet (same named sheet on all closed workbooks) to a sheet in an open workbook. All the columns are identical in every workbook but the number of rows is variable, so the data from each subsequent workbook must be appended to the end of the current data.
Whenever a button is pressed, this macro will clear the sheet, then import the data starting in A3. The workbooks are in different folders but they all have the same name, so some sort of explorer window will probably be needed to actually select each file.
I have an master excel file with 20 sheets with names x,y,z,a,b,c,f,.... Each and every sheet has data which start from Row 7 and Column 2. Now i need to consolidate this data in one sheet in another excel file.
Consolidation should be like
Suppose X sheet has 20 rows and 4 columns of data which starts from Row 7 and Column 2, this data has to be copied and pasted in my new excel file copied on my desktop. Now first 20 rows are occupied in new excel file.
Now code should move on to master excel file Sheet Y which has 45 rows and 4 columns of data which starts from Row 7 and Column 2,this data has to be copied and pasted in my new excel file from row 21, which means Master excel file sheets has to be clubbed to one consolidated excel file.
In All the sheets in Master file Data starts from Row 7 and column 2.
Data range varies row wise in each sheet but column length is fixed to 4.
I have a number of worksheets and need to move data from the same cells of each work sheet to a summary worksheet. I manually entered the cell id's for the first sheet.
Example =Jan!M6, =Jan!N7, =Jan!O9 etc.
I know I should be able to automatically enter these same cells for the remainder of my worksheets with out manually going in and typing each one (there are a lot of entries.) I just can not remember how to do it. The next sheet is named "Feb" and "Mar" etc.
I have an excel workbook I'm working on, and it needs to be protected. I have some groups that can be expanded or hidden, but once the sheet is protected you can no longer use the little button to hide or expand the group.
I have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.
I want to have the values in the corresponding cells starting with range ("e2")
I'm trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.
This grouping would be repeated 4 times, but there are some conditions.
Firstly, the same person cannot be put into a group again with someone they have previously been grouped with. Secondly, someone appearing in the first or last groups cannot appear in that group again.
I've been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.
I attached an example dataset : demo dataset.xlsx
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Attached is a spreadsheet showing various stockcodes in various districts with their prices. My aim is to find the median stock price of each stockcode which might show me the district which has a huge variance in price with the same stock. Average can be used on a subtotal, but median is not there. The spreadsheet is an example and the real list is thousands of rows, so I can't manually go and change the range for each stockcode encountered to find the median for each stockcode.
Any suggestions, I am using excel 2003 and can't download add-ins due to my work's network security, though I could do it at home if someone found a good add-in etc.
