Well we are trying to fetch some data using excel filters. But we are unable to apply filter simultaneously on two excel columns. The problem is explained in the attached sheet. If you have any query/doubt in understanding the whole problem then writ it.
View 5 RepliesI have some columns on which I have a filter, with some columns next to those that have information in them.
What I need to do is filter only columns A-F when apply filter values, but keep columns I-K fixed as A-F change when they are filtered..
The code below restricts users from inputting anything other than an uppercase "X" in a cell within column N. If the user types a lowercase "x", it would automatically convert it to uppercase. If any other value is entered other than "X", a message box will fire.
The code is specific to column N (column 13). However, I would like to apply this to multiple columns, such as columns 13,14,17,20 .....
I have 1000's of rows of data and 8 of the columns are reserved to tag possible defects. All 8 columns pull from the same drop down list of all possible defects. I have 8 columns set aside because a row may contain multiple defects.
What I want to do is look up a single defect (ie "scratches") within all 8 columns. See my attached simplified example. If I filter column B for scratches, the scratched part in row 4 will be ignored. Can I apply a filter across multiple columns?
Filter Example.xlsx
I might use the filter as a selection from a combobox, or I might honestly wish to create 15 separate charts.
I have a spreadsheet. Three columns out of maybe six in immediate interest: Date, Name and score.
In "name" I have about 15 different students. I wish to create a chart that will extract the data for one of them which I would select - on the vertical ("y") axis have the scores (from "score") which range in whole numbers from 1 to 9, and on the horizontal ("x") axis show the dates (from "Date") of each score.
Complicating my thinking is the fact that a date can have more than one score, but seldom more than three.
I would then want to add a trend line through the graph, which, if I recall, is fairly easy to do.
I am wondering if I might at some point need to control the length of history, for though now I am working with about three months and over that period of time no one has more than 18 total scores.
I have a spreadsheet where users will filter it using autofilter. I then have two cells where the val/volume of each account is totalled, this works fine. However I would also like to be able to say which is the most recurring product within the account and how many times it occurs. I have attached my sheet below, I hope this helps. Eg, Account 1 worst product is..... with a volume of...
View 5 Replies View RelatedI am staffing an upcoming event with volunteers and I want to be able to a single volunteer across their various shifts and locations. However, I cannot guarantee that a particular volunteer will always be in the same column on my spreadsheet so AutoFilter doesn't do the trick. I've tried using Advanced Filter options as well, but I either can't seem to get it to work or it won't work with strings.
I've attached a sample of the type of document I'm working in (genericized) so hopefully my below questions will make more sense.
Ideally, I'd like to be able to be able to search/filter for Joe and see rows 2 and 3 appear. Is this possible?
Additionally, I'd like to know who is also on shift together. For example, is there a way to search for Tony and Sarah together and have row 5 appear. I know that I can do this now with filtering column 2 and then 3, but the real document I'm using is over 800 rows long (very big event!) and using progressive filtering would take longer than I'd like.
I am having a problem filtering information within mulitple columns. I do not understand how the advanced search works either, so let me try to explain what is going on. I would post a pic of the table to make it easier to understand if I knew how to.I have thousands of rows in Column A with information, for this scenario a number. Thing is that all these numbers are seperated by many blank rows.
Ex: Row 1 has a number, Rows 2-10 are blank, Row 11 has a number, Rows 12-24 are blank, etc. Over in Column C, for every row that has a number in Column A, there is a corresponding number in Column C followed by Rows of numbers. ex: Row 1 Column A has corresponding information in Rows 1-10 of Column C.If this is clear, understand that this repeats for tens of thousands of lines. When I am looking for a specific number in Column A, I do a filter for just that number.
When that happens, it only brings up the info from that same row in Column C. I need the filter to bring up all ten rows of info in Column C that correlate to the row I filtered in Column A. I know the easiest way is to label all the rows in column A with the same number, but the reason the blanks are in place is so it is easier to read the breaks between the different information.
I have some auto Filters I need to apply to my data,
I have a file with supplier and description columns and i need to apply around 15-20 autofilters and move the corresponding data/ results to a different worksheet (which has the same headers but already has data - so want to move it to the bottom of the list) in the same workbook.
I was wondering If you would guide me on the If statements so that I could create a macro that works?
I have a problem that I'm sure requires the most elementary fix. I have exhausted google on this. All I need is a drop down list for each value field. See picture attached.
View 1 Replies View RelatedI want to use the content of a specific cell in one worksheet to be used to form the filter criteria on a set of data in another worksheet, both sheets are in the same workbook
View 1 Replies View RelatedI have created a post on TeachExcel.com, but haven't received an answer yet:
http://www.teachexcel.com/forum/view...7d85cfb7d#p203. I've attached an example of the spreadsheet.
Setting up all 50 states was not so bad, and I tried a Pivot Table but I have very little experience with them and could not figure out how to show only the nonzero states. So, I looked up how to filter the pivot table and discovered I could just filter the state data without the pivot table.
So, I put all 50 state abbreviations in one column. In the next column I put countif formulas to count how many customers came from each state. (In the meantime, I learned how to use an indirect formula to pull the state ID from the first column and copy it down and have the formula advance for each row... cool!) Then, I filtered the columns on the count twice. First, in descending order; then, without the zeros.
However, the weak side of the filter is that it does not automatically update when a new state is entered. I have to go and manually filter again. So, is there a way to get the filter to update dynamically? I know that a pivot table is dynamic but I have a lot to learn and I can probably browse around and figure out how to show only the nonzero states. Once I learned that, would it dynamically update when a new state is added?
I find it to be a little irritating that I have to remove the autofilter, then reapply it whenever I start entering data into a new column. I want a macro that I can assign to a button so all I need to do is hightlight the range of cells I want to apply the autofilter to, and click the button.
View 2 Replies View RelatedI have a spreadsheet with multiple columns. The first column defines a "route", and the next two list "start" and "end" cities for that route. The fourth column lists the length of each route. There are only a limited number of cities, so the same entries appear in both "start" and "end" several times. I would like to use Autofilter to sort the list for every appearance of a given entry in either "start" or "end". Is there a way to make Autofilter sort mutiple columns simultaneously?
I could achieve the desired end result with Advanced Filter, but I want something with the ease-of-use and immediate update/response of Autofilter. Advanced Filter requires explanation (as well as lots of clicking and typing) whereas Autofilter is self-evident. I also want to avoid VBA Macros as they are not well-understood by the users who will use this spreadsheet (and any VBA Macro will require very specific input to work properly.) Is it possible to do what I want? Or is Advanced Filter / VBA the only way to do it?
I have a very large table and i need to be able to Hide/show specific ranges based on:
Filter +and+ specific cell values in columns
brief example of the table : tablee.png
So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.
There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".
I have productivity data sheet of employees for a month and want to update in a tracker sheet.Every productivity sheet has 5 columns containing numbers.Since its monthly it would be contain 30-31 rows and.I want to copy this data then go to tracker apply filter with respective employee name and paste it there.Is there a way to do this using vba code?
I have prepared vba code to copy paste individual rows from productivity sheet to tracker.But preparing row by row code makes it way too big.Hence i am looking for another solution.
How can i use a single command button to do multiple task in Excel 2010.
First Instance I click -- It filters only With Record "Yes"
Second Instance I click -- It filters only With Record "No"
Third Instance I click -- It removes filter.
How I can apply an IF function for four or more columns. for example: if the value is the same in four or more cells, IF function can type OK, if not something else.
what I am doing is this : =IF(H2=I2=J2=K2=L2,"OK","Not Equal") but I always receive the value"not equal" even when the all cells have the same value. The cells values are found using formula so I did copy and paste value but I received the same result.
Here is my issue...
Column B has a lot of data within different rows
Column A only has Some empty rows, and some rows with content.
The issue is I am having is I am trying to sort the the rows of column A in Alphabetical order, but Iwant to lock the height or merge the rows so that when I sort them I do not end up with empty spaces or them be un-aligned with Column B. This spreadsheet is too long to go and re-format every cell but here is an example:
A ----- B
John
(blank) ----- this column's
(blank) ----- rows are
(blank) ----- merged
David
(blank) ----- this column's
(blank) ----- rows are
(blank) ----- merged
The cells in B are already at the proper height to be alligned with the names.... but if I try to put the names in alphabetical order then nothing will be alligned.
I wish to have one conditional format apply to K and L. Presently it's L only where it works.
=OR(K1="",AND(K1="No",L1<>""),AND(K1="OJ",L1=""))
Why when I change the range from =$L:$L to =$K:$L does it not apply to both?
I have a conditional formatting issue, using VBA.
My conditional formatting code works fine for one column; however, when I try to apply the conditional formatting to two columns, the code only applies to the first column listed. Additionally, I have two sets of "rules" that apply to the same column. One of the "rules" includes two columns. The other rule applies to only one column. I'm not sure if this is my issue, or whether I'm trying to apply the same code to two different columns.
My code is below.
Code that applies to column "M" only...
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
View 9 Replies View RelatedWhy can't I apply text functions on strings into the file? For example: can't apply "Text To Columns" delimited by space on "Status Entry Date" column.
View 3 Replies View RelatedIn the attached file, I need four different passwords for Columns D, E, F, & G Respectively.
I had gone though below link and partially I could able to achieve what I need.
[URL]
However, the problem is at the end of this process we have protected the worksheet as well. This is not feasible for the process what I am working on.
how to apply different password without protecting worksheet.
I have to create autofilters dynamically on x columns based on the selection I make. I created this code but is not working.
LastColumn = Cells. Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Range(Cells(17, 1), Cells(17, LastColumn)).Select
With Selection
.AutoFilter = True
End With
I have 5 columns with an auto filter applied using the code below. I also have a form button in each column to reset the filter. What I would like is to have the form button turn "red' when the filter is applied and return to the orginal color when the filter is removed.
If it takes something other than a "form" button that is fine. I am looking for 2 "buttons" one to apply the filter, the other to alert you that the filter has been applied and to reset the filter when clicked.
I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB:
Sub Macro1()
'
' Macro1 Macro
'
Range("B10").Select
Cells.Find(What:="as % of Revenue").Activate
Range("B20").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/R10C"
Range("B21").Select
End Sub
The following code filters my sheet to show only values less than 0 in column T, (column 20).
View 5 Replies View RelatedHow do I filter multiple columns of information with the use of one dropdown arrow?
see the attached picture (jpg.) for a visual representation of exactly what I am trying to achieve. Excel Filtering.jpg
I have also attached an Excel Filtering Example spreadsheet for use in explaining how to create this type of stacking filter. Excel Filtering Example.xlsx