Look At Entire Column For Two Key Words
Apr 9, 2009
I have a work book with a lot of information. In column “W” I have a text comments, cells in this column probably have something or not, and the text is very variable. What I need is an instruction to look at entire column for two key words for example: "na co", "apna" or "apcredit" ... in case of one of these words its found I need the macro to make a process.
For example if in the column W there is any item (cells) with text "apnapo" then
First filter the information:
Selection.AutoFilter Field:=23, Criteria1:="=*apna*", Operator:=xlAnd
Range("K1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Then write down something in columns K & L
ActiveCell.FormulaR1C1 = "Ion Apnapo"
ActiveCell.Offset(0, 1).FormulaR1C1 = "Pamela Elizondo"
Selection.Offset(0, -1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, 1).Select
Selection.FillDown..................................
View 9 Replies
ADVERTISEMENT
Jul 2, 2007
How to copy an entire column having numbers and words on every single cell into another column but only copying numbers, not words.
View 9 Replies
View Related
Sep 20, 2006
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies
View Related
Jun 3, 2014
I'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
View 7 Replies
View Related
Jul 16, 2009
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
View 9 Replies
View Related
Aug 18, 2009
Does anyone have a macro that will delete entire row if column a matches column d
View 6 Replies
View Related
Aug 12, 2008
How do I use a sumproduct for a column with the words "YES", "NO", or "MAYBE" appearing?
I'm using
sumproduct(--$C$1:$C$50000="YES"),--($C$1:$C$50000="NO"),--($C$1:$C$50000="MAYBE"))>1
View 9 Replies
View Related
Dec 6, 2009
Sheet3
BCD1Words in SentenceRepeated TimesSentence2I2I go to the cinema every month on last Sunday3Go2I go to work at 7 O'clock4 Worl Cup will start on 2010
Excel tables to the web >> Excel Jeanie HTML 4
i want to convert all the words in column d by get one word from the column d and skip repeated
example word "I" mentioned in d2 and d3 so i get "I" in cell b2 then count it it mentioned two times in column D
View 9 Replies
View Related
Dec 16, 2013
Excel. Let's say this how a column looks like:
a
b
b
c
d
Is there a formula to get this outcome:
a 1
b 2
c 1
d 1
If not, is there a way to create a list of words that are in a column?
View 6 Replies
View Related
Jul 21, 2009
=IF((P3<$Q$1),E3-S3,IF((G3="B"),"Not Changed",IF((G3="MT"),"Not Changed","Not Changed")))
Above is the formula I'm trying to get to work but it's not-a-happenin. It seems like the first IF overides all of the others. Basically I want the first IF to be ignored if column G contains any of the words shown. So basically what I think is happening is that P3 is less than Q1 so it's ignoring the rest of the IFs.
Originally my formula worked fine as:
=IF(P4<$Q$1,E4-S4,"Not Changed")
But I realized that i needed a certain category of product ignored. Products are labeled in column G.
View 6 Replies
View Related
May 30, 2012
Very simple program I think, can either be solved by build-in functions or macro. So situation is I have a table, where the D column, contains certain words.
Now I have table where the M1:M10 column contains the same words and the corresponding column (N) contains the value.
So for example , cell D5= "A" and I find that cell M6 is also "A" so I then go at cell N6 which has the value "3.3". So now I want I5 to have the value 3.3 in it.
In Summary, I want the value of the n column copied into column I. Now I have plenty of rows in the D column so I prefer a fast way.
View 1 Replies
View Related
Jun 4, 2013
I have a spreadsheet with approx 7000 rows, many of which contain the same item but with flavors and other variations on the end. An example would be:
VB:
A B
10142 6kg of whey bundle With Free protein shaker-Banana
10143 6kg of whey bundle With Free protein shaker-Chocolate
10144 6kg of whey bundle With Free protein shaker-Strawberry
10145 6kg of whey bundle With Free protein shaker-Unflavoured
10010 **Bodybuilding Warehouse Premium Whey Probiotic - 2.2kg
10011 **Bodybuilding Warehouse Premium Whey Probiotic - 2.2kg + FREE Shaker
Would it be possible To create a New column (column C) which would display all common words from row b into the New column Like below?
A B C
10142 6kg of whey bundle With Free protein shaker-Banana 6kg of whey bundle With Free protein shaker
10143 6kg of whey bundle With Free protein shaker-Chocolate 6kg of whey bundle With Free protein shaker
10144 6kg of whey bundle With Free protein shaker-Strawberry 6kg of whey bundle With Free protein shaker
[Code] ....
I've attached a larger sample of our list to get a better idea of different variations that are on the spreadsheet.
I think what we need is something similar to this thread[URL] .....
Sample List.xlsx
View 2 Replies
View Related
Jan 16, 2010
I would like to modify the below macro so it only counts a word once even if it appears more than once in a row. The reason I want to do this is so I can get a snapshot of the data without certain terms being over-represented.
For example, in the attached example the word 'Microsoft' appears 5 times in row 4 but I only want this to be counted once. In the whole data set Microsoft appears 20 times but only in 7 of the 20 rows so I would like the count to be 7.
The example is set out as follows: In column B there is a description field which in practice will contain consumer complaints and inquiries. To keep the data anonymous the description is filled out with random words and all other columns are blank. The output of Andy Pope's unique word counting macro appears in I:J.
View 12 Replies
View Related
Feb 22, 2008
see attached spreadsheet. In sheet 1 I would like to look up each word in column D, seeing if they are in column B at all. Note if the word "Jill" is in D and "jilly" in a surname in B I would like it to get picked up. I have highlighted manually those that would get picked up. Those that do get picked up I would like to be copied into column C as per sheet 2 (this is what I would like it to end up like). There is a very long winded way of doing this using a find function and 1 column per word but as the actual sheet i'm using has thousands of different words this isn't really viable!
View 8 Replies
View Related
Feb 10, 2012
I need to do the following:
I have a list of cities on one sheet. The headers are the counties and below each (6 columns) are all the cities names.
I need to be able to show all the cities of a specific column on a nother sheet (autofilling column down with all cities names) when i type the name of the related county in an adjacent cell.
View 2 Replies
View Related
Jul 17, 2013
The subject summarizes what I want to achieve. I just want to tie an if statement to an entire column, so it will disappear if a statement is satisfied.
View 4 Replies
View Related
Apr 5, 2013
I'm inserting new information at the top of my worksheet. Unfortunately the formula for what WAS row one: =SUMIF(TotalSales!$B:$B,A1,TotalSales!$H:$H) Will no longer work for row one because as more date is collected row 1 shifts down to row 2. Row two still looks good but there is no longer a formula for the new rows inserted.
To be clearer... I would like to have the following:
=SUMIF(TotalSales!$B:$B,A1,TotalSales!$H:$H) in cell B1
=SUMIF(TotalSales!$B:$B,A2,TotalSales!$H:$H) in cell B2
=SUMIF(TotalSales!$B:$B,A3,TotalSales!$H:$H) in cell B3
How can I keep that formula in row one as I keep inserting new rows?
View 2 Replies
View Related
Nov 12, 2013
I have created one excel which contains column with range. (A1:A6). and the drop box included the values as Passed, failed, NA, NC) i have coded the the macro to change color as if i select passed from A1 then A1: F1 will be Red. please find the below code.
Sub Prasanna1()
Range("A1").Select
Select Case Range("A1").Value
Case "Prasanna"
Range("A1:F1").Interior.ColorIndex = 7
Case "Kale"
Range("A1:F1").Interior.ColorIndex = 0
[Code]...
The code is working fine for one row. Now i want to set the same code for entire column A. as if i will select A10 the same range of row should get change with fill color.
View 3 Replies
View Related
Feb 19, 2010
As the title sugests I would like to Select a cell and a macro will highlight the row and column
Sub Select_Entire_Row()
Dim RowNo As Integer
Dim ColNo As Integer
RowNo = Selection.Row
ColNo = Selection.Column
If RowNo.Value >= 1 Then
Cells(RowNo, ColNo).EntireRow.Select ' I want it to do both not one then the other
Cells(RowNo, ColNo).EntireColumn.Select
End If
End Sub
View 9 Replies
View Related
May 2, 2006
Need the code for deleting multiple columns simultaneously in an excel depending on some search criteria.Suppose I want to delete the columns whose names are "Roll No" ,"Age" & "Class"
View 2 Replies
View Related
May 9, 2014
I'm trying to build a function, but I haven't been able to successfully navigate some of the eccentricities of Excel formula-building.
Right now I am working on counting all of the cells that contain numbers (so likely just a =COUNT function), but will be eventually getting other descriptive stats as well. My problem is that I would like to create a formula that will perform the counting/mean calculation, etc., but only on a specific column that has the header cell that contains several specific words. Rather than having to type in the target words for each column, I would like to have the target words in the function be imported from multiple designated cells.
For example,
I want to find how many numbered cells are found in column E headed as "dog brown," but I also have columns headed as "cat brown" "dog black" etc. (which I will be using the formula on later) to paint a small picture, in my descriptive table, "dog" is in B31 and "brown" is in C30, then "cat" in B32 and "black" in D30.
View 11 Replies
View Related
Jun 11, 2013
I have huge columns of data that can only be 30 characters long. I want to use a formula to split the column into two. The original column will have a character limit of 30, and should not cut words in half. If the Length will be more than 30 while including the entire last word of the cell, that word should be the first displayed in adjacent cell in the 2nd column.
The 2nd column can be any length.
I usually use a Text to Columns method, but dont know how to make it not cut off the words.
View 9 Replies
View Related
Jun 21, 2013
how to accomplish more work quickly is proving difficult. I am making a word cloud of survey responses for my boss and he doesn't like to see similar words in the cloud (like Ease and Easy or even Fast and Quick). I have several hundred of these responses that are open ended answers that say one or many of these terms. Any way I can separate all the words and have them in one neat column? If I can get to that point, replacing those words will be simple enough.
View 2 Replies
View Related
Apr 1, 2014
I've been racking my brains trying to find a way of doing this. I have a list (column A in Excel) of over 50,000 organisations and I'd like to know what the most common words used in the names are. Ideally it would great if I could produce a top 10 list of the most common words at the top e.g. Ltd, School or Church with a count in the next column of how times that word it appears
View 5 Replies
View Related
Jun 23, 2006
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
View 3 Replies
View Related
Feb 4, 2013
On to the topic, I have all the US and Canadian states abbreviated (CO = Colorado, etc.) and was wondering if there is a way to make all of them convert to their respective names in one fell swoop instead of writing it in for each one, one at a time. Something along the lines where I can make, CO = Colorado, TX = Texas and then hit enter and all of the abbreviations would convert. Some kind of command.
View 2 Replies
View Related
Jan 22, 2010
I want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.
View 9 Replies
View Related
Nov 9, 2006
I have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".
View 7 Replies
View Related
Feb 15, 2007
I have 4-5 separate lists of words/letters and I would like to combine them in a certain order but creating all possible combinations resulting a new word. It's like a conjugation tool for verbs. For example if List 1 = be/el/ki/meg; List 2= m List3=e/é; List4= gy/" "; List 5=" "/ek/sz/nnek So the output has to be (if the order is List1+List2+List3+List4+List5)= bemegy/bemegyek/bemegysz/bemesz/bemész/elmegy/elmennek/ ..... and all possible combinations.
Preferentially I would like to automate it in a way that it straight outputs into MS Access. Also can it be automated that it reads the list from a file (database)?
(Also can it recognize the word by letters? Because the basic word that I would load the program= List2+List3+List4)
View 2 Replies
View Related
Oct 1, 2012
I have two worksheet. One worksheet (ws1) contains a list of item I want. The other sheet (ws2) contains multiple columns where the header (row 6) is named by item name.
I have the following code which deletes the entire column if the header name is not in the list contained in ws1 :
VB:
Sub delete_col()
Dim wanted As Boolean
Set ws1 = Workbooks("test1").Sheets("aaa")
[Code]....
First of all, this loop does not work properly since deleting the entire column shift them on the left, so when I first analyze column 11, if I delete it and then analyze column 12, the real column 12 now became column 11 and so on...
Secondly, this code is pretty slow. I am pretty sure I don't have to loop through my initial item list everytime I do Instr on a new column.
View 9 Replies
View Related