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 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'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
I have two columns(1 and 2), Column two will for each data set have two common values. For most of the data set where column two have the second common value, i can find it based on column 1, but where column 1 differs, i do not know how to get data based on column two.
See attached, column 1 and 2 data. Column 3 I took out one common value, column 4 I just did a IF function to populate blank spaces with common value, but for row 24 - 26 if does not work because column 1 does not follow same logic. Is there a way to populate a third column with one value(can be either of the two common values in column 2(note there will be multiple data sets)?
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'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?
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 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.
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.
I expect to deploy over 200 iterations of a workbook (templates if you will) containing a mix of up to one hundred different attributes each with its own list of valid values. My problem is the attributes don't always default to the same column in every template workbook. Therefore, to make things easier for users I would like to create attribute valid value dropdown lists on the fly using the "Valid Values' table as the source data (see the attached). Since the attribute names will be the same from one template workbook to the next, I wanted to come up with a macro that would reference a source table (a separate workbook) containing all valid values for all attributes which users would be required to download to their local machine.
See the attached example workbooks and an example of a "Valid Values" list. Here's what needs to happen: When a user opens template workbook #1 I need drop down lists containing the valid values for the attributes contained in cell B3 (User Code),C3 (Department) and D3 (Category). Likewise, when another user opens template workbook #2, I need drop down lists containing the valid values in cell B2 (Department), C3 (Group), D3 (Category), E3 (Level), F3 (Material) and G3 (User Code). Notice that "Department", "Category" and "User Code" are data elements common between both workbooks but resident in different columns.
A friend of mine is playing a computer game, and he has to create 50 different potions to obtain her next goal. She would like to know if it's possible with excel to generate a list of potions based on 7 different elements. Here are the 7 elements : Black orchid, Cactus, Lotus, Pitcher plant, Rose, Tiger lily
Every elements could be repeated to generate one potion.
Therefore, the following combinations are valid :
Cactus - Cactus - Cactus Cactus - Rose - Cactus Cactus - Cactus - Rose Cactus - Rose- Rose
As long as there are no more than 3 elements, it's ok. I'm not looking for the precise coding, I just need to be pushed in the right direction...
I have already created a VBA Script for other parts of this project, so I would like to add the script to the end rather than create a separate macro. If there is a formula that can do this I am willing to work with that as well.
A B Eva 1982 Anna 1981 Lisa 1983 John 1980 Steve 1972 ...
And
A B Eva Female John Male Lisa Female ...
Can in any way match the above lists automaticly, so the information from List number 2, column B, appears at the correct place in List number 1, Column C? For empty cells, no information is fine.
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
Code: 7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3 7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June
[Code]....
I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:
AMWG16=01_0211-20 AMWG16=01_0212-16
and these entries will appear multiple times as well
AMWG16=01_0212-16
appears twice and the data is different and will need to be added up
How can I:
1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"
2. Take all the data that has been added up and combine the statistics, for example, when its all done it should show
AMWG16=01_0212-16
in the first column, and then all the statistics from the two tables in the columns to the right.
I'm guesssing...
Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:
AMWG16=01_0211-20
Shows up three times, you'll need to sum up all the values and this needs to be done on both tables
THEN
You can combine the data
I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together
I have a matrix with info of persons of different cities in consecutive rows, where the city data is in column B. I would like to eliminate columnb B and divide all persons from any particular city with 2 new rows, their city name and below a field for every column data.
*The number of employees for city would be variable.
Original data (The columns used go from A to E)
1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1 2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2 3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3 4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4 5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1 6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...
What I'm trying to do is concatenate all of the URLs into one cell so that each email address has only 1 single row with many URLs associated with it. I tried using VLOOKUP(B1, $A$1:$B$4000, 1, FALSE) but this didn't work. I thought it was a long shot anyway. I also tried writing a bash script do parse a csv with this data and create a csv file with the URLs concatenated with no luck. I'm trying to get my data to look like this:
EDIT: The format didn't turn out like I'd hoped. In the first example, each URL has an associated email address, each in its own row. In the second, it is just one row, with multiple urls in the first cell and an email address in the second cell.
I have two worksheets. One has columns A B C , the second has columns A B C D. Columns A in the two worksheets reflect the same data (serial number) , although one worksheet could have more rows than the other since it gets updated manually daily. I would like to use a macro that would:
1. use columns A's value in worksheet 1 to match a row in worksheet 2 then merge columns from worksheet 1 & 2 and insert into a new worksheet 2. repreat until all rows in worksheet 1 are read 3. save worksheet 3
As I said copy/past is not an option as the IDs are not in the same order. I need to create a new spreadsheeet and have all "ID", "URL", "Location", "Picture", "Address" in that spreadsheet.
I have an Excel file in which I want to find the numbers which are repeated in several columns (B to L), but there are too many rows to find them looking one by one cell, is there a function which will allow me to find the common values which appear in these columns?
I have a column with rows having strings as below (see sample.xlsx excel attached) .
I want to delimit the string in such a way that the for each parameter, the parameters will beome columns with headers and the value against them will be shown in thier respective column. The output tab in the attached excel might make what I am trying to convey more clear.
So basically CPU should have its own column and its time against it.
There are thousands of such column that we wish to analyse and therefore the ask.
I tried various option to delimit including recording a macro but did not work
I tried to learn VBA about 4years ago, so my knowledge as died. I hope this question has not come up before, i did do a search before. I wish to create a new hyperlink based on a activecell eg. A1=SN0001 then hyperlink will be SN0001. Only thing i can remember how to do is record marco, but ofcourse copying the cell will return a hardcoded value.
The attached file contains a simplified version of a more complex macro in which a sub routine will be called a number of times to change the colours of cells in different ranges. However, an error is triggered because the a variable and range are not defined in the subroutine although they are in the main part of the macro.
I know I am missing something obvious here, but I'd appreciate any help in knowing how I can define the ranges in the main procedure and then call the subroutine to change values in the different ranges.
I am looking for the code in VBA to output the LCM (Lowest Common Multiple) of a set of numbers. LCM exists as a function in Excel but VBA does not seem to recognize it.
Is there a macro that can create workbooks based on this data. So, i'd have one workbook that would contain all the Johns and all the other data in Columns A -Z, then another workbook for Ted, and so on...
The number of workbooks I need is not fixed. It all depends on how many names are in column C.
I want to create a macro to insert 12 cells left of a column labeled "This Year" As the spreadsheet grows (by 12 columns @ year) the "This Year" column moves to the right. Thus I need to reference the range off of that column and then insert 12 columns directly to the left of it each year. Can I somehow reference the label "This Year"?
I then need to enter the month labels in the new columns row 8.
My problem is trying to reference off the "This Year" column.
I am trying to compare multiple column in a worksheet to find common component in all the columns and what is unique to a particular column only. And list the results/finding in adj column. What i am trying to accomplish is something as below.
