Pulling Rows From Other Sheet - Skipping Over Rows If Column Is Blank
Jan 17, 2014
I am working in the attached spreadsheet. For anyone who does not wish to open the link, let's say these sheets are titled 'Sheet1' and 'Sheet2'.
Sheet2 looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
[Code]....
Now, how may I pull this data into a summary sheet (Sheet1) that skips over any instances where column A ("Year") is blank so it looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
2013 2 Pat
2013 5 Bob
[Code]...
Note: All columns are the EXACT same across each sheet(including Sheet1, Sheet2, Sheet3), if that extra piece of information works.
PS It is fine if, in order for a formula to work, I need to copy and paste the formula for the length of rows per each Sheet to consolidate.
EDIT:
So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).
I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.
Here is my failed formula:
=INDEX('2013'!F$3:F$338,SMALL(IF('2013'!$E$3:$E$338<>"",ROW('2013'!$E$2:$E$338)-ROW('2013'!$E$2:$E$338)+1,ROWS(B$1:B1)),""))
View 14 Replies
ADVERTISEMENT
May 12, 2014
I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
View 4 Replies
View Related
Dec 28, 2011
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
View 5 Replies
View Related
Nov 26, 2009
On the attached example there is a button called 'unsettled hedges', click this and it loads a userform. In this example, there are 10 rows of data that want a value entering into the 'returns' textboxes.
If you try to enter a value into all of the 'returns' textboxes and click the 'settle hedges' button then all the values from the textboxes should against the relevant row in column L of the 'unsettled hedges' worksheet and then each of these rows should be copied to the next available row in the 'settled hedges' worksheet.
This isn't happening though, instead, it is copying across every other row from the 'unsettled hedges' worksheet, leaving half of the rows still on the unsettled hedges worksheet.
View 2 Replies
View Related
Aug 12, 2008
I need to be able to auto fill, on sheet two,cell references for every 26th entry. Below is an example of what I would like it to be able to do with the drag, auto fill.
View 8 Replies
View Related
Jul 16, 2014
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
View 1 Replies
View Related
Jun 29, 2008
I have a workbook for a category of academic journals. It contains the journal name in the first column, the year it was published in the second and various bibliometric stats in the next twelve as follows: ....
View 9 Replies
View Related
Dec 17, 2013
I've attached a workbook with a description of what I'm trying to do. I'm not sure if it can be done with formulas or if it will require VBA. I can move it to the VBA section if the latter terms out to be the case. it's not that I'm opposed to copying code, I just like to know how it works with enough depth that I can fix/modify it, and I can't do that with VBA).
View 6 Replies
View Related
Jul 16, 2009
I'm currently working on a report and what I'm trying to do is get a Row of information to pull into 4 rows. My current formula looks like this:
=INDIRECT("'Paste SAP'!H"&IF(MOD(ROW()-1,4)=1,ROUNDDOWN(((ROW())+3/4),0)," "),1)
I change the bolded number to correspond to which row (1,2,3,0) but it's not functioning. I've done it with other but for some reason this one doesn't work. I've attached the template so you can see what it looks like. The problem is with the SAP Tab and the info from the Paste SAP tab.
View 2 Replies
View Related
Dec 21, 2012
I am trying to get excel to autocount starting in a7 and ending at a600, skipping 3 rows in between, and only gng from 1-10 then repeating and inserting a page break after the 10th line i have put an example in.
SAMPLE1.xlsx
View 11 Replies
View Related
Oct 30, 2013
I am working on an inventory sheet. Its probably only complex due to the amount of data. There are several hundred locations we service and these are in a single column, and then there is the equipment we use that is several hundred columns.
Each location uses 3 rows and the 1st needs the formula to equal 2 rows beneath it. i.e. cell E28 has the formula =E30. I basically need to fill down, but only put that formula into every 3rd row, as the two rows beneath this row need to stay blank, or show number i manually put into either.
The second part of this, is there a way once that is done, to just select those cells, and fill to the right all the way? Again, i do not need to fill the two rows under that one.
I have attached part of the sheet .....
The green cells are the ones that need the formula, and those will be copied down, and right. the others need to be left alone. I have been sitting here for an hour manually entering in the =XXX for each cell.
View 4 Replies
View Related
Jan 30, 2014
I'm trying to total up some rows that are put in a repetitive order repeat. I can do this, but the method I'm using is highly inefficient. Is there some slick equation I don't know about?
Example below:
Worker 1 - Double Time - each of these has 200 columns for different job codes
Worker 1 - Over Time
Worker 1 - Regular Time
Worker 2 - Double Time
Worker 2 - Over Time
Worker 2 - Regular Time
Worker 3 - Double Time
Worker 3 - Over Time
Worker 3 - Regular Time
...and so on to 1000 workers
Total Double Time - for each job code
Total Over Time
Total Regular Time
View 1 Replies
View Related
Aug 27, 2007
i am useing colmn a only for input,
starting from cell A5 to the end of A,
so as i input data starting form cell A5 the active cell automaticly moves to the next row A6, so i could input another number but after ten rows it should skip an extra blank row and start this all again
but thats not the only prob, starting from cell B5 should count every ten rows and give it a number starting from 1,2,3,4... and no number should be on the blank row.
a b
5 223 1
6 234 1
7 987 1
8 787 1
9 976 1
10 324 1
11 345 1
12 455 1
13 247 1
14 685 1
15 ......................
View 9 Replies
View Related
Feb 29, 2008
im trying to copy a data--paste special--transpose--
i want this data to be copied in a manner, tht the 1st entry skips 3 rows and then comes the next value??
View 9 Replies
View Related
Jul 13, 2009
i have table in the left side and i want to get the results in the table in the right side skipping blanks row price with taking in consideration blanks are formula contain ""
[IMG][/IMG]
View 9 Replies
View Related
Nov 9, 2006
Attached is a sample spreadsheet. Please look at the spreadsheet will reading this post, as it would be rather confusing otherwise.
From this spreadsheet, I need to create two new spreadsheets (I assume one at a time is best). One (S1) will contain data from rows where Column C = "Regular Hours". The other (S2) will contain data from rows where Column C <> "Regular Hours".
The format of S1 (3 columns) is: Payroll # (Column B), code for the type of hour (this code I will need to write into the VBA code), the number of hours (D:G,Q:T,AD:AG), and the constant "N".
The format of S2 (4 columns) is: Payroll # (Column B), code for the type of expense (this code I will need to write into the VBA code), the dollar amount (I:P,V:AC, AI:AP), the project (Column C), and the constant "N".
The trick is each row on the new spreadsheets can only contain one hour/expense (otherwise this would be easy!). So, I need to loop to copy/paste the right cells, and need to skip over blanks, and need to end at column AP, then continue to the next appropriate row. As well, columns H, U, and AH are not used on the new sheets. I have another spreadsheet slightly similar to this with looping, but it is a delete type loop, not a creation one, and it doesn't have to have seperate rows like this does. I know this one is a bit big, but I know someone out there can figure out some looping code that can handle this.
View 9 Replies
View Related
Oct 21, 2013
My merged cells are not purely aesthetical - each merged cell (covering 6 rows in total, beginning at A3:A8) has colums and rows to the right for other details/formulae relating to that specific merged cell (which contains a customer account name).
The list I'm trying to reference is on a seperate tab - 'Projects' starting in cell A4. This list will have project names added to it, so I need my merged cells to reference the list, and subsequently pulling through all new additions to the list, rather than having to add them on a 2nd time.
View 8 Replies
View Related
Apr 15, 2009
My sub is supposed to take the values from Sheet1 and paste them into 16x10 tables on Sheet2. However, for some odd reason, the loop is skipping some rows. If you take a look, there are 2184 values in Sheet1, but only 1368 end up in Sheet2...SKUs such as 518166,518167,510573,513746,513752 are just being skipped for no reason.
View 2 Replies
View Related
Oct 30, 2006
Is there an easy way to revise the following code so that it imports ONLY the non-blank rows from files?
Sub ImportFiles()
Dim i As Integer, files As String, text As String
ChDir _
"C:Test"
Cells.Select
Range("A1").Activate
Selection.ClearContents
'Selection.QueryTable.Delete
Application. ScreenUpdating = False
i = 0
files = Dir("*.csv")
Do While files <> ""
Open files For Input As 1
Do While Not EOF(1)
i = i + 1
Line Input #1, text
Worksheets(1).Cells(i, 1) = text
Loop
View 9 Replies
View Related
May 27, 2014
I have command button in Sheet 3 (from where I will be running the macro) and I need a macro which will delete all the blank columns and rows in the data containing in Sheet 1.
View 2 Replies
View Related
Jun 15, 2007
I am trying to delete rows that do not have values in column B. This is a quote form that takes up over 1000 rows but not all are needed (ie lines that do not have any value in B "qty"). Is there a way do have excel delete these without doing it manually?
View 3 Replies
View Related
Oct 7, 2008
I have data input in a particular column, say Column C.
I would like to have a macro that deletes the rows where the corresponding entry in Column C are blank.
In addition, there are multiple worksheets with the same data format in the same spreadsheet, but the number of row varies. It would be great if the macro can delete the row with blank cell in that column across all worksheet.
View 10 Replies
View Related
Feb 11, 2010
I'm trying to delete rows which has columns that appears to be blank. Below code worked fine but it took about 15 min for the macro to work through the spreadsheet.
View 2 Replies
View Related
Mar 21, 2007
I have a spreadsheet containing hours logged against a specific project, all of which have a 'Week Beginning' date.
I would like a macro to insert two rows above a change of date (in the D column). How best should this be done?
It's basically a formatting macro I'm after, to group records by 'Week Beginning' date.
I've spotted a few similar requests, but none of them appeared to include the function to check for a change in date, from the cell above.
View 9 Replies
View Related
Dec 13, 2007
What I have are invoices that span an entire month that I need to convert into a format for the billing department. The columns I have right now are: "Invoice Number"Cost CenterAccountFundType of Service" Date of Completion"Unit PriceQty Total Price
I need to generate a file with the headings as: Cost CenterFund"Date of Completion"Account Total Price. Since Total Price only appears on the rows that have the final information I need, I want to have a script that grabs every line in which the Total Price column has data, generate a file of the summary lines, then throw that through another macro to reformat it into the final format, or just do it in one step. In summary I need to grab rows where Column I is not empty, whats the best way to do this?
View 3 Replies
View Related
Jan 13, 2008
What I have is a sheet where the cells in range A9:A3508 have a formula that evaluates to blank if any of a variety of conditions are not met (date falls outside desired range, does not meet filter criteria, etc.) and a number if these conditions are met. What I am looking for is for all rows in that range where A is blank to be hidden (not deleted), and for this to automatically update every time A changes (meaning that if A goes from blank to a number, that I will need that row to become unhidden again). I have considered just recording the macro and calling the function with a button, but as this is for external users, some of whom may be unfamiliar with Excel, I would rather keep it as clean, simple, and automatic as possible.
View 5 Replies
View Related
Mar 11, 2014
I have a macro which opens a csv file, moves a few columns about then pastes the data into a master workbook. The problem I have is that even though there is only around 1,500 rows of data on the csv file, the sheet on the master workbook is showing up to row 1,048,576 after the data has been pasted in! This is causing issues as I use this data later on in the macro and there are a couple of 'find and replace' commands that take an age to run because of the number of blank rows.
Code:
Workbooks.Open Filename:= _
"L:PVDDBB15." & Format$(Sheet1.Range("G5"), "ddmmyy") & ".csv"
Columns("AB:AB").Select
[Code].....
View 7 Replies
View Related
Feb 9, 2013
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
View 1 Replies
View Related
Feb 15, 2014
I have been using this macro to separate lots of rows with a certain amount of blank rows.
Example:
Code:
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
[Code] .......
I trying to change it so that, instead of inserting how ever many blank rows, it just copys the row above.
Example (Row A to C)
I have been messing around with macro recorder and i could select each row and paste it into the blank rows, but from researching on the internet selecting and pasting data seems to be a waste of resources (ram?) and i will be running this on a couple hundred rows (lots of data). Also, i would like to keep the functionality so that i can still choose how many times it copys/inserts each individual row from a input box.
I eventually want the data to end up like it is shown in the table but i am takin it one step at time because i want to understand what the code is doing.
View 1 Replies
View Related
Jun 26, 2014
i have this code which inserts blank rows in alternate rows,
Code:
Sub insertrow()
' insertrow Macro
Application.ScreenUpdating = True
Dim count As Integer
Dim X As Integer
For count = 1 To 20
If activecell.Value "" Then
activecell.Offset(1, 0).Select
[code].....
what changes should i make in this code to insert rows only when ther are now blank rows. So first time i run, blank rows are already there, and when i update some data at the bottom and re-run it inserts blank rows again.
View 3 Replies
View Related
