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..
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example: type value A 15 B 6 A 21 C 7 B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
I need to generate a form letter from values on another sheet. I'm using vlookup and as you know, for duplicate values, only the first reference is given. I have several duplicates and need to pull the correct row data for each instance. I would need to replace each vlookup with the proper formula in my letter.
I have a list of data that currently has a conditional format on it of =COUNTIF($F$2:$F2,$F1)>1 so that it will highlight the duplicate but keep the first entry blank. I wondered whether there is a way to identify the last duplicate in the list. i imagine this could be done in a column say with an "L".
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 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?
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?
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.........
I'm evaluating the effectiveness of a keyword list we use to identify certain, high priorit,y medical situations from a string of text.
So, I have a column on a sheet of data (EIS Report!"B:B") in this case, and a seperate column on another sheet with keywords (Keywords!"A2:A47").
I use the formula ={OR(NOT(ISERROR(FIND(keywords!$A$2:$A$47,$B2))))} to identify if any of the keywords exist in the string. This works well but I would like to improve my work in two ways:
1 - Identfiy, in another column on the EIS report sheet, which keyword it is that's been found within the string, 2 - On the keyword sheet, add another column of words, which if found within the string, would act as an exclusion.For example, I might use "STAB" as a keyword to look for "Male stabbed" etc, however might want to exclude "STABBING" to stop the solution triggering on "STABBING PAINS IN ABDO"
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).
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.
I wanted to know if there was a way to find the Maximum value given a number of conditions are satisfied.
Similar to COUNTIFS and AVERAGEIFS, where you state the data range to use, the condition's range, the condition, then the next condition's range, the next condition, etc....
Here the maximum value is 212, Is there any way to find its corresponding date. Date in Row(A1:A5), Values in Row(B1:B5). Tried my best to get the date; but no way.
I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions.
So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value).
The content of Cell A1 looks like this attccggttaattcccccaaaattt (only a,t,g,c -nucleotides). I want to know the max times C occurs in this cell and the position from the start. like that a, t, g.
here the answer is 5 times and distance is 13 from start.
I am looking for a way of finding the maximum value in column H for each row in a pre- filtered range using VBA.
For example, the result of of filtermacro1 may return several records derived from a database of several hundred records. In the example below, the name of the person is in column C, followed by their H column value.
Audrey Perkins, .5 Marc Bloomberg, 2.0 Matt Phillips, .5 Paul Pachson, 2.0
However, the H column value may not be the maximum value for the rows above, as there are other records for Audrey, Marc, Matt & Paul in the database.
If the names are matched to the max value in column H for each record it should read
Audrey Perkins, 1.0 Marc Bloomberg, 3.5 Matt Phillips, .5 Paul Pachson, 6.5
How would I query only the names in a pre-filtered range and obtain the highest value in column H for each ? These values then need to be copied to H7 in a sheet called Detention Register.
I have a list of data for a frame in a building. I used these forums to come up with a sort that will take the data and put in on seperate sheets based on the floor of the building. I need to now go through each "floor sheet" find the maximum values and paste them onto a Report sheet. This has to be done within a macro stored in my Personal.xls folder as the file with data is coming from another program and is always changing. Also, the sheet names wont always be the same as the format might be different like third, or 3rd, or "third". Here is the code for the sort macro, I am sure some of you will recognize it:
Public Sub Unique_Record_Extract() 'extracts unique records from Column A and copies those records to a new worksheet Dim My_Range As Range Dim My_Cell As Variant Dim sh_Original As Worksheet Dim Cell_Sum As Long Dim i As Long 'turn off interactive stuff to speed it up Application.DisplayAlerts = False Application.Calculation = xlManual Application.ScreenUpdating = False 'Delete All Blank rows if cell in column A is blank On Error Resume Next Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete ActiveSheet.Name = "MainSheet"............................
I spent couple of hours trying to figure this out, but without success. Please check the attached example. I need a formula that will find the max value (number of views in English anguage) in the column "I" and display the corresponding "Headline" value in the column "B". In the example it is "Headline 5".
I can not change position of the columns, i.e. "B" must remain "B" and "I" must remain "I". I experimented with Index and vlookup but to no success. I have outlook 2003.
I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
How do I find the minimum and maximum numbers in a table if I do not know the size of the table but can assume the user will choose the first cell (upper left cell) of the table and run the macro?
