I have a long spreadsheet and want to remove rows of information from it if cells contain a specific word. If the cell in column A contains the word "district" or "borough" anywhere in the string of text and irrespective of the case I need to delete the whole row.
View 4 RepliesI am trying to create a macro that would be able to delete an entire row of data if a value in column A is equal to a range on a different sheet.
On Sheet “ISQ Raw Data”
If any value in column A (starting in row 2) is equal to a value on Sheet “Old Response IDs” Range A:A(all of column A)
Then Delete that entire row in Sheet “ISQ Raw Data”
I would like to use a formula that will pull values into a sum where the values are in a column and the category is in another column. The criteria in the text cells could be located anywhere in the cell.
Example:
Column A Column B
xyzyszt (Baseline) 7890
wrxyz4sdtr (Strategic) 1234567
zecgjioae (Baseline) Infrastructure 71235
qpielcmjj ae (Strategic) New Capability 278332
File I have attached or made a link for here Untitled-2.jpg
I am trying to make that amazon cell, all text to appear as one line like the second line foreign conversion fee i want it to appear like that. Rather then having to delete and modify 100's of rows like this.
I am trying to use find and replace but the text that i'm searching for appears three times in the cell. I only need to replace the first occurrence in the cell. Alternatively, if there is a way to do this, can the second and third occurrence be changed?
Example:
Cheryl called Louie to advise she would be late for the meeting. Louie responded that he would meet Cheryl at her office. Cheryl confirmed.
I need to change the first 'Cheryl' to a job title and the second and third Cheryl to her initials (CL) so would read:
Manager of Aboriginal Affairs called Louie to advise she would be late for the meeting. Louie responded that he would meet CL at her office. CL confirmed.
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies View RelatedI was wondering if it is possible to delete rows if a value appears only 1 time in a column? To clarify since 123 only appears once in column A I want the entire row 4 deleted.
View 2 Replies View RelatedI use this code to create sheets for courses but the student appears more than once - see attached Sample (Before sheet).
How can the code be changed so that the student only appears once on the sheet? - see attached Sample (After sheet).
Sample.xlsx‎
I have a spreadsheet that I need VBA Code to do the following
1) Delete all rows where the word totals appears in Column B for eg "Totals for Vehicles Stocked in 0706" "Totals for Vehicles Stocked in 0707" etc
2) Delete all rows where the value in col m is less than 45
I have written the code, but cannot get it to work-see my code below
Sub Del_TOTALS_Underaged()
FinalRow = Cells(65536, 2).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 2).Value >= "Totals" Then
Cells(i, 1).EntireRow.Delete
If Cells(i, 13).Value < "45" Then
Cells(i, 1).EntireRow.Delete
End If
End If
Next i
End Sub
Is there a macro to delete an entire row if a duplicate entry appears only in a certain column.
1. Look for the column header with the name "File Number"
2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column.
This is regardless of what is contained in the other columns. For example..let's say these cells contained this data...
B1 - UTE00225
B2 - UTE00546
B3 - UTE65513
B4 - UTE00225
B5 - UTE00225
In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1. I'm using Excel 2003.
I tried using Conditional Formatting, but for some reason it didn't work. I want a cell to be highlighted and/or bolded when particular text is written. It can be in any cell. This can also include blank cells (if possible within a given area such as 20 cells by 20 cells)
View 3 Replies View RelatedI am trying to have my cell show a range of dates, like:
5/25/14 - 5-27/14
I'm trying to use a formula like:
=DATE(2012,5,9) & " - " & DATE(2012,5,9)+2
But when I do this it just appears as:
41038 - 41040
Is there any way to tell excel to format these as dates?
When I hide a column in this spreadsheet, text appears in some of the cells that shouldn't be there. When I highlight and try to delete, it won't delete and it doesn't even show up in the function bar. I was able to get it to delete when I do clear, all from the edit menu but as soon as I try formating the cell, it puts it right back. Same text, same formatting. I can't get rid of it.
View 10 Replies View RelatedI have a range of data that is sorted (about 20,000 rows). In about 15,000 of these rows column D will contain a generic text string. How do I select the entire row where the generic text string appears in column D?
View 3 Replies View RelatedTime spent on each page appears in row 18. Need to sum up all of these pagetimes for each site, to get the total time on site. So in the example below,
D19 would reflect the total time spent on GreenElectricalSupply.com sum of cells D18-G18(1:23.0)
and H19 would display the total time spent on Rexel.com (00:45.0)
So, how to do a conditional sum across row 18, until a new site begins (indicated by the appearance of text in the next cell of row 8).
Data
CDEFGHI8
greenelectricsupply.com
rexel.comnextag.com9WW ONLY: Arrived at site via
[Code] .....
Excel 2007
I have a few spreadsheets with a few land transactions. I want to see if the parties involved are male or female, or both (in case of joint titles). And how many. I've tried to use ISNUMBER formulas and COUNTIF formulas but I can't seem to make them work. I've attached an example of what I need to do, the original has many more column with more info, and the names are in a different language which makes it easier to identify as female or not (like 'phany' in english female names etc).
View 8 Replies View RelatedI have a long list of terms, and I need to identify which of these terms appear in another body of text strings. Rather than searching for each individual term, is there a formula that check the terms for me automatically.
The terms will appear in the larger body of text, but they won't appear on their own, so they won't be perfect matches.
I've attached a sheet to try to illustrate the problem.
Term Checker.xls‎
I want to count how many times a particular text appears in Column A depending on the number times another text appears in Column B.
Say for example if I have in Column A {A, B, C, D}nd column B I have {AA,BB,CC) and if I want to check how many times column A has "A" value when the column B has "CC" value, then how should I proceed with this ?
I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.
View 5 Replies View RelatedI have number of items and many items appear more than once. I need a formula so that counts the number of item appearing maximum number of times and it displays the name of the text written NOT the number of times it is written. It should also calculate number of times it appears in a particular month.
For E.g.
Table 1-5-2012
Chair 1-5-2012
Fan 3-5-2012
Table 10-5-2012
Fan 1-6-2012
Window 1-6-2012
Glass 1-7-2012
Glass 9-7-2012
The formula should work as follows
Table 2 May-12
Glass 2 July-12
I have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit.
Here's an example, Column C contains:
Dan Parker
John Doe
Dan Smith
Jill Smith
So if I search on *Dan*, the function should return a count of 2.
I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro.
I'm looking for a formula that can determine whether a given sub-string appears in a string. For example if cell A1 contains the string "bathing", and cell A2 contains the sub-string "bat", the formula in A3 would return "TRUE".
However, if the sub-string in A2 is changed to "cat", the result in A3 would be "FALSE". The formula would need to work with strings of different and unpredictable lengths.
i have a spreadsheet where I need to count up how many times a particular phrase within a text string appears. The text string will be duplicated many times throughout the spreadsheet.
For example :
Miss X was at work on Saturday
Mr XX was at work on Saturday but not Tuesday
Miss Y was at work on Tuesday
So I would like to count up how many times "work on Saturday" appears in my spreadsheet, and then as a seperate query, how many times "work on Tuesday" appears.
I have values in A1 through A4.
I have a string of text in cell B1.
In C1, I need a formula that displays TRUE if any of the 4 numbers from A1 through A4 are contained within the text in B1. If not, FALSE will be displayed.
Been messing with SEARCH and FIND, but not getting it to work.... not sure if those are the functions I need.
I want to count the number of times a word appears in a range (like M9:S663), but sorting it by the month it appears (eg: january = 2, february = 56, march = 2000, etc.)
I managed to do this but there has to be a better way
=COUNTIFS(RNM.1;"PRUM Transcripcion";FechaComite;">=01/01/2012";FechaComite;"=01/01/2012";
FechaComite;"=01/01/2012";FechaComite;"=01/01/2012";FechaComite;"=01/01/2012";
FechaComite;"=01/01/2012";FechaComite;"=01/01/2012";FechaComite;"
a macro that will delete a line if the message #N/A appears. Right now I have a macro that will hide a row if it has a zero in it and it loops all the way till it hits "done" would like to add the ability to delete a row with #N/A in it, here is my code for the hide zero macro
View 9 Replies View RelatedI need a code to delete all rows with "Complete" in Column D below Row 7.
View 2 Replies View RelatedNote: The "Bread Type" column will ALWAYS be in row 1, BUT will ALWAYS be in a different column letter, so I would like this macro to find the "Bread Type" column by name rather than by column number. All criteria should NOT be case sensitive.
In the example below, rows 2, 4 & 5, have the text "Wheat" or "Rye" in the "Bread Type" column, therefore, all 3 of these entire rows should be deleted. Yes, I do mean the ENTIRE row, wipe these rows off the face of the earth
In a column I have cells where the first character is numeric and the rest text and also cells where the first character is text.
I want to delete the rows where the first character is text.
Need to search a sheet and find cells that contain the text "Requirement". If found then i want that cell to become blank.
example
so in sheet1,
i have a number of columns and a number of rows
in cell A3 the value is - " there are requirements"
in cell F23, the value is -"the Requirement is"
since both cells have the word requirement, I want these cells to become blank.