Move Rows, Meeting Conditions In Column, To Another Workbook
Sep 17, 2009
I'm working with transcribed comments from patient satisfaction surveys. The goal is to produce a single Excel file that contains all of the positive comments from source files where physicians are mentioned by name. Our vendor notes this by preceding any person's name with an asterisk (e.g., *Dr. Smith or *Doctor Smith"). I've attached a sample source file (Ambulatory.xls), so you can see what I'm starting with (I've deleted the identifying data in columns B, C, and D).
I'm looking for the appropriate VBA code to cut and paste the row in one file (in this case, Ambulatory.xls) to a separate Excel file (Weekly Physician Comment Report.xls) IF the cell contents in column G of that row contain either of the following text pieces:
*Dr
*Doctor
Each week's file will obviously have an unknown number of total rows - and will contain lots of positive comments that DON'T mention a physician by name. In the file I've attached here, for example, there is only one such comment (highlighted in yellow). I simply want to cut and paste the rows with physician names into the new file. If you can provide me with the code to do this for this example file, I can repeat the process for my other files (there are 6 source files in all).
View 6 Replies
ADVERTISEMENT
Aug 9, 2007
I have been trying to work on this thing for like 3hours and i could not make even a single line since I dont get what loops I should make and how I should do it. Could someone suggest me how to do this.
1. check the date.Calculate two weeks backwards.
2. Check for id no which is scanned more than once in that two weeks from the current date.
3. Check for type of ppe if its different then leave it if they are same and exceeds 3 of the same type it should BOLD the badge id.
Have attached the sample file.
View 5 Replies
View Related
Feb 2, 2010
Broken off from:Delete Rows Meeting Criteria
Thats for that, the sheet in the workbook is called Ticket Handling and if i try to change the name in the VBA code it will not accept this.
It Displays the following error:
Compile Error:
Expected: End of Statment
View 8 Replies
View Related
Jun 4, 2009
I have a large number of user inputs which is linked to a data sheet. In this sheet are rows to be kept and rows to be deleted. See attached example...
I need code to delete all other similar rows than the one with a number (1) in the field Complete. There can only be a number (1) in the field Complete in one of the rows.
But if there is no number (1) in the field Complete in any of the rows, no rows should be deleted.
The data sheet is to be transferred to Access afterwards.
View 3 Replies
View Related
Jun 6, 2008
I Have workbook-1 with 5 cells and 10 rows and in 5 th (E) cell i have value of 1 or 0 .
i want to selectively copy entire rows which has value = 1 in cell(E) and paste these rows into 2 nd row(A2 cell) of workbook-2 using Macro
View 5 Replies
View Related
Aug 11, 2008
I have searched for this topic, and I have read http://www.ozgrid.com/VBA/row-delete-criteria.htm, I don't really understand it though. I think what I want is simple, I want to search column A for cells containing the word " Total" - if any are found I then want to delete the corresponding rows.
View 2 Replies
View Related
Aug 22, 2008
In the included sheet I have 5 groups of data with five categories in column A. Current, Plan, Plan Var, Prior, Prior Var. I have included the button “Show Options” that opens the userform I created and gives 5 options. What I want is the user to be able to select any number of these options and then upon “ok” the rows in the sheet that weren’t selected are automatically hidden. If the procedure is completed again and a differen set of options is selected I want it to unhide any hidden ones that were selected and hide any that werent selected.
So if just current is selected the sheet will show 5 rows of current and nothing else. If current and prior are selected it will show current prior current prior current prior... etc.
I have some hide code that I created in the file as well.
Sub NotCurrentHide() ...
View 5 Replies
View Related
Oct 26, 2007
A friend is trying to change an entire row's color based on a specific cell's value in that row. He cannot use conditional formatting. This is the code he's tried, to no avail:
Sub temp()
totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If Cells(Row, 25).Value = 4 Then
Rows(Row).Select
Selection.Font.ColorIndex = 3
End If
Next Row
End Sub
View 2 Replies
View Related
Apr 17, 2008
I have a large file of invoices, and some have been paid. The problem is, sometimes it is not paid against the correct invoice, so I have a lot of credit and debit that should offset each other. I want to go through the sheet and highlight all the rows that has a matching ID and an a 0 value when total amount column was added up to another row. I would like it so that it highlights a different color for every pair of offsetting amount, but the same color highlight is fine too.
View 2 Replies
View Related
May 20, 2007
I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year.
I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color.
Next, I fill in each day with an employee name.
Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this:
Jones:
Friday 4 (total number of days jones is in a blue box)
Saturday 5 (...on a green box...and so on...)
Sunday 3
Holiday 2
Monday-Thursday 50
For each employee name. Sounds easy, right? I can't get it to work!
View 14 Replies
View Related
Nov 30, 2006
I have two sheets. Watchbill and Roster. The Roster contains a list of names that are randomly generated to the watchbill sheet into more than 60 seperate cells. My problem is the names are not being distributed fairly. If I had 20 names and 20 cells, it only uses say 12 of them and repeats 8 names, thus leaving 8 persons out of the generation completely. I did not know how to fix this so I used a system to identify when a name was repeating in the series of cells by displaying Repeat in the cell beneath it. I then had the names that were not placed on the watchbill (the other 8 not used) highlight themselves in blue on the roster sheet so I could manually insert them on a third sheet into a cell set to override the repeating name on the watchbill. This was my back door system to solve my repeating names problem. However this takes forever. I want some vba that will identify the cell with the "repeat" underneath it and automatically recalculate the cell until it doesn't repeat. I only want the cell recalculated, not the sheet. This code would have to do this for about 70 cells making sure no names repeat at all, unless there isn't enough personnel to fill the positions, then it would allow the names to repeat. Can that be done and what would the code be?
View 14 Replies
View Related
May 7, 2007
We are trying to list only certain cells that have a specific conditional format- this is so we can copy them to another sheet
A filter does not really work for us since we have over 5 columns to search on and over 800 records
This is how excel says to find cells that have a conditional formatting (for example the scores are red because the student scored between 350 and 370: I tried it and it doesn't work--but maybe that is not how it is suppose to work?
To find cells that have conditional formatting settings identical to the settings of a specific cell, click the specific cell.
On the Edit menu, click Go To.
Click Special.
Click Conditional formats.
Do one of the following:
To find cells with any conditional formatting, click All below Data validation.
To find cells with identical conditional formats, click Same below Data validation.
View 4 Replies
View Related
Dec 15, 2007
I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet to help.
The suggestion of an Index with a Match Array worked but the problem is that my spreadsheet has over 5000 rows and my data has to meet three criteria = 15000 cells to cross reference. And I need to set up a spreadsheet that would do about 300 arrays. So obviously my excel is soooo slow I can't even get 10% complete.
Any other suggestions? I've read (from Dave I think) that database is a powerful alternative but have not been able to find any info on this and it's not an Excel function. For your reference I'm attaching a demo spreadsheet of what I'm trying to do with the two Index + Match arrays that work for small files, not mine.
I looked into Pivot Tables but it can't handle all the row and column headings.
View 4 Replies
View Related
Apr 25, 2008
Im trying to copy data that meets a certain criteria from a list on one sheet to another. The problem with the code i've written is that i can't get the loop to work. Once it finds one example it copies it but then stops. My code is as follows:
Private Sub Find_Ammendments_Click_Click()
Dim RowCounter As Integer, RowCounter2 As Integer, RowRange As String
RowCounter = 1
Sheets("PINTS & BOTTLES").Select
Range("Start_pb").Select
While ActiveCell.Offset(RowCounter, 0) <> ""
RowRange = ActiveCell.Offset(RowCounter, 0).Address & ":" & ActiveCell.Offset(RowCounter, 8).Address
If ActiveCell.Offset(RowCounter, 5) >= ActiveCell.Offset(RowCounter, 10) Then......................
View 3 Replies
View Related
May 14, 2008
This below code i find out from this form Skip the row find next
but i need it i have column fom c to M
if the cell k valur or j value = 0 or empty skip the enitre row go next row ifthe kvalue <> 0 or <> empty
do something
the below code where do i moify .
Set c = .Find(MyInput, LookIn:=xlValues, SearchOrder:=xlRows)
If Not c Is Nothing Then
Count = Count + 1: ff = c.Address
k = c.Row
Redim Preserve wsName(1 To Count): wsName(Count) = ws.Name
Redim Preserve rng(1 To Count): rng(Count) = c.Address(0, 0)
Do
View 3 Replies
View Related
Apr 14, 2008
I have an original workbook. Also I have saved a "copy of the original Workbook". So the sheets and other headers are same. Now i want that If i delete a row in Original Workbook then the row should go out from origianl workbook and copied to "Copy of Orinal Workbook" in the same sheet from the the row is being deleted. Suppose row 7 of sheet1 is deleted, then the data of the row 7 from the original WB should go to row 2 of Copy of origianl WB (Being the first empty row. Similary if any row is deleted from sheet2 of Original Wb, the same should go to first empty row of Sheet 2 of Copy of Original WB. By this I want to keep a record of those rows which are deleted from the original Wb for future reference.
View 2 Replies
View Related
Apr 23, 2009
refer to attached worksheet.
I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.
View 9 Replies
View Related
Mar 14, 2008
I'm trying to populate a dropdown Combo Box by using conditions.
In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.
I've been trying to use worksheetfuntion.Index.
View 4 Replies
View Related
Mar 2, 2006
I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):
Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub
As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).
View 9 Replies
View Related
Dec 16, 2007
I am working on the attached price proposal and need my user to be able to enter a number into column E indicating that that row should be printed on the quote. The 'quote' can be new sheet or another area of current sheet that is formatted to look like letterhead.
View 4 Replies
View Related
Jul 23, 2007
We have a client that provided us with a text file that we imported into
Excel. Rather than have the entire record in one row, they have the record
in 4 rows.
What I need a macro or something to do is move award 2 award 3 and award 4 into the same row and after doing this delete the other rows.
Below is an idea how this looks.
1JohnSmithstreetcitystateZIPaward 1
2JohnSmithstreetcitystateZIPaward 2
3JohnSmithstreetcitystateZIPaward 3
4JohnSmithstreetcitystateZIPaward 4
5BillJonesstreet2city2state2ZIP2award 1
6BillJonesstreet2city2state2ZIP2award 2
7BillJonesstreet2city2state2ZIP2award 3
8BillJonesstreet2city2state2ZIP2award 4
What we need for our data to work is:
1JohnSmithstreetcitystateZIPaward 1award 2award 3award 4
2BillJonesstreet2city2state2ZIP2award 1award 2award 3award 4
View 9 Replies
View Related
Sep 21, 2009
This must be a simple fix. But I can't seem to get it. I need to test the data of each cell in column B against each other cell in column B. If the data is equal then move it under the first cell of the test.
1
2
3
2
2
3
4
Group the 2's and 3's in order.
Finally, then create borders around each group of numbers.
View 9 Replies
View Related
Nov 4, 2006
I have sheet1 with four columns. Team, Issue, Discussion and Recommendation. Discussion and Recommendation headings were placed in column B along with the data in the file I was sent. I need to get it back into table format to eventually load into Access. What I'm trying to do is to loop reading down column B. When I find the word “Discussion” (exact format) I need to drop down one row below the word, cut whatever is in the row, move one column to the right, Column C, and two rows up and paste. For example, cell B3 has “Discussion”. I need to drop to B4, cut whatever is in the cell and move to C2 and paste. I also need the same to happen for Recommendation. Find the word, drop down one row, cut whatever is in the cell, move two columns to the right and up four rows. I would like to delete the rows I moved data from, but will do that by hand if it’s too hard to do in a macro.
An example of my data:
TeamText
Design4. Reuse existing code to extent possible.
DesignDiscussion
DesignNo discussion
DesignRecommendation
DesignEstablish a baseline and work to the plan.
Design5. Delete old code
DesignDiscussion
DesignNo discussion
DesignRecommendation
DesignNo recommendation
Design6. Plan to work without full details.
DesignDiscussion
DesignIncremental design
DesignRecommendation
DesignWork on smaller details first
Sub Discussion()..................
View 3 Replies
View Related
Nov 15, 2006
Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.
For Example:
Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................
View 9 Replies
View Related
Aug 9, 2007
I need to create a repeatable macro/module to help me prepare a spreadsheet for import into Microsoft Access. In each row of the spreadsheet is various information including Order Number, Model, and Serial Number. The problem lies in the Serial Number column, which often contains several Serial Numbers delimited by commas. In order to serve my needs in Access, each Serial Number instance needs to become a new row ( record) wherein all of the information (except for the Serial Number) is the same. It is crucially important that all of the serial numbers are in the same column. (I tried using text to columns as a starting-off point, but seemed to dead end).
Example:
Order#1; Model1; Serial1, Serial2, Serial3
Needs to become:
Order#1; Model1; Serial1
Order#1; Model1; Serial2
Order#1; Model1; Serial3
View 9 Replies
View Related
Mar 21, 2008
I am trying to have rows moved from (Schedule) to (Complete) in the attached workbook. The criteria to have the row moved would be the user-selected "Status" list changing to "COMPLETE" in Column G.
I would like to have the row cut and deleted from Sheet4 (Schedule) once "COMPLETE" is selected from the list in Column G with rows below moving up. The row would then be inserted into Sheet2 (Complete) at the top of the list (Row 7) and rows would move down below the new entry. I would also like to have the formatting remain uniform (every other row fill)
View 2 Replies
View Related
Jun 21, 2008
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies
View Related
Nov 30, 2006
I am trying to delete rows - based on 1 field in each row not being equal to ANY of the values stored in an Array. e.g. my array has 3 values, CAT,DOG,MOUSE
cells are:
A1 = CAT
A2 = HORSE
A3 = DOG
A4 = DOG
A5 = BIRD
i want to delete rows not containing CAT,DOG,MOUSE. I think the autofilter could be the trick but cant quite get my head around it.
View 5 Replies
View Related
Aug 10, 2007
In each row of my data set, columns A thru E contain different three-digit numbers, in ascending order (e.g. 012 312 344 798 880). No number is repeated more than once in a single row, and no two rows contain the same set of five numbers. Column F contains a value (these are what I'd like to sum).
The variables I want to create a formula with are each three-digit numbers, call them X and Y. I want to find the pairs of rows where one row contains X in one of the first five columns, and the other row contains Y and the same other four values as were contained in the four non-X cells in the X-row (note that X and Y may or may not be in the same column, since each row is in ascending order). If X=344 and Y=955, an example of a match would be one row with 012 312 344 798 880 and another row with 012 312 798 880 955. For each X and Y combo there could be many such matching pairs of rows (with different sets of the other four values). I'd like to sum all the values in the X rows of such pairs (and then I'd do the same with a different formula for the Y rows).............
View 3 Replies
View Related
Sep 3, 2007
Searching through hide/unhide topics seem to yield more complicated codes then my meager needs. Within my worksheet I have a column BD which contains a list of values,
ie:
BD
1
2
3
6
3
2
1 etc..
I've created a toggle button "HideRow", which I would like to use to hide/unide only the rows containing value "2" within column BD.
View 2 Replies
View Related
