I have a list of strings in column A like this:
/Image in Filed/October 08/airline/arrival guides
/Image in Filed/October 08/movies/New Releases
/Image in Filed/October 08/tv/Escape
I need to put to the variable string which is after last "/" in the cell A#
in this case: arrival guides, New Releases, Escape
I sent this before but the formula I was given wasn't correct (as you will see from the file). In Column B of the second sheet (Data) of the attached file, I want to bring back the relevant name form the first sheet (List), which involves searching through the long descriptions from column A on the Data sheet for the name on the List sheet.
View 2 Replies View RelatedI have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
Need to do (preferrably as a UDF so I can call it many times and build the macro I really need) the following string testing macro. For a given column (B) - look to the field immediately to the left for a text string. This string will be searched for a list of other strings contained in Column D ( cells D1:D5). We want a case insenstive SEARCH (or find I suppose in macro terms) to return a true if the strings are found or false if it is not. Example test string in cell B1 would be:
I am a string of test data to check.
Terms in D1:D5 might be:
dog
cat
bird
data
fowl
Cell B1 should return a TRUE as a match.
I am trying to create my own budgeting tool. I have most of the tracker set up but I hit a stumbling block. I downloaded transations from Bank of America as a CSV file into excel. I want to categorize these transactions.
I created a keyword list that has words like "Starbucks" "Exxon" etc. Each of these keywords is then listed next to a category like "food" and "gas". So I want a formula that will compare the transactions to my keyword list and return the category type. Here is an example transaction:
CHECKCARD 0256 STARBUCKS USA 00029801 GAINESVILLE VA
I have a keyword "Starbucks" in f2 and "food" in g2.
I am trying to match some strings, to see if an entry is already included in a master list or not. I'm running into problems with some cells that have the EXACT same string, from what I can tell (a text ticker symbol) yet a MATCH or VLOOKUP won't return anything. Has anyone ever encountered anything like this? Worksheet attached with an example
View 3 Replies View RelatedI need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
Lets say I have a column of data and there are many sub parts.
A
John
Apples
xxx
Banana
Peter
Apples
xxx
Banana
yyy
How do I modify the range. find to locate the 2nd "Apples" , 29th "Banana" and so on or is there another method ?
I'm a novice trying to figure out the following:
I have a column, where each cell in the column has one of the following "ratings" entered as text:
excellent
good
fair
poor
How do I count the number of times "poor" AND "fair" are listed in the column? I used the following countif forumla to count the # of occurences of the word "poor":
=COUNTIF(SurveyData1stRoundSortedbyDoc!Y2:Y30,"poor")
...but how do I adjust the formula so that it counts the # of occurences of the word "fair" AS WELL?
How many text strings in a reference column appear in paragraphs of text listed in another column within excel.
So column A contains text strings such as:
{{Advisor_Signature__c}}
{{Advisors_Initials__c}}
{{Advisors_Job_Title__c}}
{{Age__c}}
And I need to see whether any of these appear in cells in a reference column G. If they do, I would like to return 'Used' into column B.
An example of the type of text in each cell in column G is:
"If you have any questions regarding your offer, please contact me. For any questions regarding your benefits, payroll or company policies and programs, please contact HR. Sincerely, {{Advisor_Signature__c}} {{Advisors_Job_Title__c}}"
I don't seem to able to search for a text string across multiple reference cells.
I have a long list of products in an Excel column. I want to count the number of unique items in the list (the cells contain strings, not values).
I know I can use the "Remove Duplicates" tool in the Data menu, and then use a Count function on the resulting list. I wondered if there was an Excel function or formula that would do it, similar to the Frequency function that does the same thing with a list of values.
I have a set of data in Which in column A is the name of organisation.
If string in col A is longer than 50 I need to split in and put in col B.
That would be simple however I need to do it in a smart manner: i.e. cut it to the nearest full word.
Example:
THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
Incorrect; length = 98
THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT
Correct; trimmed down to 48.
My question is about formula that can detect spaces and depending on those trim the string down adequatly:
to 50 if 50th char is preceeded by space; if not then check where is the next space going towards left. Once you find it cut the string there.
What formula I can use to get desired effect?
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'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
View 5 Replies View RelatedI have a list of around 50000 names. I need to count how many are unique.
I am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View RelatedI have a find and replace function that removes + smybols from a coloum of strings. How can I remove the first instance of a space (if it later contains a +, too?
E.g.
Before: [ +Test +Test]
After: [+Test +Test]
Code:
Columns("D:D").Select
Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.
View 14 Replies View RelatedI am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID
VOL
001
01
001
05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique)
VOL (concatenated)
001
V01, V03, V05
002
V01, V04
003
V06
PS: I have people using this table with office 2003, so compatibility is necessary...
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies View Related1. I have a list of several items in sheet 1. Lets say 30-40 rows.
2. I do a frequently update of this list.
3. In sheet 2 I want to have a copy of this list in row 1 from column A to Column x (depends on how long the list in sheet 1 is.)
4. When I change the list in sheet 1, could be in first, middle or last row- mayby several rows. The list in sheet 2 should be updated
5. The update of sheet 2 should insert a new column on the right place, according to the list in sheet 1.
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number.
Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits)
PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error.
2- I have another list with 2 columns which arranged in order. I mean:
A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column.
My request is I want to make this list 1 column as arranged.
A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc.
3- Macro will add a new line to the end of the above list with this format:
Z00001xxxx
Z00001 is static
xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits.
Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)
I'm trying to come up with a single formula to create a single column list from a table with blanks.
a
b
c
d
e
f
g
To
a
b
c
d
e
f
g
I know I've done this before but having trouble visualizing today.
If I have a table as noted below with the following assumptions:
- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.
A
B
C
1
Item
Calories
Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
a macro to convert this;
a
b
c
d
[Code]...
Into this;
x
a
9
x
b
[Code]...
So far I have the following, but this is not quite right!
[QUOTE][Sub ConvertRange()
Dim targetRowNumber As Long
targetRowNumber = Selection.Rows(Selection.Rows.Count).Row + 2
Dim col1 As Variant
[Code]...
/QUOTE]#
If you open the attached file you'll notice that once clients are entered into the name field of the master worksheet that a new worksheet is created with their name based off a master template. What I need now is a way to create a "on-demand" "click" macro that will allow the user to select a month (Jan-Dec) based on my master tab and autocreate a summary worksheet named that month with each client listed on the Team Roster worksheet in the next available column of the worksheet that was just created.
macro generate a prompt to list the months to allow updates frequently.. so if the same month was run twice it would overwrite the previous.. the woman we're doing this for isn't very excel literate...
NOTE: As you enter names on the Team Roaster sheet it will autocreate tabs.. test file has MrExcel as the worksheet, if deleted the sheet will delete automatically.
I need the searchable list box to return data one column to the right of the "searched" column data.
This works mostly with Data Validation.
Named range "validationlist" with "=OFFSET(Choice!$AC$4,,,COUNTIF(Choice!$AC$4:$AC$14,"?*"))"
D9 has a drop down control. Enter the cell and type "br" and click the drop down arrow. Mouse to choose an item from the list box. If you chose "brown" , it will be entered in the cell. The returned item should be "banana" from the other column. How to do this?
Once working the control needs to be used across the row in more cells: E9, F9... all the way out to W9. And there has will be a Lookup to pull data from elsewhere above D9.
This worksheet ultimately needs to be uploaded to Skydrive and used as a collaborative worksheet as a 'web app'.
See attachment and formula output below.
[Code]............
searchableListBox-EF1.xlsx‎
I am trying to create a macro that converts a large list of Customer details from a vertical to horizontal format.
Each Customer has a unique sequential number running from 1 but the details are not always the same (e.g. in the below Phone does not always appear).
Current format is per the below:
Before
Column A Column B
Customer1
Address ABC
Phone 1234
Customer2
Amount 25
Address XYZ
Customer3
Amount 500
Address PQRS
Phone 567
Format afterwards should be
Customer AmountAddress 1Phone
1 ABC 1234
2 25 XYZ
3 500PQRS 567
I like to search in a columns(1) for specific words say “Don” and then write in another column (say column 6) as “one”. Then it should keep doing until the last cell in that column. It should do nothing if "Don" is not found.
How can I do it?