I have a cell with a long list of phrases all separated by commas.
So it would appear, chicken, beef, steak, soup, fish
I want to create a rule in a cell to identify if one of those phrases comes up - so if chicken comes up then a 1 would appear.
To make it interesting is it possible to grab two words from the cell, so grab the words chicken and beef from a mass of words in one cell and bring it across.
I have these cells which contain huge amount of text and preferences and I am looking to identify these entities on if they contain these words.
Text configuration - how can I tell a cell to pick up a phrase from one cell, but if that phrase is equal to one particular phrase, then produce a blank.
i have a spreadsheet where I need to count up how many times a particular phrase within a text string appears. The text string will be duplicated many times throughout the spreadsheet.
For example :
Miss X was at work on Saturday Mr XX was at work on Saturday but not Tuesday Miss Y was at work on Tuesday
So I would like to count up how many times "work on Saturday" appears in my spreadsheet, and then as a seperate query, how many times "work on Tuesday" appears.
I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
I have a list of variables created by a user like this:
BAND ID START END Band1 06:00 08:59 Band2 09:00 15:59 Band3 16:00 17:59 Band4 18:00 22:30 Band5 22:31 24:29 Band6 24:30 05:59
Seperately I have data from a third party like this:
DATE TIME VALUE 18/05/08 17:22 10 19/05/08 02:15 1 20/05/08 07:14 5 20/05/08 21:36 15 20/05/08 25:43 1 21/05/08 19:15 15 22/05/08 18:26 16 22/05/08 24:59 2 etc.
I need to be able to identify which BAND ID belongs to each TIME in the list so that I can sum the VALUE figures for each BAND ID (e.g. the first time given,17:22, would belong to Band 3). have tried using LOOKUP but I can't seem to get it to work. Additionally, I have a problem with the 30 hour clock. Would I have to stick to a 24 hour clock? This would mean that I would have to find each time from the list that was between 24:00 and 05:59 and allocate it to the previous day because it is important to keep the values that I ultimately derive in the right day of the week.
Here is the deal I have 4 columns. Each line gives you the following information: The Id is the record number, Code_Name is a code for each Fox in the study, date and area is a sub area in a bigger grid. Basically I have an area divided by squares and every time a marked fox enters in one of my squares a new line in the data is created. What I won’t to know is if a fox when in my area of study will return to the same squares or not.
ID
CODE_NAME
Date
Area
Logical 116
SSS1 02-Jan-09 1A1
273
RRR1 02-Jan-09 2A2
2959
BBB1 02-Jan-09 1B1
2959
What I’m trying to achieve is a 5th column were ill get a logical value of TRUE or FALSE if, for each fox in the next available record a fox went back to the same square or not. So if you check for the fox RRR1 I have 2 records one in 2 of January in area 1A1 and a second in 7 of January in the same area. For the Fox BBB1 you will see that she was always in different areas and for CCC1 she only came back to one square.
The problem is I have over 400 fox’s and 12000 records and I’m trying to get a way of doing it automatically.
I have a number of workers in one column and how many dvds they have sold for the week in another column. how would I automatically identify the worker from the worker list who has sold the most dvds in that week? is there a formula for this?
I would like to identify duplicates in a list using conditional formatting in Excel 2007.
I have tried choosing to identify duplicates using the formula that I have found on many threads throughout the message board:
=COUNTIF($A$1:$A1,$A1)>1.
This function works up to 15 characters in a cell, but Excel seems to be treating all digits after the first 15 as the same, resulting in a "fuzzy match" where I want an exact match. Many of the values in my list are 18 characters long, in text format to prevent rounding.
I've noticed that Excel treats the 18-characters values the same way when sorting; for example, it treats these two values as the same:
'234567891011121314 '234567891011122413
Is there a way to force Excel to examine those last four digits for the purpose of sorting & identifying duplicates?
with creating this macro to identify duplicate text strings in a column, which is great.
But, I'd like to be able to identify them by changing the text of the subsequent duplicates that are found.
For example, if 3 cells in a column are 1111, I'd like to add a string of text to the end of the 2nd & 3rd cell, but not the 1st cell. 1111 dup-1111 dup-1111-dup
This will enable me to sort the column and find the duplicate easier than just visually.
Sub color_dup() Dim r As Range, rng As Range, Col As String Col = "d" Set rng = Range(Col & ":" & Col) rng.Interior.ColorIndex = 0 For Each r In Range(Col & "1", Range(Col & "65536").End(xlUp)) If Application.CountIf(rng, r) > 1 Then r.Interior.ColorIndex = 6 End If Next End Sub
I have a database of approximately 250000 email addresses and I would like to see if there is some way to use Excel to analyse the strings in order to identify potential 'non-unique' individuals.
For example, if I have the following email addresses: 12345@example.com 12346@example.com 12347@example.com or example1@example.com example2@example.com etc
Is there some way of extracting common text strings, lets say where there is a difference of 1 character / digit between strings (rather than alphabetising and searching manually)?
I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.
If the macro finds the text in column 2, it would delete 1, 2 and 3.
I need to have a second macro that performs a very similar function. it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.
I have three columns. Column 1: A list of items with a very long text description in each cell Column 2: The Key phrase found within the long description in the cells of Column 1. (The key phrase is different for each cell). Column 3: Custom shortened description in text format. Column 4: Formula to return the Custom shortened description, equivalent to the item in Column 1
How do I create a formula in Column 4 so that Excel searches for the Key phrase found within the string of Column 1 and returns the custom description in Column 4?
i have a list of information, and from that i want to extract a certain piece from that information and out it in a new cell... eg. 'KLM00506', 'KLM00409-10', 'KLM00821-9' etc
as you can see, the info is not in the same length what i need is to take out the FIRST 6 characters and have the remaining to be in a new cell
anyone has an excel formula that i can use ? not really interested in code, cause in this particular worksheet, everything is at its' place, no code required
In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.
Let's say this is my data. It's already been sorted so that like entries are all grouped together.
Ex1 Ex1 Ex1 Ex2 Ex2
I need to make a macro that numbers my data like below.
1 Ex1
2 Ex1
3 Ex1
1 Ex2
2 Ex2
Essentially, my data is many columns and sorted on a bunch of different levels. I need a quick way to assign a rank to every row in the same group, to see at a glance which performed the best.
I am trying to write a code for VBA code for find and replace, I want to find a particular phrase (i.e. 1. Value Added Processing) which is all in one cell and replace it a range of cells of other cells which is contained on a different sheet.
So basically the original 1 cell would be replaced for anywhere between 1 to 20cells. Depending upon what I type in.
I try to search for only one word/phrase in range of cells. I would like formula to return the cell value that contains work/phrase I asked to look for.
For example: A1 contains "Apple", C1:3 contain "Cherry juice", "Apple pie", "Orange bonbon". I want D1 to check which cell from range C1:3 contains word/phrase from A1 and show it to me - "Apple pie".
I would like to search for a keyword or a phrase in a website using excel.
Generally we copy the keyword or a phrase and paste it in google and go for search. but i wanted to do this using excel as i have to search same pharse in multiple websites.
I have a large keyword list in a sheet called "AllKWs" In ColA from A3 downwards).
What I'd like to be able to do is this, which I'm sure will be complicated, but I will explain.
Say keyword phrase sheet has 25,000 rows of data (could be more/could be less). I click an assigned macro button. A pop up box appears. I type in a word or words I'd like some info on, so for example I type in a word or words like "car rent"
It then returns for me on a new sheet called "Multi Keywords" a lot of data on this sheet, which would hopefully be as follows:
All Row 1 will contain Column headings All row 2 will contain Total Counts (I'll explain in a minute this row)
So, all data to be returned from Row 3 downwards. OK, as to the data to be returned. All returned data In all Columns to show data in descending order by No of occurrances/appearances
Col A (From A 3 downwards) = The actual number of 2 word appearances (In this example that contain the words "car rent"
In Col B = All 2 word Phrases Containing ("In this example "Car Rent") (As a note, In this example, ColA (CellA3) could only show the number"1" and ColB (B3)could only show the phrase "Car Rent") once. (As there isn't no other possible combination).
In Col C =The actual number of 3 word appearances listed in descending order That contain the word "Car rent"
In Col D =All 3 word Phrases Containing "Car Rent"
In Col E =The actual number of 4 word appearances containing "Car Rent" listed in descending order
In Col F =All 4 word Phrases Containing "Car Rent"
In Col G =The actual number of 5 word appearances listed in descending order
Actually if anyone can crack this I really do take my hat of to them. OK,A few more points,
Cells B2,D2,F2,H2,J2,L2,N2,P2,R2, All contain the word "Total:" and if the macro can fill in the number as appropriate.
So for example Cell L2 (For 7 word phrases) would say something like "Total:42" (If in Col L From L3 downwards the macro found 42 7 word phrases that contained the words "Car rent"
Ok, Cells A2,C2,E2,G2,I2,K2,M2,O2,Q2 All these cells will contain the word "Total". So these cells would list the combined total number of occurrances of all the phrases. So for example cell K2 might say "Occur:324" as the total number of occurances of 7 word phrases that had the words "Car rent"in.
OK. as an example, I will post a code that Jindon wrote for me sometime ago. I'm posting this now, as it is very similar in what I would like this macro to be able to do, and might help as I'm sure this 1 will be complicated. This 1 looks for a phrase, returns by No of occurrances etc, but for all the combinations (Word lengths) within the Keyword phrase list, rather than what I'm asking for now, which splits them into Number of words columns.
Here it is anyway:
Sub NicheKeywordFinder() Dim a, dic As Object, X, myTxt As String, b(), c(), n As Long, i As Long, e, s, myTotal As Long myTxt = InputBox("HuaHinCarRental - Niche Keyword Finder") 'change to suit If Len(myTxt) = 0 Then Exit Sub Set dic = CreateObject("Scripting.Dictionary") dic.CompareMode = vbTextCompare ReDim b(1 To Rows.Count, 1 To 1): ReDim c(1 To Rows.Count, 1 To 3) With Sheets("All KWs") 'change to suit a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value End With
Each cell in Range("A1:A2000") contains a remark, each phrase or remark is Between 5 & 70 characters all written without line feeds (carriage return,i mean Alt Enter) Just spaces between words. What I would like to do in every cell is to force a line feed (Alt Enter) every 10 characters, and if the 10th character happens to be in the middle of a word I want the line feed to be inserted at the end of this word. Note that the phrases are not necessarily multiples of 10.
I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB: Sub Macro1() ' ' Macro1 Macro ' Range("B10").Select Cells.Find(What:="as % of Revenue").Activate Range("B20").Select ActiveCell.FormulaR1C1 = "=R[-1]C/R10C" Range("B21").Select End Sub
I am trying to search for a phrase in a particular column but the excel search function is returning !Value as an error...
Both fields are text so nit sure what is wrong here.
I have a list of cities that i want to cross reference across a cloumn of notes on another sheet. So, I really want to search for the city (Sheet1 Cell A1) in Notes Sheet2 Cell A1) and I want to return it in Sheet1 Cell B1 Whether it exisits in the notes. I then want to copy this down my list of cities in Sheet1 Column a.
