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
I've found a vb script via Google that displays the most common words in a list followed by a number.
Code: Sub HTH() Dim vArray As Variant Dim lLoop As Long Dim rCell As Range With CreateObject("Scripting.Dictionary") For Each rCell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
[Code] .......
At the moment this results in 1 word followed by a count.
How do I alter this so it shows 2 or 3 words, ultimately showing the most common phrases?
I have a spreadsheet of part #'s, descriptions, manufacturer names, and manufacturer part #'s. (It's a list of the inventory in my warehouse). Each row contains information for just the item in that row. Row 2 references another part in my warehouse, row 3 yet another, and so on.
Many of the parts have more than one potential manufacturer and part #, (meaning that any of those manufacturer's part #'s are basically the same tool; just different brands. At one time we may get a shipment of one, at other times we may get a shipment of another). For example, a screwdriver may be listed like this:
Part # 1234 screwdriver, mfg Snap-On, part # 456, mfg Stanley, part # 789, mfg Mac Tool, part # 439.
Then further down the list, there may be another part listed like this:
Part # 9980 wrench, mfg Stanley, part #741, mfg Snap-On, part # 852, mfg Proto, part # 369.
If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'Snap-On' was the first mfg name on the screwdriver, but it was listed as the 2nd mfg name on the wrench.
So, here's my question: I want to be able to group all of the items made by any one manufacturer together in a new list. If all of the manufacturer names were in the same column, I could simply sort the list by that column, but since I've got thousands of rows with the mfg name I'm looking for in different columns on different rows, I thought maybe a macro could search each row for the word I'm looking for, then if found, take the whole row and copy it to a new worksheet. So the end result would be, If I wanted to see all items of which Snap-On is an acceptable supplier, I could get a list of all potential Snap-On items grouped together.
I'm sorry this is so long. I may have over-worded this and it may not be too clear. I could email an example of the spreadsheet if anyone needed more info to figure out what I'm looking for and was willing to take a look at it.
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).
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
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.
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] .....
I'm trying to write UDF which getting RegEx pattern and a certain cell as arguments and returns only matching string. For examples for string "The quick brown fox jumps over the lazy dog", and RegEx pattern "w{4}" the function will return two words "OVER" and "LAZY". What should I change in my code?
Function GetPattern(myPattern As String, myString As String) Dim regEx As RegExp Dim Matches As Object Set regEx = CreateObject("VBScript.RegExp")
With regEx .Pattern = myPattern .IgnoreCase = True End With GetPattern = regEx.Replace(myString, "$1") End Function
I would like to sync cells together that contain common words for sorting purposes is this possible? For instance i have a [URL] ..... in column A row 1 and In column B row 2 i have the word bellmont i need to get the rows to sync so rows containing common words line up. I have 8,000 rows to sync?
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".
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)
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.
I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.
I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.
I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.
I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.
I have a fairly large timecourse dataset and I need to find all common values within all 3 columns. Also, when I find these 'common values' is there a speedy way to retrieve data in the same row that is associated with these values, instead of going back one-by-one and copying and pasting beside the value that the function has returned?
I have 3 sets of data in three columns A, B and C. Data in Column A is the base data, now i have certain common records in columns A, B and C. Considering the column A as the base, I want to find out in Column
D = Common records in A and B, E = Records only in A not in B F = Records only in B not in A G = Common records in A and C, H = Records only in A not in C I = Records only in C not in A J = Common records in A, B and C
if possible also in Column
K = Common records in B and C L = Records only in B not in C M = Records only in C not in B
I have about 5000 records in column A, B and C each.
In column A I have 50,000 cells, each containing 1 to 10 keywords. For example A1 = "jobs" A2 = "jobs in milton keynes" A3 = "it jobs in milton keynes" A4 = "sales jobs in milton keynes" A5 = "well paying brickie work in spain" etc etc
At first I was trying to find out the most common keywords in column A, and I used the following code to do so
I have two lists, one is 15,000 records, the other 100 records. I want to find the common records between the two. I am using this formula currently, but is returning an incorrect result-- =INDEX($B$2:$B$11157,SMALL(IF(COUNTIF($M$2:$M$100,$B$2:$B$11157)>0,ROW($B$2:$B$11157),1000),ROW()-ROW($P$2)+1)-ROW($P$2)+1)
this is the record number in M2 BU1HAD80
it is returning BI1METBRSM as the common record. This formula is from Joseph Rubin's book F1 Excel Formulas and Functions
I need to find out common numbers between columns. Each column has multiple number entries seperated by a comma. For example:
Column A: "5592,12222" and Column B: "1,2,3,4,5592,123123" and Column C: "3, 4"
I need to find out any numbers that are found in more than 1 column. Hence, the result should be something like "3,4,5592". If no match is found then maybe can indicate by coloring the cell. My data is over 50,000 rows and 30 columns. I need to do this for each row one by one.
Got something I need to do that I have never done.
I have a column on a worksheet1: NBC//1 ABC//1 ABC//2 ABC//3 ABC//4 CBS//1 CBS//2
I have a column on another worksheet2: NBC ABC CBS
In the column next to the above on worksheet2, i want to pull the numbers in for that (the numbers are contained in ONE column): NBC | 1 ABC | 1 2 3 4 CBS | 1 2
Im not sure how to go about this? In the example I attached, on sheet 2 column B, that is where I want the numbers to build to.
What i am trying to do is to to determine the common material that is used among different model do product in a product family. I have the column C the various part number for the product family. Each product model is made up of different combination of the parts.
In I3:U3 i have the model number for each product. Under each are the combination of various part that make up each model. What i need to do is in column G conditional formatiing that if all the different model use a particular part (part number). The respective cell in column in the row will be color. This will help me to determine what are the parts that are common to all the product.
Column C Column G Column I .........................Column U Part no Common Product 1 Product 2 Product 3 Product 4 12-1234-56 no color 1 4 0 6 13-2345-45 color 2 3 2 2 14-1234-56 no color 0 2 4 2 14-1234-56 no color 0 2 2 2
Problem description: I need to put together twelve arrows each comprised of four components; field point, front insert, shaft & fletch and nock. Therefore, I have forty-eight components in an Excel table. Each of these components may differ slightly in weight. My objective is to mix and match all of the components to create as many arrows as possible of the same exact weight.
Two questions. 1) How should I be thinking about setting up the problem? 2) Which Excel function returns the results I'm looking for?
I have a list of parts in Column A, often times with duplicates in the column. I have a list of parts in Column B (from another source), again, with duplicates.
What I need is - leaving columns A and B alone;
Column C to be unique Column A parts Column D to be unique Column B parts Column E to be parts in common between Columns C and D.
Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:
Capture.PNG
- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on. - I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.
I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?
