Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Column keyword search using list of keywords Results

I have a column of 5000+ cells, each containing one of a possible 40+ keywords. I need a program that will search each cell in this column, search the cell for one keyword, if any, of a list of 40+ keywords, and if a keyword is found, the keyword would be inserted into the adjacent, right cell. If I'm asking too much, let me know. I'm great with Excel, but I'm not a VBA programmer... so I am limited in my ability to manipulate mass data sets.

Ideas, anyone?

I have created a workbook to track the macro-nutrient breakdown of my diet (grams of carbohydrates, protein, and fat). On one spreadsheet, "Food Database", I have a table with a list of foods I typically eat in Column A. Columns B and C give the category of food and the serving size. Columns D-G respectively list the grams of carbs, protein, fat, and number of calories for the serving size specified in Column C.

On a separate spreadsheet, "Daily", I track the food I eat each day. Column B lists the food eaten, Column C lists the number of serving. What I would like to do is search the "Food Database" for every food listed in Column B, get the carbs, protein, fat, and calories, multiply by the number of servings in Column C, and return 4 separate totals for the carbs, protein, fat, and calories for a single day.

Initially I attempted this with VLOOKUP but was unable to sum the values for each individual food into a total. Then, I started digging around for alternatives and came up with

=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX('Food Database'!$A$2:$G$59,,1),B$3:B$6,0))),INDEX('Food Database'!$A$2:$G$59,,4))

to return the total number of carbohydrates (the final "4" in the formula is replaced with 5, 6, or 7 for the protein, fat, and calories).

However, I have still not figured out how to multiply this by the number of servings eaten. I had attempted to simply insert C3:C6 into the SUMPRODUCT to multiply the results by a third array, but to no avail. My workbook is attached and I am more than willing to entertain all suggestions. The values in B15:B18 on "Daily" are the intended answers. Those in D15:D18 are where I'm at with the SUMPRODUCT formula I'm currently using.

Thanks in advance.

I'll try to describe all the relevant information...

Column A - contains comment fields, randomly containing keyword(s)
Column B - contains the unknown search formula, yet to be determined
RangeXYZ - contains a list of multiple keywords

I wants the formula in B2 to search the comment field A2 using the entire list of keywords found in RangeXYZ. The general assumption can be made that a comment field should only contain one keyword. I'd like A2 to result in a blank or "NA#" if nothing was found... or the keyword, if any keyword was found.

For example:
A2 = "The customer was happy about their widget."
B2 = Unknown search formula, resulting in "widget"
RangeXYZ contains = widget, newspaper, sports car, computer, calculator

I hope that I provided enough information...

Thanks for any help or insight...

I'll try to describe all the relevant information...

Column A - contains comment fields, randomly containing keyword(s)
Column B - contains the unknown search formula, yet to be determined
RangeXYZ - contains a list of multiple keywords

I wants the formula in B2 to search the comment field A2 using the entire list of keywords found in RangeXYZ. The general assumption can be made that a comment field should only contain one keyword. I'd like A2 to result in a blank or "NA#" if nothing was found... or the keyword, if any keyword was found.

For example:
A2 = "The customer was happy about their widget."
B2 = Unknown search formula, resulting in "widget"
RangeXYZ contains = widget, newspaper, sports car, computer, calculator

I hope that I provided enough information...

Thanks for any help or insight...

Hi - I have a set of data where column A has a list of places. I want to search this list of places for keywords to easily determine which rows contain a keyword. I have tried using a countif function such as =countif(A1:A20,"*"&B1:B5&"*") but all I get are zeros. Any help on how to solve this would be great as the larger set of data that I'm working with contains over 100,000 rows.

ColA
Places Keywords Result Vision Studio Music 1 Visions Barber Shop Beauty 0 Vista Grove Dance Centre Pizza 0 Visual Image Salon And Tan Pc Salon 1 Visual Therapy Of St. Petersburg, Florida Studio 0 Vita Park 0 Vitaganza Health Foods 0 Vito & Michael's Gourmet Pizzaria & Restaurants 1 Vito's Hairstyling 0 Vittone's Music 1 Viva Beauty 1 Vlasic Foods Inc 0 Vocelli Pizza 1 Vocelli Pizza 1 Vocelli Pizza 1 Vocelli Pizza 1 Vocelli Pizza 1 Vocelli Pizza 1 Vocelli Pizza #47 1 Vocelli Pizza #74 1 Vocelli Pizza Store 20 1

Hi,

After doing some research on the internet and on this forum I have come up with, put together, and modified bits of code that I hope will ultimately do the following:

Create new workbooks with data pertaining to a specific set of data.

For example:
On sheet one I have a range of data...(see attached spreadsheet for visual)
On sheet two I have a list of unique numbers. ID numbers, if you will.

What I am trying to do is cycle through the ID numbers and, lets say the first ID number is 12345...for each row in the master data that has the ID number 12345, I want to select it and copy it to a new workbook. Essentially, create a new range of data (Based on an ID number) and copy all rows that pertain to it to a new workbook. THEN, I need it to cycle to the next ID number on the list in sheet 2, and do the same thing again.

I have two bits of code that SEPARATELY, work.

The first code below goes through my list of numbers and stores them as a variable (I think...). I used the message box portion just as a test to make sure that it was actually working.


	VB:
	
 newTest1() 
    Dim cell As Range 
    Dim keyWord As Variant 
     
    Sheets("Sheet2").Activate 
     
    For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp)) 
         
        keyWord = cell.Value 
         
         'MsgBox keyWord
         
    Next cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The following code looks at the master data and searches for an ID number that I specified just as a test. (it worked). Ultimately, I want this ID number to change to the next one on the list of unique numbers after the code has run and do the entire sequence again.


	VB:
	
 Range 
Dim rngG As Range 
For Each c In Intersect(Sheets("Sheet1").UsedRange, Columns("b")) 
     
    If c = "12345" Then 
         
        If rngG Is Nothing Then Set rngG = c.EntireRow 
        Set rngG = Union(rngG, c.EntireRow) 
         
    End If 
Next c 
 
rngG.Select 
Selection.Copy 
Workbooks.Add 
ActiveSheet.Paste 
Range("A1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The following is what I get when I put the two together. Logically...I feel like it should work, but it doesn't. Instead, I get the following message. "Run-time error '91': Object variable or With block variable not set" with "rngG.select" being highlighted when I press Debug.


	VB:
	
 newTest3() 
    Dim c As Range 
    Dim rngG As Range 
    Dim cell As Range 
    Dim keyWord As Variant 
     
    For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp)) 
        keyWord = cell.Value 
        For Each c In Intersect(Sheets("Sheet1").UsedRange, Columns("b")) 
             
            If c = keyWord Then 
                 
                If rngG Is Nothing Then Set rngG = c.EntireRow 
                Set rngG = Union(rngG, c.EntireRow) 
                 
            End If 
        Next c 
         
        rngG.Select 
         'Selection.Copy
         'Workbooks.Add
         'ActiveSheet.Paste
         'Range("A1").Select
         
    Next cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any suggestions?!?! Any help is extremely appreciated.

Hi,

After doing some research on the internet and on this forum I have come up with, put together, and modified bits of code that I hope will ultimately do the following:

Create new workbooks with data pertaining to a specific set of data.

For example:
On sheet one I have a range of data
On sheet two I have a list of unique numbers. ID numbers, if you will.

What I am trying to do is cycle through the ID numbers and, lets say the first ID number is 12345...for each row in the master data that has the ID number 12345, I want to select it and copy it to a new workbook. Essentially, create a new range of data (Based on an ID number) and copy all rows that pertain to it to a new workbook. THEN, I need it to cycle to the next ID number on the list in sheet 2, and do the same thing again.

I have two bits of code that SEPARATELY, work.

The first code below goes through my list of numbers and stores them as a variable (I think...). I used the message box portion just as a test to make sure that it was actually working.

Sub newTest1()
    Dim cell As Range
    Dim keyWord As Variant
 
    Sheets("Sheet2").Activate
 
    For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
 
        keyWord = cell.Value
 
        'MsgBox keyWord
 
    Next cell
End Sub
The following code looks at the master data and searches for an ID number that I specified just as a test. (it worked). Ultimately, I want this ID number to change to the next one on the list of unique numbers after the code has run and do the entire sequence again.

    Dim c As Range
    Dim rngG As Range
    For Each c In Intersect(Sheets("Sheet1").UsedRange, Columns("b"))
 
        If c = "12345" Then
 
            If rngG Is Nothing Then Set rngG = c.EntireRow
            Set rngG = Union(rngG, c.EntireRow)
 
        End If
    Next c
 
    rngG.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Range("A1").Select
The following is what I get when I put the two together. Logically...I feel like it should work, but it doesn't. Instead, I get the following message. "Run-time error '91': Object variable or With block variable not set" with "rngG.select" being highlighted when I press Debug.

Sub newTest3()
    Dim c As Range
    Dim rngG As Range
    Dim cell As Range
    Dim keyWord As Variant
 
    For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    keyWord = cell.Value
    For Each c In Intersect(Sheets("Sheet1").UsedRange, Columns("b"))
 
        If c = keyWord Then
 
            If rngG Is Nothing Then Set rngG = c.EntireRow
            Set rngG = Union(rngG, c.EntireRow)
 
        End If
    Next c
 
    rngG.Select
    'Selection.Copy
    'Workbooks.Add
    'ActiveSheet.Paste
    'Range("A1").Select
 
    Next cell
End Sub
Any suggestions?!?! Any help is extremely appreciated.

Hello all,

This is my first post on excelforum.com - hopefully you all can help me out

I'm working with a data set about 7000 entries long, and I want to build a template to display every entry where a keyword match is found. I need to be able to search 2 arrays, and if the keyword is found, then display the column next to it. My attached workbook uses the LEFT and INDEX functions to do this.

The problems i'm running into:

-If I use an array 2 columns wide, then I get duplicates.

-If i use an array 1 column wide, then I can only get 1 of the list of names

How could I do this search on both NAME columns, yet not display duplicates?

My only other thought is to concatenate both columns into one, and use SEARCH instead of LEFT, though I fear that will display too many results to be practical.

Thanks

Hi,

I've got an Excel document with over 7K entries and I need to search the data to find where ever a certain keyword is mentioned and copy the entire row into a separate sheet within the same workbook. However, I have a list of around 50 keywords so doing this using Ctrl+F would take some time. I'm new to Macros but I think that a macro may be the best solution for this task.
The data is in Sheet1 and the particular column that needs to be searched is "K". The contents of column "K" is made up of words and sentences. Sheet2 is where I would like the found results to be copied to. The list of Keywords that need to be searched for are located in Sheet3, starting from Cell A1. Below is a list of the sort of words that need to be searched for:

Jaguar
Landrover
Tata Motors
Volvo
Audi
Mazda
Vauxhall
Opel

Does anyone think they can help me create a macro to carry out this task or know of a better solution for doing it?

Any help would be much appreciated.

Thanks,

I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution...

I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust).

I sort the list so that the all names that are the same (ie John Doe) are together.

A short example list looks like this:

Jeremy Apple
John Doe
John Doe
John Doe
Jimmy Kravitz
Jimmy Kravitz

In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it).

Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on.

Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...

Thanks,
Nate

Hello everyone,

What i'm trying to do is simple (I thought); but I have yet to find a way to make it work. Ive searched your site, Google, VBA examples - and such. I hope someone here can help, I have some excel knowledge, and some background programming know-how.

I have one workbook, and 2 sheets. On one sheet is a list of Addresses (lets call this Sheet1), and another for Customer Information (Sheet2).

I want to copy the address data from Sheet1 into sheet2 using a keyword in sheet2. I'm looking for the data in Sheet1 (address info) to be merged into one cell, it looks like: Company | Address | City | State | Zip (as columns).

I want to reference the company to always = Address / City / State / Zip when the Company keyword is used in sheet2.

In a nutshell, condense address data from sheet1 into a normal address format, and copy it into sheet2 where the fields correctly match. The address will be entered into a cell next to the Company name.

I'm including a sample file, I hope someone can help or at least point me in a direction I should go with this

Thanks
ae

I searched the forums but was unable to locate anything regarding this ...I was probably using the wrong keywords in my search because I recall solving this problem before using advice I found on this board.

I have a user with a large listing of names and addresses. The problem is that she entered the complete name in one cell, and the complete address (street, city, state, zip) in a second cell. Ideally, she would have entered street in one column, city in another, and so on.

I'm trying to recall how to copy only a part of the cell using a wildcard so I can reformat this worksheet to better suit our needs. For example, I would like to take a cell that has the following data:

1234 anystreet, anytown, anystate, zip

and be able to just copy the "1234 anystreet" section to a different cell.

How can I do this?

I'm trying to find a method for advanced filtering using multiple criteria.

Sheet 1 Column A contains the names of seminars. Column B contains keywords for those seminars. For example, in cell A2 we have a seminar for Personal Financial Independence. The key words for this seminar are listed in cell B2 as "Finance, Budget, Personal".

This is a huge list with multiple seminars. Some of the seminars have 1 keyword, but others (as is the case listed above) have multiple keywords each separated by a comma.

Sheet 2 contains only a list of keywords, each of which are listed separately in their own cell. So for the example above where "Finance, Budget, Personal" is listed in a single cell on Sheet 1, each of these words are listed separately on Sheet 2.

What I'm trying to do is create a list box where the source is Sheet 2 (list of individual keywords). Based on the selections, the list of seminars on Sheet 1 is filtered. The problem that I can't get past is the fact that there are multiple keywords in a single cell on sheet 1.

For example, if someone wants to search our list of seminars based on the keyword "Finance", I need a filter that will search for the word "Finance" in the keywords column on Sheet 1. That means, that if Finance is only 1 word in a cell that contains 3 or 4 different kewords (separated by commas), I need this seminar to be listed. Likewise, I would like the ability to choose multiple keywords from a drop down list or list box. So, if someone wants to search "Finance", "Legal" and "Senior Citizens", all of our seminars would be filtered based on these keywords being found in the keyword column on Sheet 1.

I'm not sure if this is possible, but it would be incredibly convenient for us if we could establish this process.

Any help/direction you can provide would be extremely helpful.

Thank you.

I would like to create a spreadsheet called FAQs that has 2 worksheets.

A 'faq' worksheet will have a list of all of the faqs and answers to
these, possibly a third column that has 'keywords' in it.

A second worksheet will be called 'search' and should have up to three
cells where the user can type in keywords to search for.

I want these keywords to then look at the faqs and grab any relevant
rows, displaying them in the 'search' worksheet.

example.

FAQ sheet

faq 1 ~ How do you do this? ~ You do this by pointing your mouse and
clicking the button.
faq 2 ~ How do you do that? ~ You do that by clicking the button.

SEARCH sheet

If you enter the word 'mouse' into the first search cell, the sheet
will display the text for faq 1.
If you enter the word 'button' into the first search cell, the sheet
will display the text for both faqs 1 & 2.
If you enter the word 'mouse' into the first search cell, and the word
'button' into the second search cell, it will display faq 1 as a match,
and faq 2 as a partial match.

I'm not sure of the cleanest and easiest way to do this, purely using
excel and not Access. Any ideas on structure and formula/VB needed
would be greatly appreciated.

Thanks

Mxx

--
murphyz
------------------------------------------------------------------------
murphyz's Profile: http://www.excelforum.com/member.php...o&userid=20624
View this thread: http://www.excelforum.com/showthread...hreadid=355854

Hi there,

Some great posts here -- been reading the last 30-40 minutes and picked up some really useful pointers. I searched the forum looking to see if someone had already asked something like this, and while there are similar posts, nothing seems to match exactly.

What I am looking to do is input a large list of text keywords into excel (say approximately 5000 words) and have Excel group them by like phrases. However, I don't want to supply the "seed" words to group by, rather I'm looking for a macro to do this grouping automatically for me based on a combination of like continguous words common between the rows as well as the length of the like continguous word phrase....Yikes, that reads kind of confusing. Let me try to give an example...

Let's say I have a list of keywords that looks like this:

error in microsoft windows browser program
error in microsoft windows
error windows browser
error windows browser program
error in windows browser

Pasting this list into Column A, I'd like Excel to be able to sort these keywords (into separate columns or worksheets) based on criteria above. So running the above list through the macro, the macro would logically break apart the list like this........ note the subphrases in quotes are the continguous words common between the rows....

"error in microsoft windows" (4 word phrase, 2 occurences)
error in microsoft windows browser program
error in microsoft windows

"in microsoft windows (3 word phrase, 2 occurences)
error in microsoft windows browser program
error in microsoft windows

"error in microsoft" (3 word phrase, 2 occurences)
error in microsoft windows browser program
error in microsoft windows

"error windows browser" (3 word phrase, 2 occurrences)
error windows browser
error windows browser program

"windows browser program" (3 word phrase, 2 occurences)
error in microsoft windows browser program
error windows browser program

"microsoft windows" (2 word phrase, 2 occurences)
error in microsoft windows browser program
error in microsoft windows

"windows browser" (2 word phrase, 4 occurences)
error in microsoft windows browser program
error windows browser
error windows browser program
error in windows browser

"error in" (2 word phrase, 3 occurences)
error in microsoft windows browser program
error in microsoft windows
error in windows browser

So above we have 8 subphrases (the ones contained in quotes) that are the phrases the lists are grouped by. Now obviously there is overlap between the lists... So in addition to performing the above logic, the macro should ideally be able to remove duplicates. This is where the length of the contiguous word phrase comes into play... If a duplicate keyword is found between two or more sublists, the macro should delete the keyword from the sublist with a smaller contiguous word phrase length.

Again looking at the example above, notice the first two sublists:

"error in microsoft windows" (4 word phrase, 2 occurences)
error in microsoft windows browser program
error in microsoft windows

"in microsoft windows (3 word phrase, 2 occurences)
error in microsoft windows browser program
error in microsoft windows

The first sublist is grouped by a 4 word phrase, the second is grouped by a 3 word phrase. The macro should remove the duplicate keywords from the second sublist because it's grouped by a smaller word phrase than the first sublist...and obviously remove the duplicates from other sublists that are also grouped by a smaller word phrase than the largest one.

How in the world can I do something like this? Is this even possible? Please let me know if anything is unclear, I'll do my best to clarify.

Thank you in advance for any help,

- Dave

Hi all -

This is a continuation of a prior 'solved' problem (thank you Haseeb A!). I've now been asked to provide a further data parse and I think I'm going to have to use VB to get it - and those skills of mine are old!

The data is proprietary, so I can't share it. I'm going to attach a fake dummied up version. Essentially, here's the skinny:

Column A has the name of a person. Columns B thru D have string data with a keyword buried in it, such as Steel Support Beam ("support" is the keyword), Driver Clamp ("clamp" is the keyword). This string data may recur in a row, but I only need to count the occurrence of the string once. I have a formula that performs the count already, thanks to a previous posting on this forum. The new wrinkle: In addition to the count of the individual strings, I now need to know the count of occurrences of each keyword per person.

In essense:
Joe: Support = 2
Clamp = 3

The dummy sheet I've attached tries to show this, and show the data I'm after. I'm happy to do a script but, as I say, those skills are old - so I could use some help!

Happy to provide further explanation if needed. Thanks much!
Rachel

EDIT: I'm quite comfortable with Pivot tables, but I've yet to see a way to search for a string via Pivot table. If the resulting output from a vb script does nothing more than give a listing of the person's name, and then a listing of the keyword next to it, that's fine. I can turn that into a pivot table.

Hi. I can't figure this out even after searching this forum with keywords such as "font color change macro"

I have text in Column D. It consists of phrases such as:
JUL 2009 EXAM
FEB 2009 EXAM
JUL 2009 LIST
FEB 2009 LIST
JUL 2009 OUTLINE
FEB 2009 OUTLINE

I need a macro to change the font color of the text in Column B of each row based on the text in Column D. If the text in column D contains the word "EXAM", the text in Column B for that row should be Red and bold. If the text in column D contains the word "LIST", the text in Column B for that row should be Blue and bold. If the text in column D contains the word "OUTLINE", the text in Column B for that row should be Green and bold. I know I can do this with conditional formatting, but it must be a macro that actually changes the properties of the text in Column B since I use Excel spreadsheets to do batch search and replaces in Word documents with a program called Useful File Utilities and it does not recognize the conditional formatting. I only want to change the font color for text in Column B. The other columns should stay the same. Thanks.

HELP!!! I suck at this.
I am using excel 2007 for database of a large e-commerce site.
I am creating search-able attributes from keywords found in description, title and meta-data columns.

I am using the following function in "column X" to search 4 columns (see formula) for specific music styles.
I have hundred of searches to do and this formula works but is extremely time consuming.

It would be cool if I could search for multiple criteria simultaneously and have the columns created if a "true value" is returned.
i.e Search columns A - C - D - F - H for all music style terms and output the "true - value" to separate columns. Which I will combine later.Search for "Rock" (output to column X)search for "jazz" (output "true" to column Y)search for "Classical" (output "true" to column Z)And So on
Creating the columns via the macro is not essential, if I must first create the empty columns and make sure the macro outputs correctly, so be it.

I don't know much about macros but from what I have read it seems like the way to go. I would then be able to use the maco's and create a template and use them for each csv file I receive from different publishing companies.

Any suggestions would be extremely appreciated,
McMasters

sheet 1 contains relevant database info and the functions I am currently using.
Sheet 2 contains a non ordered list of search criteria that I will be using in the macro.

Hi Guys,

I wondered if anyone could possibly help me?

I am trying to do a SUM on values that have the same keyword. For example, I have a list of keywords in a column, and in the next column the numbers of searches for this keyword. Some of the keywords are duplicates so i want to add the numbers together.

so for example;

test-keyword | 93
test-keyword | 12

I want to be able to add 93+12 together because the keyword "test-keyword" are duplicates.

Is there any way to do this?

Thanks

Mike

----

UPDATE: Solved this myself, I needed to use the "consolidate" button in excel 2007.

Allow me to apologize in advance if this is covered elsewhere. I just have no clue as to what keywords to use to search the forum. I have a list activities in column A. I have a list of years in Row 1. Say for example, I'd like to report all the activities required in 2005 in sheet 2. In my sample, Activities 2, 7, and 10 are required to be performed in 2005. Is there a formula I can use to do this without using programming? Thanks in advance.


No luck finding an answer? You could always try Google.