I have a workbook with many (25) worksheets which all have the same structure, column headings, etc. but vary as to the number of rows. I would like to search all worksheets in workbook and copy to worksheet "120" only those rows where column C is "120" and column E is "1-00053-".
Ideally, input boxes would be used to enter these criteria so that it can be used for different scenarios in which these values will vary.
I would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.
Create_List.xlsx
I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.
I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.
I then want to count the number of rows (looking at column 1-3) with the following criteria:
Countif column2<= "date in colum4" AND column3< "date in column4"
I have a worksheet that contains 2 Rn managers per 3 Site and 2 TSR managers per 3 Site.
I want to be able to sort these managers by Site into 6 tabs: 3 RN tabs and 3 TSR tabs.
I want to keep the original worksheet as is and copy the appropriate rows to the designated worksheets labeled SACRN SACTSR VALRN VALTSR and so on. The data range is A:M
The columns of value is A (JOB) and B (SITE) and here is the sample of the data
JOB SITE TEAM
RN SITE SAC GASTON SUSAN TEAM SAC BERNAL
RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO
RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE SAC MCINNIS JOHN TEAM SAC SMITH
RN SITE SAC MCINNIS JOHN TEAM SAC LEMON
RN SITE SAC MCINNIS JOHN TEAM SAC JACK
TSR SITE SAC GRECO DAVE M TEAM SAC KEN
TSR SITE SAC GRECO DAVE M TEAM SAC ROGERS
TSR SITE SAC GRECO DAVE M TEAM SAC LOPEZ
TSR SITE SAC SHAVER KELLY TEAM SAC JONES
TSR SITE VAL FRANCIS TEAM SAC YANG
I have a filtered list of data expanded to "all" in each column. One column is "percentile" (25%, 50%, 75%, etc).
Search down the "percentile" column until you encounter 25% as an entry, then sum a number in the same row from a different column with each 25% entry you find.
I attached a spread. On the data tab, I have 2 criteria that I would like to use to move rows to another sheet. Columns U and W. An example would be that all rows that have a coil in Bay "B" and are allocated would go on the "B Allocated" sheet. I would also like this to automatically update when I delete the data in the data sheet and put new data in.
View 4 Replies View RelatedI would like to pull all rows and columns from a table and copy them to a separate sheet. I would like to pull the data if a certain value is present in a particular column. Essentially, it is the same effect of filtering the data but I don't want to have to keep filtering and copying specific cells and pasting them into a different sheet if I need to change the raw data.
Example: I would like to copy all ids, store, and partner information IF the partner is "X". I would like to put this information in Sheet A.
Capture.PNG
I have a spreadsheet with 3023 rows and columns A-L. Here's a small sampling of Column A (with heading "sku" included, copied exactly as it appears in the spreadsheet):
A
sku
3102-0400-100000
3102-0400-200000
3102-0500-100000
3102-0500-200000
3102-0600-100000
3102-0600-200000
3102-0700-100000
3102-0700-200000
I need to delete every row in which column A includes the text "200000".
Although the above sampling shows "200000" appears in every other row, that is not the case in all 3023 rows.
I have multiple sheets with the same format.(all columns have same format) in column "A" i have a value lets say "B1-1".
What I need to do is to copy from all sheets the rows containing in column "A" the same criteria("B1-1") to a new sheet and skip the rows that have a blank cell in Column "A".
I have a spreadsheet which contains data for crime incidents occurring in a month. I have created a worksheet which uses "IF" statements to pull in data that occurred within the last 24 hours only. I would like to create a report that can be printed which has a crime category and lists all the incidents for that category below that occurred during the day. The issue I am having is that all "IF" and "LOOKUP" statements are line for line and I want to be able to skip blanks or where it is not the category in the title.
View 4 Replies View RelatedI have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted.
The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
I have a sheet of data. The data starts in row 4 and can be variable in length. I want to delete rows that do not meet a specific criteria, for example a location, Leeds, which is in column "i" of the table. I have written the below but it seems to delete data from row 1 rather than row 4 and repeat down each row
Code below
Sub deleterows()
Dim i As Integer
Dim intCounter As Integer
Dim strLocation() As String
p = Range(Cells(4, 6), Cells(4, 6).End(xlDown)).count
[Code] ..........
I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?
Name
Start
End
Other data
Verizon
1/10/2010
1/25/2010
AT&T
3/2/2010
4/14/2010
I have a have excel problem Im trying to figure out. I have 2 sheets. Sheet1 and Sheet2. I want to copy into sheet1 cell B2 the contents of sheet2 column C row x iff sheet2 column B row x = 1. There will be only one cell in that column equal to 1, so I will be copying that cells(colB; rowx) adjacent cell (colC;rowx) contents. This is what I have so far but it doesnt work. =IF('Sheet2'!B:B=1,DONTKNOW WHAT TO PUT,""))
View 5 Replies View RelatedI have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do. Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook.
This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.
1. I need a script to retrieve data (member number) from "Search List" worksheet and then to search it in "Members List" worksheet.
2. Once the search result (member number) found, e.g. 00311, it will copy the entire row to the "Only Selected" worksheet.
I have also attached a sample excel for better understanding.
What I want to try and do is move certain worksheets if their name is changed into another workbook. So there are two scenarios:
1. If any worksheets name is changed to begin with "Old", then it is automatically moved to a workbook called "OldPlans"
2. If any worksheets name is changed to begin with "Expired", then it is automatically moved to a workbook called "ExpiredPlans"
When worksheets are moved to new workbook I would like them to be positioned towards the end of the workbook. So I am not sure how to go about this, first would it have to have a Name Change Event or something to trigger off what gets moved?
I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error.
MS VB Script error:
Runtime error '9':
Subscript out of range
Any advise would be greatly appreciated.
Thank you
Code is below..
Sub saveall()
'
'
For Each ws In ActiveWorkbook.Worksheets
ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls"
I = I + 1
Sheets(I).Select
Sheets(I).Move
ActiveWorkbook.SaveAs Filename:= _
ThisFN, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Next ws
End Sub
I need to assign to variable the number of cells in axle "Y", I explain them, I have assigned to the variable "Celda" the number "4" that, celda = 4 where 4 is the number of cells where begin the information that i need to copy,at firts they were separated sheets, and the macro it was working, assigning a letter a the processed row in the column of the left, but now is a one sheet, and I need, this macro find the last cells that the contain a letter in the left and assign the number of cells to the variable "celda" dont the range for example "A33" else "33" and begin toward down, I have this
Workbooks(origen).Activate
Application.ScreenUpdating = False
celda = 4 'where the number 4 will be changed for the number of cells in axle "Y" in this case "33"
celda2 = 4
cdestino = 1
While Workbooks(origen).Worksheets(1).Cells(celda, 2).Value <> ""
dato = Cells(celda, 2).Value
Select Case dato
Case "Aplicación de material"............
I have a spreadsheet that is set out to look similar to a MS Project plan, with dates across the top, users in column A and Assignments in column B. In the corresponding cell that matches the user, date and assignment they will enter the number of hours they anticipate working on the assignment.
I then have a summary sheet that covers all assignments, week ending dates and users. I want to be able to calculate the hours and copy it into the cell in the summary sheet that matches the Day, User and Assignment. I have tried using SUMIF, but this requires the criteria cells to all be in columns, but with this sheet the dates are in a row.
I have built the following code which should look through a data sheet and then work out the instances where it meets the following criterias:
= "Client A"
= "First letter of surname is "a"
The code is as follows:
Public rowcn
Sub compare()
Dim rngTemp As Range
Dim intCounter As Integer
loop_col = "Client A"
data_sheet = "Data"
target_sheet = "Summary"
rowcn = 2
Do.............................
I have the following code that works well on a single worksheet
Sub hide()
For Each rw In ActiveSheet.UsedRange.Rows
If rw. Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
End Sub
However I want it to run on each worksheet in the workbook when I run the macro. I tried this but it seems to loop continuously on the first worksheet. When I press escape the line "next row" is highlighted in yellow by the debug in the VBA Editor.
Sub hide()
Dim ws As Worksheet
Dim rw
For Each ws In ThisWorkbook.Worksheets
For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
'Next ws
End Sub
I need to count the number of occurances that meet two criteria i.e.,
Status needs to be UNSOLD
Size needs to be =>10.5 and also <12.5
Dress
Status
Size
1
UNSOLD
9
2
10.5
3
12.5
4
UNSOLD
12
5
15
How can u use sumifs to meet 2 critera and then sum the one column..
=SUMIFS('[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$M$8:$M$100000,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C$8:$C$100000,B5,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$B8:$B100000,$A$2)
That is the formula i used and it worked, yet when i try to use it for other names (where b5 would be) the numbers skew and do whatever they want..
I just want to sum all of the values in M:M which have B5 or "fiona" in columns C:C and also match the date from B:B with A2.
I believe ive done it right and as i say it does work with the first name in b5 but when i pull the formula down it doesnt work anymore...
Aim - Extract every word after: and before , and put them in the appropriate headings
I have attached my desired results in column B - E. To calculate the answer, the heading of the tile is normally just before the : Meaning the Answer is after the : and before the ,
On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?
View 3 Replies View RelatedI have a spreadsheet that has employee name and average hours worked.. The employee is listed multiple times because each row represents a pay period with number of hours worked. the last column I used to average the hours worked. So what I need is whenever there is a value greater than 30 in column g, I need to copy the row where column G has a value. It can be copied on the same sheet or on another.
View 9 Replies View RelatedI am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26.
Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field.
Here is the formula/function I'm using:
=(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")
I have a list of items (TR Sets) from 1 to 96 in the range B4:B99, with values corresponding to each one (Acid Number) in the range C4:C99. I would like to make a separate list that names each TR Set (using numbers 1-96) that has an Acid Number > 0.1 . Is there a formula to do this?
View 13 Replies View Related