I have a list 10 of names in Column A. ( On sheet 1 )
I then filter column A to show only three specific names, or four names etc. . .
On Sheet 2, in cell A1, I would like to list the three filtered names from column A on sheet1
e.g. if the three names showing in column A ( Sheet 1) are: john, mary, jane . . . then on sheet 2 cell A1, I would like to see: "john, mary, jane"
{=SUM((C2:C8="black")*(B2:B8="sneakers"))}
I’ve got following formula counting different kind shoes that are black and sneakers and above formula work very well. What I wonder is if you can convert above formula to count these items in a filtered list. Could you use the subtotal-function in any way?
As the subjects states I need to count the unique entries in a filtered range.
View 7 Replies View RelatedI am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies View RelatedI know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
The following sub will look in the file ("FY09 SOF"), in column "A", search for the strings that begin with "2109", "3009", or ends in "-1", and copy the entire row. It will then paste these in the file ("FY09 PR Log Blank").
I have found that in the file ("FY09 SOF"), if things are filtered in any row, it will not copy those necessary items.
The data filter is on row 13 of each sheet. Is there a way of fixing this? (i.e. having the macros select "all" on the filter before copying the sheet? There are 60 sheets so a macros will be necessary.
Sub get_data()
Dim wb As Workbook, wbDest As Workbook
Dim ws As Worksheet, wsDest As Worksheet
Dim lngCalc As Long
Dim FoundCells As Range
Dim FoundCell As Range
Set wb = Workbooks("FY09 SOF")
Set wbDest = Workbooks("FY09 PR Log Blank")
Set wsDest = wbDest.Worksheets("Paste all here, then sort")
With Application
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each ws In wb.Worksheets.............................
It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.
What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.
I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.
I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.
It's a quarterly schedule for utility meter readers, divided up into days across the top, and routes/areas down the side.
There's a space with each route for the employee ID to go, depending on who's doing it.
Down the bottom of the sheet, there's a few empty lines for all the available employees who haven't been allocated to a route.
On the second sheet labelled EMP LIST, is all the employee IDs. It's also got the areas they work- it's for something I want to implement later.
I can put all the employees into a single column list, then use basic DV to give a dropdown menu by each route, to assign an employee to it. This is pretty straight forward. I've also been playing with this in combination with a countif, so that employees already assigned do not appear in the list.
This is about as far as I've managed to get with it.
I need to get the employees who aren't assigned to anything to appear in a list below the routes. I'm fairly sure this is just another countif, but I'm not 100% on the exact formulas.
I also need the list in the dropdown menu to reset for each day of the quarter, i.e. if I assign an employee to a route on the 30th day of the quarter, their ID will disappear from the list for any other route for that day, but will still be there for the other days of the quarter.
So far the only way I've managed to do this is by duplicating the employee list for every day of the quarter, which is going to get very cluttered, and also makes staffing changes difficult. I'm hoping there's an easier way to do this using one data set.
I've added an example of the sheet. It's one week and it's only got about 1/3 of the routes we would actually have on any given day.
scheduledemo.xlsx
I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.
View 1 Replies View RelatedI Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies View RelatedI have a list of names in B8:B200 (unique)
I have another list of names in I7:I15
I want to count how many names in B8:B200 that matches any of the names listed in I7:I15.
I am trying to sum a filtered list and when a filter changes I want the sum to change and it isn't right now. I have attached a TEST workbook in which all the data is values but in my situation all of the data is actually read from a workbook from each agent.... not sure if that matters.
If I filter by District (East or West), how would I get the total to change? I am trying to avoid any vba or macro within this workbook.
I'd like to have a dynamic report that ranks my lists based on an autofiltered list. In other words, I'd like to toggle the criteria that qualifies the lists content, and have excel generate "ranks" based on the values returned. Currently the rankings are based on the overall list, includeing the records that are filtered out.
View 4 Replies View RelatedI am trying to write a macro that will let me loop through an auto-filtered list (column C in example file) . I want to filter for a criteria (in this case a network), copy the filtered data, paste it onto a new workbook and save it. Although I have been able to record a macro (see below) that can do this, I have to copy and paste this macro over and over again for the other networks. I would like to know if there is a macro that can loop through each criteria in an auto-filter list, copy and save the data into new workbooks for me. I've attached a sample workbook that should be helpful in understanding what I'm trying to do.
View 11 Replies View RelatedI need to apply a dynamic filter to my constantly changing list.
Here's an extract of the fields in Sheet "Materials". I need to filter by an of the Type Levels or by the "Index #" fields. The result would be used in another sheet. The return value should be a filtered list from the "Full Material Name" field.
Material IDIndex #Type Level 1Type Level 2Type Level 3Type Level 4Material NameFull Material NameM0000110010Class 0 - Raw MaterialsSection 0 - Metallic MaterialSheet & PlateGalvanized10gSheet & Plate - Galvanized - 10gM0000210010Class 0 - Raw MaterialsSection 0 - Metallic MaterialSheet & PlateGalvanized12gSheet & Plate - Galvanized - 12g
I'm trying to write a macro that goes through every value in a filtered list.
Specifically, I'd like the macro to select the first value in the filter. Perform the operation. Select the second value in the filter. Perform the operation. Select the third value in the filter. Perform the operation. Etc...
I need to perform a lookup on the first row of a filtered list. i.e. If I search for VCP/3301/MP the filter displays all rows containing that part number. The first row after the header row may be say A320. The next time I search for a different code the first row may be at A120. How can I select this first row for my lookup?
View 7 Replies View RelatedI have a macro I'm working on and am requesting your help.
In my filtered data, I have an empty column. For each cell in that column, I'd like to insert text, based on the results of the filter criteria.
I am using VBA only, modifying a huge .txt file into a .xls file. The ranges are dynamic.
I am successful in my results, aside from this issue.
I would like to paste values from a list into a different list - different tab - that is already filtered. When I try to do so, the values will paste into the hidden cells - being filtered -.
Find attached an easy example of my problem. The list I am talking about is around 1,000 lines.
Pasting Example.xlsx
To explain my example, I filter sheet 1 so only item I need are showing - B, D and F in this case - and I would like to paste the value of Alarm 3 from the Sheet 2 to the corresponding line in sheet 1. But it does not really work - line F is not filled - and when I un-filter values are filled into Item C and D.
I would like to know how to create a validation based on a list, but with filtered. I have an employee sheet and another sheet to select the names but only according to a specific job.
View 7 Replies View RelatedI am working on a sheet to check different outlets turnover, margin etc. on a certain product group or department in a certain week.
There are about 50 outlets of which the information comes to me in a raw data file with around 40.000 rows of weekly data. Each week gives qty, turnover, margin.
What I do now is to filter the raw sheet to the product group that I want the info from and then loop through the filtered rows.
I have extracted a report from a web package into Excel - basically a list of documents held on our website. The Cells in Column B would have the word Title or Description, Column C would have the actual title of the document, Column D would be blank on rows with Title in Column B and Column E has a Document ID (DocID)Number. To make this easier to read is it possible to filter Column B using Title and then move the DocID number from Column E to Column D.
I have attached a shortened version of the spreadsheet with the filter applied in Column B already. The real spreadsheet is 1000 lines long and would take quite a while to drag and drop each DocID number from Column E to D. I assume this can only be done with VBA.
I need the VB code to copy just a portion of a filtered list. I have completed the code to sort and filter the list. I'm having a problem determining how to define the region needed. I have searched the forums and found a few helpful threads but nothing specific. Most show selecting all the columns of the filtered list.
My list is in columns A:AA and begins in row 4 (header row). In my test data, there are 5,900+ records and filtered list is approximately 4,900 records. Since I have sorted the data, the portion of the filtered data I need will always begin in cell D5, be columns D:K, and be the visible rows.
I can see that I'm not allowed to delete rows from a list when it's filtered. Do I have any options to avoid this restriction?
View 2 Replies View RelatedI need to identify values which are less than 0 and move those cells to an adjacent cell.
Acct Desc Amt
2100 Acct1 -10
2101 Acct2 10
The -10 cell will move one cell to the right. I have an imported list with gl accounts, descriptions and amounts and want to have the negative values moved to a new column.
How can I find the median in a filtered list of numbers?
I don't see median as a function of subtotal.
I have a workbook that lists various shipment numbers and their contents to several locations. What I am trying to do is, on a different worksheet in the same workbook, list all shipments to one location; having different worksheets for each specific location.
I am sure there must be an extremely simple solution to this, however I am unable to locate it.
Also I would like the separate location worksheets to autoupdate when new shipments are entered on the main worksheet.
In Excel 2003, when I had a filtered list, I could enter text in a column and copy that text "down" to the "visible" rows- and it only copied to those rows. In Excel 2007, when I do a fill down or use the fill handle, it copies the text into the "hidden" rows as well as the visible rows. Is there an option or alternative way to "fill down" in a filtered list to only copy to the visible rows? Auto Merged Post Until 24 Hrs Passes;I did further research elsewhere on the web - it appears to be a known MS bug - here is the text I found that addresses the issue:
If you place your cursor in a cell within column A, before you make the
selection from the dropdown on that column, then the fill handle will behave
correctly. If the cursor is place anywhere outside of the autofiltered range before
making the selection with the dropdown, then you do get the behaviour as
described. It is a bug, MS do know about it. Alternatively, you can choose Insert tab>Table to apply to your data, rather than using Autofilter. The Data table has the same dropdowns, and the autofill feature always works correctly when you make a filtered selection and then fill down.
how to delete only visible rows in a filtered list?
View 9 Replies View Related