Selecting Rows In Table Based On Another List?
Jan 30, 2013
I have attached the data in an excel file here. I'd like to select (or highlight) the rows (based on names) in the table on sheet1 based on the list provided in sheet 2. I've been struggling with using a formula for this one. The list on sheet 2 may be longer than the table on sheet 1(since there are entries on the list which might not be present in the table column 2), and the reverse may also be true. I would like to select "entire rows" of names in the table that are there on the list on sheet 2, not just the names column in the table. See the desired result on sheet 3.
View 9 Replies
ADVERTISEMENT
Mar 15, 2014
I have a list of Products in my Excel file, I am trying to put them into categories easily. So what I am asking is there a way to select all rows with a certain key word in them so I can easily paste them into my other excel file?
View 1 Replies
View Related
Jun 4, 2009
I have a worksheet which is created monthly by one of our company's employees which reports results for that months operations and successful audits. These audits are separated into various service lines and on the report which is submitted it takes on the following format:
1|Service Line|Data A|Data B|Etc.
2|___CR_______| Data | Data | Etc.
3|____________| Data | Data | Etc.
4|____________| Data | Data | Etc.
5|____________| Data | Data | Etc.
6|___DD_______| Data | Data | Etc.
7|____________| Data | Data | Etc.
8|___MS_______| Data | Data | Etc.
9|____________| Data | Data | Etc.
What I want to do is use a macro from another sheet that has the effect of: While ServiceLine = "CR" copy the row of that line and move it to my monthly summary workbook, sheet 1. Then when the Service line changes to DD have the macro copy that information to the monthly summary workbook sheet 2, and so on. The problem is, if I test the Service Line column each time it will be an empty string the majority of the time and I'm not sure how to combat that with my If, Do, For, and While statements. As I've thought about it there are two solutions that I can think of: someone knows of a way to test the row only if it has information in it, or if there is a way that I can copy the service line information down until the change in service line so I can test Service Line on each Row.
View 3 Replies
View Related
Mar 19, 2005
I'm sure there is simple code for this but I have excel spreadsheets like the sample attached that are consistant in the types of fields but the only data I want to strip out are the rows where column A has a "G" in the cell, I tried using this
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
but this doesn't work when there is only one row of data. The number of rows with valid data will vary each day. I then tried an if, then statement and a loop but I could never get the loop to stop. I basically want a macro which parses through the worksheet and selects the rows that have that "G" in column A so I can copy them and paste them in another worksheet.
View 8 Replies
View Related
Jul 15, 2013
I have a large set of data regarding the activities going on in particular rooms. The data contains activity name, start time, day, duration, room and size.
I would like to select all rows that contain a cell in the activity name column which contain the words 'VideoConference'.
I have tried to Search, Select All then index the remaining data using =iferror(index(etc. However some activities occur in more than one room and all variables apart from 'Room' are the same. Thus when I have tried to index the room column I get the same room for both rows.
ps the raw data set consists of +28000 rows so I can not simply look through and copy.
View 4 Replies
View Related
Jun 15, 2007
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
View 4 Replies
View Related
Jul 4, 2014
I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------
UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
View 6 Replies
View Related
May 7, 2014
basically i have a macro to filter a lot of data based on peoples names but the people keep changing so I dont want to go into VBA each time to update the name list int he macro.I've created a list of names in a sheet in excel but not sure how to point the macro to filtering by that instead, heres what I have:
VB:
ActiveSheet.Range("$A$3:$H$61").AutoFilter Field:=7, Criteria1:=Array( _
"Name1", "Name2", "Name3", "Name4", Operator:=xlFilterValues
ActiveSheet.PageSetup.PrintArea = ""
[Code]....
I have named the database list for each name and its on a sheet called Team_List
View 4 Replies
View Related
Aug 22, 2007
I have a way of getting a cell value from having a data validation linked to a row of cells containing lookup formulas.. which in turn are looing at a table.
I have attached a small example.
The problem is the spreadsheet is a large one and i was hoping I could shrink the size of this by removing the vlookup formulas by having some kind of index/match/lookup formula inserted directly in the data validation.
I got this to work using nested IF formulas but its not very practicable!
The attached example has data validation in column A that selects the task type. Choosing this then changes the values in the vlookup area.. which then becomes the validation list for cell in column B.
View 9 Replies
View Related
Jun 5, 2009
i want to do is, have a list that picks from a color and then beside it, it will show the possible fruit that is that color for example
Red
apple
cherry
strawberry
OR
Green
watermelon
grapes
The number of fruit will differ by the color so i know i need a counter there, but i am not sure how to list the fruit after i've picked the color
View 6 Replies
View Related
May 26, 2014
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
View 12 Replies
View Related
Aug 19, 2014
I'm creating a new spreadsheet for different fines we have as a football club.
I have a table frozen at the top which looks like this:
Fine 1 Fine 2 Fine 3
Player 1
Player 2
Player 3
Then also I have a list below this with the date and specific fines:
Date Player Fine Amount
(drop down (drop down Entered manually
list of players) list of fines)
I am wanting the amount of the fine to be entered into the table at the top of the spreadsheet automatically based on the 2 drop down list entries. I would also need the table to keep adding the fines together once new entries are made.
View 1 Replies
View Related
Jan 11, 2013
I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:
Column A Column B
Office Employee
Hamilton Emp 1
Hamilton Emp 2
Hamilton Emp 3
Toronto Emp 4
Toronto Emp 5
Toronto Emp 6
Toronto Emp 7
Toronto Emp 8
Waterloo Emp 9
Waterloo Emp 10
This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection
ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.
If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.
Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.
So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).
I would be ok with something like:
Column A Column B
Office Employee
Hamilton Emp 1, Emp2, Emp 3
Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8
Waterloo Emp 9, Emp 10
and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.
View 4 Replies
View Related
Feb 20, 2012
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
View 2 Replies
View Related
Jan 3, 2014
I have four sheets with the same size tables and just need rows to be able to be moved back and forth based on input from a certain cell and just added to the next available blank cell in the desired table. Adding the code from the webpage below works very well but only puts into the worksheet and not into the actual table. Is there any way making it actually input the row into the table itself?
Creating Macro that automatically moves row to another spreadsheet?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "COMPLETE" Then
Target.EntireRow.Copy Destination:=Sheets("Archive"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub
View 1 Replies
View Related
Mar 3, 2013
I'm trying to extract a list of names from an Excel 2010 table based on two criteria, thus:
=IFERROR(INDEX(Database[FullName],AGGREGATE(15,6,ROWS(Database[Age]-ROW('Database'!$A$2))/((Database[Age]>1.8)*(Database[Age]
View 4 Replies
View Related
May 1, 2008
I need to CUT & PASTE records according a cell value. For example:
Columns A2 to AC1000 there are some values. In each rows of AD2:AD1000 the values repeats with the result something like "YES", "NO", "N.A.", "LESS CHANCE". So, I need to shift (to another sheet) only the entire rows with values of "NO" and "N.A."
View 6 Replies
View Related
Aug 14, 2009
I have a need to add or delete rows from a number of known sheet names using a table of variables on another sheet that tell me the start row of the sheet I need to go to and the number of rows I need to either add (ie copy rows and paste / insert these) or delete (delete rows).
There are multiple blocks of data I mey need to amend on each sheet and the values in my table of variables will change on each iteration (ie if I delete rows from the first block on a sheet, the start row for the 2nd block I need to amend will need to be updated in the table of variables before I can edit the 2nd block on that sheet).
I have been able to get the process to work for a single instance (ie one sheet and amendments to the first block of that sheet) but I can't figure out how to create the loop to elegantly move to the next set of variables and repeat the process for the 2nd, 3rd, 4th block etc on the first sheet and then move to the 2nd sheet to repeat the process etc.
Public Sub EditCurrentBlock()
Dim rowcount As Integer
Dim startrow As Integer
Dim endrow As Integer
Dim rowcountBal As Integer
'Dim selSheet As Worksheet (tried to use this to nominate the sheet variable but
' had problems so scrapped it)
'Reconfigure the GP Revenue block.................
View 2 Replies
View Related
Jun 10, 2014
Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.
Please see attached file : Formula.xlsx
View 1 Replies
View Related
Feb 6, 2013
I have two pieces of code that perform virtually identical functions on different sets of data.Basically, the code deletes rows of data based on a selection in a listbox. One code works perfect, the other has an issue.
The Issue:
When I select a single row for deletion, or multiple contiguous rows, everything works fine. It is when multiple Non-Contiguous rows are selected that the problem occurs. I get a "Delete Method of Range Class" error.
The data for the code that doesn't work is in a defined TABLE, the date for the code that does work isn't. So I assume this is where the problem lies.
I converted the data for the code that did not work from a table to a range, and everything works fine. However, I would really prefer that this data remain a table. There is consistently going to be rows added and deleted, and a table will take those adjustments into account, whereas a named range shrinks each time I delete a row, and doesn't grow as I add rows.Here is the code that works:
VB:
'Delete Selected Offers
Else
For r = 0 To ListBox1.ListCount - 1 [code].....
View 6 Replies
View Related
Jan 2, 2014
I'm working in Excel 2010.
I setup a table with a column titled "Type". Each value under column "Type" defines the type of row it is: "Section", "SubSection", "SubSubSection"
I wish to create a set of conditional formatting rules that apply to data rows in this table, each rule controlling how cells within a whole row should be formatted, according to the value found in "Type", for that row.
Example:
Rule1: [@Type]="Section" -> fat red line on top of cell.
Rule2: [@Type]="SubSection" -> thin black line on top of cell.
Rule3: [@Type]="SubSubSection" -> dashed black line on top of cell.
I can't seem to make this work.
How can I leverage the nice column names, and the "@"-this row designator, within a table to create conditional formatting rules that apply to all the cells within a row, in the same way one can refer to in table formulas?
View 2 Replies
View Related
Jun 11, 2008
I can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
View 3 Replies
View Related
Aug 3, 2006
if it is possible to have a selection from a list change the list for another cell?
For example, if I have a drop down list in A1 which is "Apples, bananas, pears", and I select "Apples", is it possible for A2 to have a list of "Green, Red, Yellow", and if I select "Bananas" in A1, for A2 to have a list of "Yellow, Black, Blue"?
View 2 Replies
View Related
Dec 20, 2006
I have a bit of a curly one - I have a sheet with 9,000 records that I need to expand to 24,000 + on a separate sheet records using the following method:
1) Select and Copy entire row 2 from sheet(1)
2) Insert a number of new rows into sheet(2), based on the value of cells(2,8) or cell H2. Column H contains quantity values for each of the 9000 rows which are variable.
3) Repeat for row 3, copying and inserting into sheet(2) based on the value of the cell in H3.
4) loop through all records until complete
I have tried writing the code in VBA but am not even getting close!
View 9 Replies
View Related
Oct 3, 2012
As far as I can tell Conditional Formatting will not work for this problem.
What I need now is some code to colour the rows in the data sheet ("Standards_Data"), so some other code (which is working fine) can then colour data points on a multiple graphs (which will be on the "Graphs" sheet). I've played around with as much code as I can find but nothing has the flexibility that I need or doesn't seem to work at all.
The idea is that users can adjust the colours using the Column A on the "Graphs" sheet to best highlight certain things. Also this list could then be edited as old Standard ID's become redundant and new ones come on line.
So in a nutshell: Colour rows in "Standard_Data" based on value of Standard ID (Column 4) according to the corresponding colour found in the list in the "Graphs" sheet.
I've attached a trimmed down version of the workbook.
View 4 Replies
View Related
Jan 11, 2010
Is it possible to update certain rows only based on the choice selected from a dropdown list
I shall explain this scenario
I have attached a small excel sheet called TESTTHEM which i'm testing
and learning excel simultaneously. On the attached sheet
If i select a value in B4 as BusinessLogic, it should then lookup for relevent rows in MISC worksheet and copy C5:L5 from MISC worksheet and replace
the columns G4:P4
Is it possible to do this in excel?
View 13 Replies
View Related
Dec 30, 2011
I have a data validation cell that has 5 items to be selected, lets say A-E.
I would like a macro that checks and does the following:
A or B selected, hide row 25-34, rows 45- 53
C or D selected, hide row 24-44
E selected, hide rows 34-53
In each case, the macro should check and unhide all rows between 25-53 before doing the above
View 9 Replies
View Related
Dec 8, 2011
I'm creating a pivot table using visual basic. for 1 of the titles in the column ("Organization") I only need to Select "Telford", "Kaiserslautern" & "Morocco" out of several other options. The code I've got at the moment only tells the pivot table not to show the ones I dont want as per below:
Code:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Organization")
.PivotItems("Printers KL").Visible = False
.PivotItems("Printers TE").Visible = False
.PivotItems("Valls").Visible = False
.PivotItems("(blank)").Visible = False
End With
Not all of these options are always in there so the macro crashes when it can find 1. Is it possible to de-selct all options then only select the 1's I want rather than the other way round using visual basic?
View 2 Replies
View Related
Oct 2, 2013
I've made a macro and spend some time making it work across different sizes of data. The last issue I'm having is after the macro sorted out some data, that it will then delete, it has to select the first row and shift select down before deleting it. However, depending on how the data is sorted, the first row could be 9, could be 10 and so on. See below for my code.
Code:
Sub Macro1()
Windows("statistik.xls").Activate
Range("A8").Select
[Code]....
If the first row in my data set is not one of the 3 criteria ("Personligt ejede virksomheder", "Privat", "Reklamebeskyttet"), then the first row # will be 10 instead of 9. If the first two rows are not one of the 3 criteria, then it would be row 11 and so forth. How do I make my macro take this into consideration?
View 4 Replies
View Related
Nov 7, 2013
I have a table like this:
Book, Loaned to, Date
A Tale of 2 Cities, John, 1/1/2010
Oliver Twist, Mary, 2/3/2011
Great Expectations, James, 4/6/2011
Oliver Twist, Greg, 6/2/2010
I want to make it such that, when I input the name of the book, it will tell me the person whom I lent it to last and the date of the transaction. Preferably, this should be done without any assumptions on how the table is ordered. For example:
Book= Oliver Twist
Loaned to= Greg
Date= 6/2/2010
View 7 Replies
View Related
