How To Count Unique Words In A List
Sep 30, 2008
Usually it's to count for one unique word in a cell. But what if I have 2 or more unique word in a cell and need to be counted for?
for example
10827Holiday Decorations & Party Supplyholiday decoration supply10827Holiday Decorations & Party Supplyseasonal decorative
this category has 5 unique words in the synonym list
11044Facial massagerfacial11044Facial massagerbeauty care product11044Facial massagerbeauty appliance11044Facial massagerbeauty11044Facial massagerbeauty care11044Facial massagerbeauty product11044Facial massagerfacial appliance
this category has only 5 unique words although the synonym list is much longer.
View 9 Replies
ADVERTISEMENT
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
Dec 6, 2011
I have 15 different words that I want to order in as many ways as possible, but only 3 at a time.
So let's say the words are:
cat
dog
elf
clown
monkey
rock
bananas
(etc)
then I want a list that has all of these possible combinations:
cat dog elf
cat dog clown
cat dog monkey
cat dog rock
cat dog bananas
cat elf clown
cat elf monkey
(etc)
until all are listed. I understand there is a huge number, I don't mind having a couple of thousand as long as they are all genuinely unique.
I CAN have elf dog cat, elf cat dog, dog elf cat later on ... it's just the order that needs to be unique not the words in the phrase.
View 9 Replies
View Related
Feb 26, 2014
I have uploaded my spreadsheet.
I have a column of text strings on Sheet1, Column A, which I need to check for the presence of keywords listed on Sheet2, Column A
So if any word from the keyword list on Sheet2, Column A is found in, say, cell A2 of Sheet1, the cell to its right (B1) should have a formula to display the count of keywords found in A2. I also would like to see each keyword identified either through a highlight or a list. I need the formula to NOT be case sensitive and the match does not have to be for whole words).
View 2 Replies
View Related
Jun 25, 2008
I am attempting to take a very large list of keywords, and find the most common words and phrases within them. For example, if I had a list that said:
excel formulas
excel spreadsheet formulas
excel help
excel formulas help form
formulas for excel
I would like to come away knowing that "excel" and "formulas" are common words within the list.
Currently, I believe this can be accomplished by doing the following:
1. Break down each line into all of its possible combinations.This would mean that the line with "excel spreadsheet formulas" would return:
excel spreadsheet formulas
excel spreadsheet
spreadsheet formulas
excel
spreadsheet
formulas
2. Once the entire list is broken down into its many parts, use the pivot table feature of excel to determine how common each of the parts is within the entire data set.
So, my questions are these:
1. Do you believe this is the best way to solve my problem? If not, what would be the preferred method?
2. If this is the best method, what function or script would I use to accomplish the first step of breaking down the lines into their individual parts?
Mike Auto Merged Post Until 24 Hrs Passes;It appears I put too many characters in the title of my post. It should read: Common Words - Decomposing Text Phrases
View 3 Replies
View Related
Jan 22, 2009
to calculate the count of unique text values from a list based on condition.
I have 2 columns A & B. In column A, I have Yes or NO text and in column B, I have Client names.
I want to count a list of unique values from column B if it is Yes in column A.
I have to write a formula to do this task and will appreciate any expert help in solving this.
View 9 Replies
View Related
Jan 29, 2012
I am trying to get an unique list based on the value being the only member of the group.
Sheet1
A B 1 Group A A 2 Group A C 3 Group B B 4 Group C C 5 Group C B 6 Group D B 7 Group E C 8 Group E B
The solution is
Sheet1
D E 2 Solution 3 Group B B 4 Group D B
Is there a way to do this?
View 9 Replies
View Related
Feb 8, 2008
I have created a VBA Script with the goal of taking an initial Excel Spreadsheet and essentially creating a summary of it. The original Spreadsheet has data in it such as:
110 Compact Car
110 Compact Car
111 Full Sized Car
111 Full Sized Car
111 Full Sized Car
113 Truck
The numerical part of the above data is in column A, and the description is in column B of the original spreadsheet. I have been successful in creating a new Spreadsheet, naming the columns with each unique entry from the first set of data, but I cannot seem to correctly formulate a looping statement which correctly counts the above. The solution that I would like to get to on the new sheet is the following:
110 Compact Car 2
111 Full Sized Car 3
113 Truck 1
The number after the descriptions would be generated by the VBA Script and placed into column C in the new Spreadsheet behind the correct description.
View 5 Replies
View Related
May 25, 2014
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A
ABCD
ABCD
ABCE
BDCE
BDCF
ZXYE
ZXYE
YEZY
CDEF
Col B is where i would like to list all the unquie vaules with a count e.g.
ABCD 2
ABCE 1
BDCE 1
BDCF 1
ZXYE 2
YEZY 1
CDEF 1
View 14 Replies
View Related
Jul 27, 2009
I need to count the unique values in a list based on another value. i.e.
AB ___ Jones
AB ___ Smith
BC ___ Henry
AB ___ Jones
BC ___ Brown
BC ___ Henry
AB ___ Tomkins
For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:
=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))
but haven't been able to get it to work.
View 9 Replies
View Related
Jun 1, 2006
I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?
View 6 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
May 16, 2008
I have a list for example:
apple
pear
grape
orange
i'm after some code that will create every possible combination of the list.
The examples i've found in the archives give all possible combinations of the same list in a different order
e.g apple, pear, orange, grape.......apple, orange, pear, grape.........apple, grape, orange, pear etc etc etc... THIS IS NOT WHAT I'M AFTER.
i'm after every possible combination starting right at the beginning with single words
e.g
apple
apple, pear
apple, pear, orange
apple, pear, orange, grape
pear, orange,
pear, orange, grape
orange,
orange, apple
etc etc you get the idea..... but i do not want repititions like
apple, pear.........pear, apple.
apple, orange, pear ...........pear, orange, apple.
View 9 Replies
View Related
Aug 24, 2009
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies
View Related
May 9, 2014
in column A i have fruit words (e.g. apple, banana, orange...)
in column B i have cities (e.g. london, paris, rome...)
i would like a formula in column C that gives "british apples" when "apple" and "london" are on the same row
whilst also giving "french bananas" when "paris" and "banana" are on the same row.
View 10 Replies
View Related
Oct 22, 2009
Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
View 8 Replies
View Related
Jul 30, 2009
I have a data table that repeats as follows:
CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC
From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC
I am trying to use the following guide:
http://blogs.techrepublic.com.com/howdoi/?p=111
Get stuck on "Listing A:
View 9 Replies
View Related
Jan 18, 2010
I have 1 or 2 maybe 3 words in a cell (difference words). I need to count how many of the same words are in the cell and place the number of words another cell I have included a file the same word may appear in other cells in same column which has to be counted into the same cell
View 2 Replies
View Related
Sep 19, 2013
The following formula counts the word WO in a row
I want it to also count the work WL
So basically want it to count WO & WL
=COUNTIF(M3:JS3,"wo")
View 3 Replies
View Related
Jun 12, 2006
I am trying to use Excel to do my manpower allocation and planning. So I have
a column of employees and a row of months. I then put in the project name
that each is working on in a given month.
then at the bottom, I just sum the number of people working on a particular
project in a month and do a simple calculation between the number of
available people and the number who are allocated. This works beautifully IF
each person only works on one project in a month. As we know, this is an
unreleastic assumption!
What I would like is for me to put in multiple project names in a cell and
for the 2nd table to be smart - i.e. if I put in Project A & B for John, then
it should count John as 0.5 for each A & B not 1 for each A&B. Similarly if
John is working on 3 projects, then it should count only 0.33 for each
project John is working on.
View 11 Replies
View Related
Dec 14, 2006
I have a problem to count some words in a column: in fact, in the cell they can write several words and my objective is to count in this column how many times there is the word "info" but as they can write several words in the same cell,(for example: speciality, info, degree) I can t really count how many times the word info is written in the column.
View 13 Replies
View Related
Feb 17, 2008
I am trying search & count 2 or more words within a cell using a worksheet function. See below example:
The texts are in A1 (only)
Entered by John at 02:19 PM on 02/13/2007
Entered by Smith at 02:20 PM on 02/13/2007
Entered by John at 02:30 PM on 02/13/2007
Entered by John at 02:21 PM on 02/12/2007
Entered by John at 02:22 PM on 02/11/2007
The words I am looking for are "John" and "02/13/2007" and the count should be 2. I bolded what should be counted.
View 12 Replies
View Related
Mar 26, 2008
I would like to count the words separated by a "-"
Right now I have this below
=LEN(TRIM(J1448))-LEN(SUBSTITUTE(J1448,"-",""))+1
But what I would like is for the formula to count the WORDS and Numbers in a cell that are separated by a
"-"
OERT-JTF-JUA-JTF-ASG1-JMF-JMF-JTF
so the answer would be 8
View 9 Replies
View Related
Aug 11, 2008
I have a table with some reapeated words in, I wondered if anyone had any methods of counting words and displaying the total in number format. for example if I had in cell A1-Hello and A2-Hello and A3-Bye how can I get Hello = 2 and Bye =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
May 25, 2007
i can count how many "A" there is in a text put in 1 box and "B" to be put in another box etc.
View 9 Replies
View Related
Jun 20, 2008
I am looking for code to look at a cell and see if it contains more than one word. So if Cell A has one word Cell B = ? If Cell A has two or more words Cell B= something else. I assume that searching for spaces would be best way to accomplish this
View 5 Replies
View Related
Apr 10, 2014
I need a formula to count the total number words occurred in an array. It has to separate the "," and count.
Here is a scenario:
1.Apple
2.Banana
3.Orange
4.Mango
5.Banana
6.Mango
7.Orange
8.Apple, Banana
9.Banana, Mango
10.Mango, Apple
11.Banana
View 7 Replies
View Related
Oct 11, 2009
I want to count the nr of words in cell without counting the repeated words.
For example, in "this is a ball and that is a square", the total nr of words is 9, and the total nr of non-repeated words is 7, because "is" and "a" are repeated twice, and I just want to count it once.
View 10 Replies
View Related
