Finding Keywords On A Large List

Jan 20, 2009

I need to setup a macro that will search for a keyword in a list with about 21000 rows. When the macro finds the keyword it cuts the row out and moves it to another sheet. I'm having a few problems

1. I don't know how to make the function search until it can't find the keyword anymore, then move on to the next word

2. I can't get it to select what it finds it finds the cell but I don't know how to select that cell once its found.



Sub Search()
'
' Search Macro
'
Dim keyword As String
Dim x, y, z As Long
For x = 1 To 53
Sheets("Test1").Select
Range("A" & x).Select
keyword = Selection.........

View 9 Replies


ADVERTISEMENT

Finding List Of Keywords In Column?

Mar 17, 2014

I need to find 100 or so keywords in a column, I have the list but how would I search multiple keywords as there are 10k columns and it would take forver to search for them manually?

View 4 Replies View Related

How To Categorize List / Column Into Separate Ones By Finding Keywords

Jul 28, 2014

Say I have a list:

VB:
Something something apple
Something something orange
Something something banana
Something apple something
Something banana something
Apples woo
Apples And bananas
Something orange something
Something something apple pie
Something something vegetables

And I want to separate the list into separate columns/lists with certain keywords (e.g. apple, orange, banana) like so:

Apple
Something something apple
Something apple something
Apples woo
Something something apple pie

[Code] .....

The "multiple words found" part isn't a big deal (I'm not sure how I'd deal with it anyways). The "Unsorted" part is there assuming creating the separate lists won't remove the entries from the original list (which would then leave all the unsorted text).

View 3 Replies View Related

Finding Keywords To Identify Product In Multiple Cells

Jul 23, 2012

Macro - it is currently set up to find a specific text, and activate that cell. From there, the spreadsheet has data in 6 cells in the 6 rows below the active cell. What I need the macro to do is to identify the product based on key words in a range of cells: that is, if any of those cells contain the string "total", than the product type is total, but if "total" is not found, look for keywords such as "alpha" and "beta", to identify the product type. If its not a total product, then i want it to look for "productB", "productc", and so on with relevant keywords until the product is identified.

I think IF(ISNUMBER(SEARCH can be used, but I'm not sure how, and I'm not sure how to search all 6 cells (to make things worse, the number of cells vary, so I'm using the "find" function to figure out the first and last cell that contain data).

View 5 Replies View Related

Changing Cell Text Color WITHOUT Using Conditional Formatting Based On Finding Keywords

May 29, 2012

I have a spreadsheet that uses VB macros to calculate sums of cells based on the font color of the numbers inside. It used to be fairly easy going through each cell and "classifying" them by color, so that my macros can go ahead and sum the numbers in each respective color's cell... but now I have a huge amount of numbers and would like to automate the process somewhat. Here's an example using the A and B columns:

flight $400
hotel $150
hotel $130
meal $20
meal $15
flight $350

I tried using conditional formatting to automatically change the color of the adjacent cells based on the presence of a keyword such as "flight" or "hotel", but this change is only cosmetic, and doesn't actually change the font color (it is still the default black, hence why my color-summing macros won't work!).

I'm including a sample macro for what I use to color-sum my cells, but what I am looking to automate the color-coding process based on looking for keywords as explained above in my example. Here is one of the working color-summing macros (for red, in this case) if you'd like to use it as a reference:

Function SumRed(SelectedCells As Range)
' Adds the values of the cells where the font colour is red(3).
Dim Cell As Object
Dim x As Double

[Code] ...........

View 9 Replies View Related

Highlight Keywords From A List

Oct 25, 2006

I would like to develop a macro that can be run against an excel doc that will highlight keywords from a list I have established.

For example: If my excel doc contains the words, "sales, selling, sell, sale, school," make those words bold, red and 2 font sizes larger.

View 9 Replies View Related

Selecting All Rows With A List Of Keywords?

Mar 15, 2014

I have a list of Products in my Excel file, I am trying to put them into categories easily. So what I am asking is there a way to select all rows with a certain key word in them so I can easily paste them into my other excel file?

View 1 Replies View Related

Find Duplicate Keywords, And List Maximum

Feb 13, 2010

I have rows of 10 keywords. I want the macros to compare the first row against all the rows except itself, then compare the second row against all the rows except itself, the third row against all the rows except itself, basically looking for duplicates...It will then record in a column for each respective row, the max # of duplicates found with another row..

For instance lets say I was looking at the 15th row, and it looked for duplicates elsewhere. It only found duplicates in the 4th row and the 5th row. In the fourth row, there were 5 keywords common, and in the 5th row there were 7 keywords common. Therefore the max # of common words is 7 for the 15th row, and that is what is recorded.

In addition the number of rows will probably vary it could range from 1 to 500. I included an excel spreadsheet that has visually speaking how this could work, maybe using COUNTIF. Basically it counts the # of common keywords for one row with all the other rows, pastes the count #s to the right, then finds the max of those count #s, copies the max into another column, then clears the count #s, and looks up the next row, repeats the same process. It is easier probably if you look at the attached spreadsheet to get an idea of what i am talking about..

View 9 Replies View Related

Filtering By List Of Keywords And Pasting Results Into Spreadsheet

Aug 19, 2009

I have two Spreadsheets, lets call it Spreadsheet 1 and Spreadsheet 2.

Spreadsheet 1 has a list of partial employee names in column A.
Spreadsheet 2 has a list of 500,0000 full employee names in column A and their phone number in column B and address in column C through E.

Basically what I want to do is do a wildcard search for the partial employee name, so like lookup Spreadsheet 1' Column A's *name* in column A of Spreadsheet 2. Based on the number of matches. I want to insert that many blank rows underneath the partial employee name, and paste those matched rows there, then continue to the second partial employee name lookup and repeat the process.

This loop has to repeat 1000 times.

View 14 Replies View Related

Finding Multiple Cells With LARGE

Sep 3, 2009

Is there a way to return not only one cell but two with LARGE?

I'm still working on my top 5 sellers list and trying to generate the list correctly. As of now when I have multiples of the same value I get the same name. Here's what I have now:

Name---Sales
Bob---75
Sue---68
Tim---66
Tim---66
Tim---66

What I'm trying to do is this:

Name---Sales
Bob---75
Sue---68
Tim---66
Jane---66
Dave---66

View 9 Replies View Related

Finding Certain Values In Large Amount Of Data

May 8, 2013

I have a large amount of data and am trying to find certain values.

Firstly - I am looking for any cell which has the number 0 in it, as I need to edit that specific cell. However, when I search for the number 0, multiples of 10 (e.g. 10,20,30,etc) come up in my search. Is there anyway to find the number 0, without also finding the multiples of 10?

Secondly - I am also looking for values over 100, is there a search function which allows me to do this?

View 3 Replies View Related

Getting Data From Large List

Jan 14, 2014

I have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.

See Below:

Date ID
12/1/2013 10:00:00 AM 67890
12/6/2013 12:00:30 PM 67890
12/18/2013 06:30:05 AM 67890

From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.

End Date ID
12/1/2013 1:00:30PM 67890
12/6/2013 4:00:45PM 67890
12/18/2013 9:30:00 AM 67890

Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.

View 9 Replies View Related

Finding List Which Has Most Similar Entries To Reference List

Apr 26, 2014

I have two lists of product data, one for buyers and one for sellers (these are listed as A, B, C). The product names are not exactly the same (Eg Playstation and playstation three should be matched), I would have thought using the FuzzyLogic add in to match these would be the way forward! I need to rank the sellers by how many of their items appear on the buyers list

View 8 Replies View Related

MS Word Large Numbered List

Sep 1, 2009

I have a very large numbered list in MS Word, in the following style:

1. Animals
1.1 Mammals
1.1.1 Dogs
1.1.1.1 Beagles
1.1.1.2 Pugs
1.1.2 Cats
1.1.3 Elephants
1.2 Fish
1.2.1 Cod

...and so on.

I am trying to bring this data into Excel (2007) but in a way so that:

"Animals" goes into column A.

"Mammals" and "Fish" go into column B (in their own cells, in different rows).

"Dogs" and "Cats" go into column C.

"Beagles" and "Pugs" go into column D...

and so on.

The idea is to have each line in a different row, but have the columns serve more or less the same function that the tab indents do when looking at it in Word. (I'm trying to create a "breadcrumbs" style list for each line item). I've tried using tabs as a delimiter for importing text into word, but it doesn't seem to recognize Word's tabs when applied through a numbered list.

View 4 Replies View Related

Creating List From Large Set Of Data?

Dec 3, 2013

I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.

I have attached a simple example to demonsrate what i mean.

in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.

I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.

View 1 Replies View Related

Create A List From A Large Table

Nov 21, 2009

I am trying to do is create a list from a large table (see attached). I have 2 columns (A and B) listing 'Asset' and 'Area' and then 2 rows (1 and 2) listing 'type of task' and 'task'. This forms a table with entries in the cells (from C3 onwards) denoting frequency of each task at each location, i.e. daily, monthly, 6 monthly and annual. This gives me quite a large table which is quite confusing. Can I create a formula to sort all the daily activities which will give me a list of the 4 corresponding properties 'country', 'location' (in columns A and B) and 'type of task', 'task' (in Rows 1 and 2)? Then do the same for the different frequencies populated in the table, 'monthly', '6 monthly' and 'annual' ???

View 7 Replies View Related

Large List Of Data In Sheet1

Apr 20, 2007

I have a large list of data in sheet1. The relevant columns contain a unique identifier (column 'N') and a value of either "True" or "False" in column O. Because I don't want users to have to trawl through hundreds of lines looking for the 'False' entries, I have a macro that copies only those records to Sheet3. In this sheet the user can set the 'False' value to 'true' but I don't know how to return the new value to Sheet2 (possibly for multiple records).

View 9 Replies View Related

Creating Specific List From Large Dataset?

Jan 2, 2014

We collect loan payments for 36 months from customers.

Column A lists 1000+ customers.

Column J lists the date we received payment 1 ... Column Q lists the amount we received on payment 1.

Column R lists the date we received payment 2 ... Column Y lists the amount we received on payment 2.

Column Z lists the date we received payment 3 ... Column AG lists the amount we received on payment 3.

This repeats for all 36 payments.

New customers are loaded in each month, so be aware that Column J, Column R, Column Z (and so on) have dates from 2011 and 2012 and 2013.

We'd like to create a list of all customers that have not made a payment for the current month as of a certain day (say the 12th). So this month, on January 12th, we'd like to search our data for all customers that don't have a payment listed between January 1st - January 12th.

View 14 Replies View Related

No SMALL And LARGE With SUBTOTAL On Filtered List?

Oct 17, 2011

I have sales data in columns, the right most column has values in it.

If I filter the data using the standard Filter, I can add only filtered records by using SUBTOTAL(9, filtered_list) and this gives me the answer.

How can I find the 2nd largest or second smallest ONLY from the filtered data without using a Pivot table?

View 5 Replies View Related

Find Large Value If List Is Having Duplicate Data?

Dec 30, 2011

I am facing problem in finding the second large value while my list is having duplicate data.

For Example

my numbers are:-
12,65,45,12,87,90,65,87

if I want to find out the top four value by using the formula:-

=large(A1:A8,{1;2;3;4})

the result is coming like: - 90,87,87,65

instead of this I want the result as: - 90,87,65,45.

View 6 Replies View Related

Calculations On Large List Of Data With Duplicates

Nov 4, 2012

(Excel for Mac 2011). The size of my data means am crashing by usual methods of sorting - I need a formula for this rather than pivot tables.

I have a list of c. 40,000 rows. Col A is client name (largely different but some duplicates); Col B is Policy type (eg Home, Motor, Travel) and Col C is earnings on that policy (eg -60). An example would be:

Name
Policy Type
Earnings
Johnson
Home

[Code]...

So I need to figure out (via a formula) two things:

1) How to count duplicate names (eg appearing 3x = 1 customer; appearing 2x = 2 customers; appearing 1x = 1 customer)

2) How to build a simple table to show the relationship between policies. This would have Motor, Home, Travel, Motorcycle as columns and also as rows - the data would then be how many policies and in each cross point?

I have tried just working the data using Countifs etc, but as soon as I apply it to the whole data set the machine freezes, presumably due to its size

View 6 Replies View Related

Data Validation In That Large Merged Cell Using List And =Cements

Mar 25, 2009

I am including a sample. On sheet 1 I am trying to do data validation in that large merged cell using List and =Cements. Why do I get the error that pops-up?

View 5 Replies View Related

Gather Countries From List Based On Sum (Index / Match / Large / Sumif)

Jul 2, 2013

I have a table of sales by month per account. Each account has an associated country, but there may be many duplicates of each country per month.

I want to create a formula which indexes the country field and gives me the country with the highest total sum for a specified date range. I'd like to be able to drag the formula down and have the country with the highest sales, then the second highest, and third highest, and so-on. Column ranges are specified below.

K - L - M - N
------------------------------------------------
Month
Year
CtryDsc
Rev

1
2011

[code].....

I was thinking of using an array function utilizing index, large, sumif, and match, but I am getting stuck. I'm honestly not sure if what I'm looking for is even possible, but in Excel, everything is possible, so there has to be a way!

View 4 Replies View Related

Finding Similar Value From One List In Another List?

Oct 7, 2011

On Sheet 1 I have a list of employee names (John, Bob, Ross etc...)in column A and in column B I have a list of employee bonus points (1, 5, 3 etc...). On Sheet 2 I have the same setup but the list of employees on sheet 2 is a lot longer than the ones on sheet 1, all employee names are on sheet 2.

I need a macro that will go down to each name on the list on sheet 1, column A, and copy the employee bonus points then go to sheet 2 and find the that employees name in the list and paste the bonus points in column B. This must be done until the last name on sheet 1 is found and all points are copied to their corresponding names on sheet 2.

View 3 Replies View Related

Finding Names Within A List

Sep 14, 2009

I am creating an interactive tool in Excel.

One of the Fields is "Employee Name"

I created a validation list that includes the names "Mary, Joe, Michael, and David"

When I plug in the name "Mary" I want to pick out her qualifications from a list I have created on a separate worksheet.


Passport
Mary
Joe
David

Lunch
Mary
Joe
Michael

Car
Joe
Michael
David

I want Excel to have the ability to find the name "Mary" from those three lists and reply back to me with the headers of either Lunch, Car, and/or Passport based on the lists Mary appears in. I want this to change depending on the Employee Name and I pick and the lists they appear in.

View 14 Replies View Related

Finding Top 3 Most Common From A List?

Apr 14, 2013

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.

View 1 Replies View Related

Finding From A List Within Spreadsheet

Nov 1, 2012

I have a spreadsheet with the first 17 tabs containing data and the last tab the 18th one contains a list in col a of 4,000 unique code items.

Is it possible to check if any of the codes items exist in any cell of the first seventeen tabs.

View 1 Replies View Related

Vlookup Not Finding Value In List

Feb 26, 2010

Excel 2002

I have a number (22.3) in a cell that is there from an Hlookup function. I am then using a vlookup to lookup the 22.3 (from the hlookup) in a column. The number is in the column, but the vlookup function cant find it. If I were to type over my hlookup formula so that I am hand-typing in "22.3" instead of it being their from the hlookup, then the vlookup works just fine. I hope that is not too confusing. I dont understand what is going on. I checked to make sure none of my cells are rounding and therefore creating a look-alike number when it really isn't and that is not the case.

View 9 Replies View Related

Large CSV File: Too Large To Open. Split

Aug 6, 2003

I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).

Is there a way to split ( ) this file so I can open two files instead?

View 9 Replies View Related

Finding The Biggest Value In Custom List?

Apr 26, 2014

I know how to make customs lists, but it's not getting me anywhere.

Let's say I have a lot of data containing values as follows and in the following list I sorted them from small to large:

5a
5a+
5b
5b+
5c
5c+
6a
6a+
6b
6b+
6c
6c+
7a
7a+
7b
7b+
7c
7c+
8a

and so on... In the list, the date isn't sorted from small to large, many values (if not all) occur multiple times and on occasion some values are added to the column. With that last part I mean that you can see it as a batch of 500, and if so far the batch only contains 450 values there will be (for the time being until items get added) 50 empty cells in the column as well.

You can't order numerically or alphabetically. I know I can make a custom list and then just sort according to that custom list so the biggest value would come out on top or bottom but that's not what I'm looking for either, as the data needs to be sort by another colomn (dates in chronological order).

So I'm just looking for a formule saying "return the biggest value if the order of things is as I typed above'.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved