I have attached a workbook where the data in columns D:N is collected from another source using Vlookup. In Row 5 There are three numbers 60, 74 & 93. I need a way of condensing those 11 rows down and getting those numbers to appear in a summary for the week (the yellow box) on the right.
View 6 RepliesI've got a worksheet with a 1-row gap in between every two rows, and I'm hoping to delete those gaps. In the attached spreadsheet, worksheet 1 (Initial) is what I'm starting with, and worksheet 2 (What's wanted) is what I hope to achieve.
Now obviously, if it were only 10 lines of actual info, I'd just cut/paste. But, I've got ~950 rows of info.
IIn my example I have result data from the 2013 USPGA Tour, ordered by player (column A) with subsequent columns detailing their finishing position in each event.
I simply want to condense each player's performance data into one row - as doing it by hand after each event is very time-consuming.
Is there a way of using a simple formula, macro or pivot table (or whatever) to merge each player into one row, but keep each column in the same position (ie to correspond to each weekly event).
Is there a way to make this a bit shorter without altering the result.
IF(OR(AI10="",ISERR(VLOOKUP(AI10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AI10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))
+IF(OR(AK10="",ISERR(VLOOKUP(AK10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AK10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))
+IF(OR(AM10="",ISERR(VLOOKUP(AM10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AM10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))
I initially had a SUMPRODUCT formula that looked like this:
=SUMPRODUCT(--(ISNUMBER(MATCH('Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79,F1:F5,0))),'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)
However, I turned the page into a dynamic page where the month can be selected from a drop down menu and changes the column index number in Cell F8. And because VLOOKUP can give me an error result if there is a mismatch, I used that combination of Blank cells or ISERR. In the range AI10:AM10 there should be three lookup values (sometimes only one or two), and empty columns between the three values columns. I also will use this formula like a 1,000 times so, arrays is not advisable.
I think this must be fairly easy to do. I've found various posts here and tips elsewhere that indicate ways to do this, but I've not found one I could get to work.
I have a column with numbers in it. These numbers are formatted as TEXT (because they are 001, 002, etc). This list is generated by pulling the unique values from another column where these numbers are repeated.
In the following example, B has the original data. H has the same data with just the unique values. That is how far I have got. What I now want is what you see in Column I (the unique values with NO BLANKS in it).
B | H | I |
-------------------------------------------
001 001 001
001 002
001 003
002 002
002
002
003 003
003
003
I am aware that I can use an Advanced Filter (not that I manage to get that to work without it generate an erro). But this is not what I want, as I'd like the data in I to be dynamically generated such that if more unique values are added to B then H will reflect this (it does that at present) and then I will also reflect this.
I have a spreadsheet with over 1000 rows of information.
There are two columns FROM_ACCT_NO and THRU_ACCT_NO.
I need to create a macro that will give me a condensed account ranges.
Example the first range would be 2000000 to 2000003.
The next range would be 2000005 to 2000298.
And so on, and so on etc....
Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.
[Code] ......
Would like to Condense Column Q to Col S no Blanks no Zero's
Tried formulas on net but won't work for me ? ?
I have data that is being pulled down from an external source into an Excel spreadsheet. The data comes in the form of a 1 for TRUE and a 0 for FALSE. This is in the picture below.
data.PNG
I want to take the "1" or "0" and use an If statement so that if the cell contains a 1 then the word TRUE appears in a second sheet and 0 is FALSE. I only want the 1 or YES rows to be copied as in the picture below.
Capture.PNG
Pretty much each column in the data that has a 1 or 0 will be turned into words in the second sheet.
need to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.
This is what I have so far, but I can't seem to get the loop right.
Rows("2:2").Select
Do
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
activecell.Offset(-1, 0).Select
Range(activecell, activecell.Offset(0, 5)).Copy
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
Selection.Offset(1, 0).EntireRow.Select
Loop
I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
FeS_Kinetics.xlsx
I am an undergraduate biology major working on a geometric morphometrics project. It is focusing on wing asymmetry, so I have data for the left and the right wings for a sample population of 30 individuals. The data from the software is exported as a spreadsheet with two rows of data for each individual, since each wing was calculated separately, and my mentor has asked me to average the data for subsequent analyses in another program.
I'd like to do this easily with an Excel formula, but when I try to do the averages I'm having a little trouble getting the formula to carry on correctly. I need it to average, say, E2:E3 then the next cell average E4:E5, but instead the only thing I can get it to do is average E2:E3 then the next cell do E3:E4. Which obviously doesn't work for me, since E3:E4 is data from two different individuals.
Is there a way I can do this for my data? One of my spreadsheets is a 60x32 matrix of landmark coordinates, so I'd really rather not try to do all the cells individually.
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
I have a spread sheet with values in the area of A1:H834
In column H, I have number values from 1-7.
Essentially that number value means that the values in the row are duplicate.
So, for example, if H2 has a value of 4, that means that $A$2:$G$2, really should have an additional 3 rows underneath with the EXACT same data in each cell, however, the way the sheet was created, was to remove the duplicate values and just indicate in column H, the number value of how many duplicates $A$2:$G$2 really is.
I need to unpackage this and create what it was originally. What type of formula can I use, to look at the value in H2, and then insert underneath that number of rowes with the exact same data as A2:G2 and do the same for the remainder of the table all the way down to A834:G834
See the attached xls. There are two worksheets contained therein, one with the original data, and the other showing how I'd like it organized.
I have rows of product data consisting of a product id followed by repeating attributes of quantity and associated price. I need to convert each product row to multiple rows of each quantity/price pair while preserving the id for each pair.
I have a worksheet with 40 colums and 9200 rows. There are columns with empty data in the rows. Can I have a macro to copy data in the rows in Column E, F, I, J K and L to empty rows below?
View 3 Replies View RelatedI have raw data stored in one row which I would like to break up into four individual rows.
I cannot get the formula to repeat and I don't want to manually do it.
I have a game where people are scored on the spreadsheet, but they can join anytime, so everyday, I get a combined list of points. The list consists of people who joined previously, and the new players. How should I combine the points?
[URL] ..........
I want copy the data from Rows 25-29 (B25:E29) to under Rows 20 (B20:E20) or Rows 21 and next rows, When I click this Button (New).
And then after copy the data, data in Rows 25-29 (B25:E29) will be deleted, And insert new row for the next data.
I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J
Email in Column L
Memo in Column N
Balance in Column T
Due Date in Column P
Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L
Subject: "Company Payment Remittance Payment Date *Date from Column P*"
Body: Hello *Name from Column J*,
For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
Example Workbook for Email Macro.xlsx
I'd like to have a sheet with multiple columns of data (say A thru K for instance.). Id like to reserve column A for ONLY imputing an X. The rest of the columns b-K would have data in the cells. I'd like to have a macro that when it saw an X in column A, would copy all of the data in cells B-K in that row, paste it into the next empty row of a second sheet (for history tracking), then go back to the original sheet and continue looking for additional "X"'s and repeat. Once all of the X's were copied, it would "clear" (Not delete because some of the cells would have formulas in them that would need to remain for future use.) the cells based on the "X" then finally move all of the remaining data up to the empty rows to fill in the empty rows. This last piece would be more for esthetics to have a clean looking sheet.
View 1 Replies View Related I have created a userform with multipage, has two page that add new record in a excel sheet. Data has a unique reference no.(TxtRef.Value) for each record. I am trying to add a button to load the added data for a specific record using reference no back to userform so that it can be updated and overwrite back to the sheet in the same row, So far it is adding new record correctly. I do not know how to populate all the fields of the of an existing record and overwrite it back to the same row instead of adding a new record. Below is my codes
[Code].....
I want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
View 3 Replies View RelatedI have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series. I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.
View 5 Replies View RelatedI want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.
What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.
See annexed file for expected results.I have preference to have a formula much more.
reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.
The attached example shows before and after.
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 Replies View RelatedWhen I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
Need in displaying data by user input criteria, mulitple rows of display also need to display to blank out when no criteria is input. In example spreadsheet the criteria cell is B6 in "Results" page and needs to pull in data from the "Database" page.
View 3 Replies View Related