I have two spreadsheets. Spreadsheet B reads from the master. No entries are made on spreadsheet B, it is visual only. Changes are made only on the master. I want to create a Macro where all blank lines are filtered out automatically on spreadsheet B, in effect only lines with values are displayed.
Once a new task is created, the details are typed into the master spreadsheet and spreadsheet B, located in another building, is automatically updated to display the details. Once the task is completed which can be seconds, minutes or hours later, an "x" is placed in column A of the master. I have formulated spreadsheet B is "" all rows where "x" is listed in the master. Towards the end of the day however, I can have 50 visable entries on spreadsheet B spread over 500 rows.
I want spreadsheet B to display only active tasks from the top of the page down. I currently have those that read the spreadsheet filter out blank rows. Because tasks can be updated at a rate of 10 - 15 per minute with new tasks and the completion of old, I want to eliminate the need for our employees to have to filter themselves.
I have a list of about 200 companies in column a. Columns B, C, D, E, etc. list revenues for 2005, 2006, 2007, etc. The problem is not all of the years have values. Is there a way to filter out the companies that have a blank cell for any of the years? For example, if company 1 has a blank in 2007 can I filter it out, even if all of the rest of the revenues are filled in?
I have a worksheet which shows multiple tests and whether they are completed or not[code]Test 1 Person1 Test 2 Person2 Test 3...Person12. I want to search for a specific test, e.g. pH which could be in any of the "Test" columns but only return it if it is not completed. The "Person" columns will be blank if no one completed the test.
I set up the Advanced Filter criteria with all the appropriately labeled columns and for the Person columns, I put ="" but it is returning all rows which have pH in them whether they are completed or not. What should I be putting in the Person columns of the criteria to only return those rows which have the pH test but it is not completed. I attached an example of the criteria range and the filter window to help clarify (the full spreadsheet is huge)
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
How can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23 Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
I have a large array of data which I'm filtering out and copying to a new spreadsheet using an advanced filter. I have 2 filter criteria, one works... the other doesn't.
The second criteria is supposed to filter out rows that have blank cells in column C or D. I tried various things:
a. at first I tried following: Column header: Name |Surname Criteria: <>"" |<>""
b. since it didn't work I tried that Column header: Name |Surname Criteria: =<>""""" |=<>"""""
I also tried <>0 and <>null with absolutely no joy.
c. once I figured out that doesn't work either, I tried: Column header: (empty) Criteria: =OR(NOT(ISBLANK(C2)),NOT(ISBLANK(D2)))
now, the last one did have some effect, most of the empty-celled rows have been filtered out. However the filter persistently picks up ONE row that has blanks in column C2 and D2. I checked the data to check if the cell is really blank and it's as blank as it gets.
Obviously, I need help. It's been 3 days and I still can't figure out what's wrong with my filter. Is there a way of fool proofing the criteria, so that it will filter out all cells that are or appear to blank?
I cannot work out the syntax to run an advanced filter for all Non-Blank Columns! I have attached an example sheet, and you can see my syntax in cell D4. I am looking to bring through a list of all line that are both Status= "NOT QUOTED YET" and Project Name= NOT Blank. Unfortunatley, the Status Row will be set as NOT QUOTED YET when there is no project name enetered, hence the problem.
I am struggling to get Excel Advanced Filter to work on a database, where not every field is populated. The problem I am experiencing is that Excel is treating a blank field as a criteria. When I run the Advanced Filter, any fields in the database that are not populated are filtered out of the results.
To try and illustrate the problem with a simple example: I have a database with two filter criteria: TYPE and CATEGORY. If I filter on type, it returns 100 results [correct]. If I filter on category, it returns 50 results, which is incorrect, as it should be 70. The reason it is returning an incorrect result is because it is treating the unpopulated/blank fields under TYPE as a criteria.
I have set up my criteria table with the below Boolean formulas: if the result of the formula is true (ie if it is blank), it returns "" and if it is false, it returns the selection.
Frustratingly, Excel Advanced Filter still treats the result of this formula as a criteria. So when I run the procedure, with only CATEGORY selected, all blank fields under the TYPE are filtered out of the results. The only way to get the Advanced Filter to produce the correct result is to delete the formula under TYPE from the criteria table - which of course defeats the object!
My intention is to incorporate the Advanced Filter into a VBA procedure, where the user will be able to select criteria under TYPE and CATEGORY from drop down menus.
I can think of only three possible solutions to the problem:
1. Ensure there are no blanks in the database (not really practical as it is not always possible to apply type or category) 2. Tweak the formula so that the Advanced Filter will not treat "" as a criteria 3. Write a VBA procedure, where the selections are written each time to the criteria table when the code is run (hoping to avoid this!)
I have some code that filters on blank cells in column D. What I am stuck on, as my knowledge is fairly limited, is how to activate the first blank cell in that column when the filter is on, as the cell # will differ everyday.
Is there simple line of code that will go to this cell?
I have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.
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:
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
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
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.
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.
I have been using this macro to separate lots of rows with a certain amount of blank rows.
Code: Sub test() Dim j As Long, r As Range j = InputBox("type the number of rows to be insered") Set r = Range("A2")
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.
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
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.
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.
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
I have the code below that clears colums B-J and L-N in whatever row you activate a cell in. When a row gets cleared, how do I shift all the other rows up to eliminate blank rows in between the data real-time? I've deactivated the cut function already since this messes up references in the worksheet, so it would have to only use copy, paste, and clearcontents functions. Also, I don't want to DELETE any rows, just essentially shift the blank rows to the bottom. So when a row is cleared, all others shift up and the blank one goes to the end of the data that is available for data entry, so rows 17 to 116 are always available. But it must check to make sure that B-J and L-N are ALL blank, otherwise some wanted rows with one piece of information might get cleared. So at any given time, my range will always go from row 17 to 116. This is Excel 2007. Here is the code I have to clear rows that can be built upon.
First row of data being a category, say 1-5. The second row is data I want the AVERAGE for only if the category is equal to 2 in the column. Looking for formula that uses 'Array of row 1' if = 2 to average of Array row 2???
1 4 3 2 1 5 4 1 2 2
So, filtering to only find the average of 300,500,800 = 533.33.