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.
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 RelatedI'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.
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 RelatedHow 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 RelatedHere is my dataset... with ( cell references):
(Cell A1): "John: Apples, Oranges, Grapes"
(Cell A2): "Sally: Oranges, Peaches, Grapes"
(Cell A3): "Tom: Apples, Peaches"
(Cell A4): "Bailey: Apples, Grapes"
How do I get the text above formatted to bold the individual's names? Here is my desired result:
(Cell A1): "John: Apples, Oranges, Grapes"
(Cell A2): "Sally: Oranges, Peaches, Grapes"
(Cell A3): "Tom: Apples, Peaches"
(Cell A4): "Bailey: Apples, Grapes"
how to do this with visual basic ( VB) code. I need VB instructions to be detailed.
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
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.
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 RelatedI 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 RelatedI 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)
I'm trying to write some code that will make excel examine a particular range, determine if there is text in each cell, and if there is replace it with different text.
View 6 Replies View Relatedi need to look for two words along a range along a row. i need to know if only one of the words is found or if both are found. ie if apples or oranges or both are found in a range along one row.
I need a way to do the following in Excel:
column A has text
column B needs a Formula that returns specific value(s)
ie. want to find any of 17 specific strings within a row in column A and return that (those) string value(s) in column B
for example row1 column A contains the following:
Troubleshooter Notes: Sound KVSND Tree was not used as doc exists that covered the specific issue
The formula in column B needs to be able to find any of the following values (note: these values could be placed in a lookup table):
"KVBAT" "KVBLTH" "KVDNW" "KVDOC" "KVDWB" "KVFLSH" "KVKBD" "KVLAN" "KVODD" "KVOTH" "KVPFS" "KVRCVY" "KVSB2" "KVSFT" "KVSND" "KVVID" "KVWFI"
and place the found value(s) in row 1 column B
in the above example column B would return the value:.............
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
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..................................
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
Looking for a formula (not macro) that can do the following:
I have a spreadsheet and in one column, it has last names of clients. In the next column, it has full names of clients that are the same account, but different system with different descriptions for the name. I need to lookup the column that just has last name and see if that last name exist anywhere in the "Full name" column. If it does, then I need column C to say "Yes" or something along those lines.
The "full name" column isn't always in the same format and sometimes doesn't even contain the "last name." Sometimes the last name will be first and sometimes there's a comma and sometimes there isn't a comma. The spreadsheet attached shows a good short example of what I'm working with...
Name Spreadsheet Ex.xlsx
I've got a list in "column A" of around 6000 lines which contains key words which I want to extract to "Column b" if It matches but it can match on more than one word .
e.g
List
Six Nations Ireland v England
FA cup Semi Final Chelsea v Liverpool @ Old Trafford
Criteria Sample
Ireland
England
Chelsea
Liverpool
Ideally I would like to extract the first Town/Country as this is where the event is held unless the @ symbol is used then it's the last Toen/Country as in the FA cup example.
I am trying to count words in seperate rows,columns in a spreadsheet. I have tried to use the formula that was suggested in one of the formula pages but I did not get the correct answer. I tried to do just like the information said with a1:a5. I did get 5 as an answer but when I deleted one of the words it still said there were 5 words.
View 4 Replies View RelatedExcel. 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?
=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.
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.
I have a excel sheet that contains about 8000 entries... I need to create a macro to help me. my file is like this:
123
RT4
eliott.billy
mark.garth
elena.david
I would like to split first name and last name. All the ligne that contains "." split it in 2. Example: eliott.billy will become eliott (column A) billy (column B)
I am look for a macro that can look through a range of cells within a sheet and sorts that range of cells by word count.
I have 1 macro that is excellent which sorts by character count.
If possible, if this macro can be duplicated exactly the same, but to sort by Word count and not character count.
As a note the range of cells are always in a sheet labelled " AllKWs"
Always start from Cell A3 running down.
And the phrases could be anything like "car to rent in london"
As to the macro I have already for character count, it is this kindly written by VOGII
Sub NoOfCharacters()
Dim lastrow As Long, TotP, TotC, AvC
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow < 4 Then
MsgBox "Nothing to sort!"
Exit Sub
End If
I have a key-word, and I need to search a 90,000 words in a range where i should find several words that have 2, 3 or 4 letters of the first letter of the key-word, then find several words that have 2, 3 or 4 letters of the second letter of the key-word,
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.
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 RelatedTrying to do:
Where ever "New Orleans Saints" shows up in my sheet. I want to replace it with "NO"
Where ever "Washington Redskins" shows up in my sheet. I want to replace it with "WAS"
.
.
.
This continues on for 32 different teams.
I would love to be able to click on an assigned button in a new sheet, and a pop box appears.
Then I can type in a word, click find, and the macro will go through my whole list of keyword phrases and find all the words STARTING with the word chosen in the pop up box.
Yes, I could just sort from a-z, find the word, then copy and paste etc.
But I work with large lists sometimes, and its just so much easier to type the word, click find and the macro returns the list of just the phrases with the starting word I've chosen
As a note. I did put a post up a while ago which a coder called Jindon solved which works perfectly. (A great job by the grand master)
This macro finds the words ending with.
So, what I would really like is exactly the same macro, but it finds words at the start of a phrases not the end of a phrase.
I'll post Jindon's macro now
Sub Find_Words_Ending_In()
Dim sTime As Single, res As String, msg As String, x As Range
Range("c3:c" & Rows.Count).ClearContents
res = InputBox("Enter word to find")
If res = "" Then Exit Sub
sTime = Timer
On Error Resume Next................