I just came across an error in one of my workbooks where I had duplicate headers on a few columns
So When I used AdvancedFilter to " cut" the data into several workbooks for end users, the data in the first column was pasted 2x and the second columns was ignored altogether.
Headers since changed and that fixed the problem.
I'm just curious why the second column was ignored altogether
Here is a copy of my code and I am Having a problem trying to get it to filter zero balances out. There are positive and negative numbers and I need to post them to a seperate sheet and exclude all zeros.
View 7 Replies View RelatedI have a excel sheet auto generated by a process in which there are 4 columns and 150 Rows. One column has dates data in DD-MMM-YYYY (15-May-2007) format..but the datatype of that cell is not Date. When I click on any date let say I selected 24-Mar-2007 in formula bar instead of displaying 3/24/2007 it displays as it is 24-Mar-2007.
But when i double click on this cell it shift to right alignment and display date format 3/24/2007 in formula bar. I have to create a formula using which I can see only some rows of define date range. Means if i define range in two cells 01-Jan-2007 and 01-Jun-2007. The sheet will display only rows having this criteria. But due to this datatype problem before putting the criteria I have to double click each of the row to convert it to Date. RightClick Format cells option is also not help full in this.
I'm trying to use the advance filter for multiple criteria in one column. It works fine if the criteria is ="*10*" (ie where the cell in the range has 10 in the string) plus other = criteria. What I cannot get to work is where the range doesn't equal 10 in the string. I've tried ="<>*10*" and other variations to no joy.
View 4 Replies View RelatedThis is a sort-of bizarre query I have.
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'm having an issue where advnace filter isnt working and I need to get all of the unique entries.
I don't know if this has anything to do with it but I got some erros saying the numbers were being pu as text so I changed the cell tpe to a custom with 00000000 because the unique number I need to filter is 10 digits long.
As another alternative I would paypal some one a few bux if they can help me just compare two lists and make a report with the number of netries that show up in one list and not the other and vice versa.
I have many large arrays of climate data. I am trying to find an array criteria formula that would filter out those years from a variation around a given year. Like this example, to filter out those years B10 (47.8) plus or minus B12 (3.339).
AB
1YEARTEMP
21870 44.78
3187143.33
4187240.99
5187339.43
6187440.94
7187537.33
8187642.23
9187745.68
10187948.18
11
12Stdev3.339
I need to filter out data which displays only a few categories of information at the same time in the same column. For example, column A: David, Susan and William. After filtering the data, it will show David, Susan and william information. I have tried custom filter, but it only allows 2 conditions. I need to show more than 2 conditions of data. I have tried working out using office assistant guide on Multiple Criteria in one column to no avail. Only shows a single cell.
View 9 Replies View RelatedI bring in data with these same columns A thru J. I do not know how many rows will be comming in. I have create a macro that uses the advanced filter. I have a criteria range set up on sheet 2 with 6 variables, the main data is on sheet 1. This works fine if I use all 6 variables. I wish I could select from a list box which of the 6 or all variables I need(I might select 2,3 or more variables), & have that information populate the criteria range. I only filter in place(no copying), there is only filtering on column B, the label for the criteria range will always be "Name" it will be located on sheet 2 D:1. Is this possible or should I be trying some other function? Here is the macro that uses all 6 criteria. I have searched and could not find what I was looking for.
'add criteria range
Sheets("Sheet2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("D2").Select
ActiveCell.FormulaR1C1 = "e11*"
Range("D3").Select
ActiveCell.FormulaR1C1 = "e12*"
Range("D4").Select.........
I have a folder which has 200 files. I have extracted data from these files based on autofilter criteria. But there are many duplicate records extracted for the criteria. I need only unique records . Below are the codes. Where to I add the criteria for search records:
Sub ExampleSearch()
'Note: This example use the function LastRow
Dim basebook As Workbook
Dim mybook As Workbook
Dim rng As Range
Dim rnum As Long
Dim mnum As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
I recorded a macro to perform an Advanced Filter. I then adapted the range to & LastRow. My question is, can this now be adapted to remove the Select so the sheets are not selected when this is run.
Sheets("CIT Results").Select
Sheets("Open Calls").Range("A1:I" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Open Calls").Range("N5:V8"), CopyToRange:=Range("Q50"), Unique:=False
Moderators, can you please edit the Thread title. It should be "Advance Filter From and To Non Active Sheet"
Is it possible to use an advance filter to detect blank cells? Or can the criteria be set to detect all cells that don't contain a letter e.g. "Y"?
View 3 Replies View RelatedHow do you remove entries from a column such that only unique entires are left?
Say the spreadsheet contains only a single column:
david
kris
tony
hayley
kristy
john
david
test
hello
david
I tried an "advanced filter" with "unique records only" checked. It gets rid of one david, but not 2 (there are originally 3 of them, and we want to leave 1)
This is the result.
david
kris
tony
hayley
kristy
john
david
test
hello
I am trying to copy/paste header data into corresponding rows. A1 and A2 have the header information and need to be placed in the corresponding rows under columns A and B. I have been doing this manually and it is very time consuming since there are roughly 2000 rows. I believe a macro can accomplish this task but unsure how to create it. I was also unable to find any similar threads. I have attached a sample workbook. The original tab has the imported information and the Finished tab has what I would like the data to look like.
View 3 Replies View RelatedTake a look at the attached which shows a quick sample of a set of data and an accompanying criteria box. I'm trying to run an AdvancedFilter on it, but nothing seems to work. When I run the filter, I get zero rows returned. I've tried filtering using the headings in the criteria box and tried it without... but not much luck. I'm guessing the problem has to do with the data itself, and not the criteria box.
View 6 Replies View RelatedI am asking if it is possible to delete filtered rows? With code.
What I mean is after setting a filter, then deleting all shown rows except row 1, (Titles)
I did a search but nobody seams to have asked this yet, so recorded it, but that did not seam to enlighten me much either.
Or is this the wrong approach, should I delete using a loop, using the filtered criteria as to say delete row, or move on to the next row?, this would be far more time consuming as when all can be deleted at once if possible of corse.
I have a large spreadsheet of data, with various columns with different bank names, the amount (column H) each bank holds plus loads of other columns. Some of the banks are duplicated, so in a way I want to do an advanced filter to get rid off the duplicate lines, except that I need the amounts (column H) summed.
View 2 Replies View RelatedI have row of data from cell A2:E2 to A5000:G5000
in Column B I have customer account number, and they should not repeat but some reason some customers repeat more than once,
so out all column A B C D E if there if customer repeats only thing can be same is customer account number
so for example below, account number 12 repeats so I want to only few all account number 12 so I can compare both row and delete the only i need to delete and go back to original
DATE
ACCOUNT#
FIRST_IN
LAST_IN
SOMEVALUE
1/22/2012
12
A
B
2321
[code].....
I have data contained in 4 columns. Column A is name, Column B is ID, Column C is Company, Column D is amount. There are duplicate names in column A and duplicate ID's in column B (ID's have correspending Company Names in Column C). I want to have a list so that this combination shows up only once, and then the amount next to it will be the sum of all.
Example:
DataABC DTom9730-003Company B100 Joe10242-001Company A200 Tom9730-003Company B300 Joe10242-001Company A400 Sam9730-003Company B100 Desired ResultTom9730-003Company B400 Joe10242-001Company A600 Sam9730-003Company B100
I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).
As filter I would like to be used A,B,C columns.
EXAMPLE:
BEFORE
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
AFTER
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
starting on A5, I have to entered 10 digits code. I would like to put a VBA code that after I entered the 10th digit it will jump down automatically to next column (A6) without hitting the ENTER key..and so on...
Then once I click on Save button, it will save it as a CSV file based on what I put in A1. ie: if I put in zzz on A1 it will save it as zzz.csv (pure csv).
Is there a way to make excel advance to next lowest cell automatically once a number of a specifed length (e.g. single digit integers in my case) is entered in a cell? I have large volumes of data to enter--usually integers from 0 to 9--and the extra "enter" keystroke becomes very tedious and increases errors.
View 9 Replies View RelatedExcel Sheet Description:
My Excel sheet is a check list inventory and is arranged in three, two column matrix.
The cells are expecting an integer rating (1-5).
Problem Description:
What I would like to do is enter my integer rating in a cell in a column and then automatically have the cell selection advance once to the right and then down to the next row until all the cells have been filled with there integer ratings.
I would like to do this without having to hit the enter or tab key.
I'm trying to mark up in advance the dates where i'll get paid. I get paid monthly on the 2nd last working weekday of the month. So because we have the 30/11/2009 falling on Monday of next week, my pay day for this month is Friday 28/11/2009.in December it should be the 30/12/2009.
I'm getting all tied up in Networkdays, EOm and weekday function!
Is it possible to use an array without looping through it? I have code that has two loops: One that is just for the array and the other that loops through the data. It would be nice if I could get it down to one loop, but I'm not sure if it's possible. For example, if you have
Dim varArea As Variant
varArea = Array(1,2,4,6,8,12)
For x = LBound(varArea) To UBound(VarArea)
Do While Not C Is Nothing
Code here
Is there a way to advance to the next item in the array without using the loop.
How do i auto advance a text box in a user form to the next question. As long as that question was answered otherwise it doesn't advance and displays a msgbox telling them that something must be entered in that box? All done in vba.
View 2 Replies View RelatedPart of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message.
give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount?
attached is the start of the assesment question.
You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid.
Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received:
• If students pay the course fee less than 7 days prior to the course commencing then they receive no discount.
• If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%.
• If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%.
• If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
Copy of OCL 2010 (3).xlsx‎
I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.
Attached is a sample of that spreadsheet.
I feel as though I have spent enough time searching the previous posts to ask this question.
I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.
COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner
I want to end up with rows that each contain unique info in COLUMN B.