Search for text within column Results

Hi,

Does anyone have any suggestions for the following:

I have a spreadsheet that looks at data and then sorts the data based on certain text within the cell. The code works great when there are several rows of data. However, when there is only 1 row of data or no data for a particular day (This happens once or twice a month) the code does not work.

Here is an example of the code:

Sub Test()

Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(OR(ISNUMBER(SEARCH({""fee"",""inter""},RC[-1]))),""F"",IF(OR(ISNUMBER(SEARCH({""transf"",""direct pay"",""xf""},RC[-1]))),""T"",""O""))"
Range("C2").Select
Selection.End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "end"
Selection.End(xlUp).Select
Selection.Copy
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues

Range("A1").Select

Sheets("Sorted").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="T"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("transfers").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("Sorted").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="O"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("other").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("sorted").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="F"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Fees-Interest").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:d").Select
Columns("A:d").EntireColumn.AutoFit
Sheets("sorted").Select
Application.CutCopyMode = False

End Sub

Any Help would be greatly appreciated!!

Hi

What I'm trying to do is:
Automate the opening of three .TXT files into Excel, format the data, moving
columns, changing fonts etc.. in each file, ready to place into a single file
to sort, filter, delete and add formulas etc.. That's the part I can handle.

Problem is the three .TXT files I receive named OUTPUT1.TXT OUTPUT2.TXT &
OUTPUT3.TXT do not always contain the same information i.e. OUTPUT1 may
contain students D.O.Birth, next time OUTPUT2 will contain D.O.Birth.
The reports are generated elsewhere so I do not have control of their
filenames. But in each the cell A2 has text indicating part of the files
content.

My problem is after opening any of the OUTPUT files, how do I check if cell
A2 contains " list with date of birth " within it's contents and if TRUE
rename the file to Output1.txt.
Also should A2 contain " list of school card " Then rename the sheet tab to
Output2.txt or should A2 contains " list of atsi students " rename the sheet
tab to Output3.txt?
What is the syntex to search/find a short string of text, say 15 char. in a
cell containing a longer 100 char string?
Can the text search/find be non_case_sensitive?

Sorry for this second post on same subject but I received a message saying
my reply to Otto Moehrbach got lost in system.

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

Hi,

I am trying to create a formula that will search a cell to see if it ends in a three digit number (e.g., 0.06, 0.03, 0.01, etc.) and if it does then it will return the text immediately before that number. Some of the data has time stamps plus a serial code that need to have excluded as well. Some of the data has ":30" or ":15" before the three digit number that also needs to be excluded.

I have attached an example with the data in column B and the needed result in column D. I have been trying to do this for over an hour and gotten close but still not quite right. Thank you for any help you can provide!

Thanks,
John

Hello.

Summary: Is there a way to combine some/all of the fornulas below?

After searching for a few hours and finding partial solutions to each element of my quandary, I thought I'd reach out to this community for more specific help.

Excel 2007 shared macro-enabled spreadsheet.
Column B = assigned date
Column C = due dates
Column J = text-based status (Not Started, In Progress, Develop, Complete, On Hold)

What I’m trying to accomplish is to have column D be shaded yellow if today’s date is within 7 days of the deadline AND column J = “Not Started” or “In Progress”. If Columns B or C are blank, I want them shaded blue and nothing else to happen.

However inelegant, the formatting rules below accomplished this. Is there a way to combine some of the arguments? The reason is I’d like to apply these rules to multiple spreadsheets so the fewer rules the better.

=AND($C2="") <shade blue, stop if true>
=AND($B2="") < shade blue, stop if true>
=AND(($C2<=TODAY()),$J2="Not Started") < shade yellow>
=AND(($C2<=TODAY()),$J2="In Progress") < shade yellow>
=AND(($C2-TODAY())<7,$J2="In Progress") < shade red>
=AND(($C2-TODAY())<7,$J2="Not Started") < shade red>

Many thanks in advance!

<and many thanks after your solution worked!>

Hi, I need to find the cell that has a text string (a number actually), that is at the beginning of the rest of some other text. To illustrate, in column C I have cells that contain this:

965 Los Angeles Dodgers C Kershaw - L
966 Colorado Rockies J Chacin - R
.
.
.

I need to do a search on all the cells in column C looking for the cell that contains the number, say, 966. I can't really use MATCH or LOOKUP because they would be looking for a cell that has 966 in it by itself - in other words they look for a perfect match. I need something that will detect my search string (966) *anywhere* within the cells to be searched. And if this function could return the exact cell location where it's found, that would be perfect!

Thanks!
Shawn

i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)

what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255

the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)

is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset

after some MASSIVE googling, i have stumbled accross this

B1 = Search box (txt field)

A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number

A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number

and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on

2 things i cannot recitify..

1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results..

is there any resolution to the above

cheers & thanks

Hey, everyone! I just want to say I really appreciate all the help I've received here. This is a GREAT forum.

Too bad MS Excel has such a frequently useless Help function - you'd think they would have a much better Help Search algorithm. When I look for help within Excel I often get no help.

However, here is my first question. WHY I should enter brackets {} for an array formula? I know you can use array constants and get multiple results, but...

The reason I ask is I'm comparing Last Name text in 2 columns - here's the formula:
{=IF(AD14="","",INDEX($A$4:$A$1629,MATCH(LEFT(AD14,4)&"*",$A$4:$A$1629,0)))}

One col has LN only, the other is formatted LN,FN within the cell (note comma).

The formula seems to work whether I use brackets or NOT (repeat: seems to work either way based on small sample so far).

So... a corollary question is why should I use the formula as an array? Anyone know? Does it not matter with a simple formula like this, but would matter if using a more complicated formula? Please post a reply if you know.

Thanks!

LTUser
Boston, MA
USA - Let Freedom Ring!

I have a cells in column A in varying lengths that I want to extract the
text characters in between two known and common characters { and }. I've
tried figuring out the use of =MID(A:A,FIND("{",A:A&"}")-1,255) however the
string of characters within { and } varies in length. Is there a way to
count the characters in between my search criteria and replace the 255 with
that number?

hello,
i tried searching but was unable to find a solution. sorry if this is a repost.

issue 1
i'm trying to create values (text and numbers) for a column where the bulk of the information is concatenated. the dilemma lies in trying to have the last 3 characters as dynamic numbers that increase down this column. following is what i have, but i don't know what to replace "001" with:

=CONCATENATE(E2, F2, G2, H2, "001")

for example, following would be results that i would need:

AOI050101001
AOI050101002
AOI050101003
AOI050101004
...

issue 2
i have a column containing numerical values (01 - 11), and another column with dates formatted as MMDDRR (ie: 012805). what i would like to do is have the date column calculate itself based on the numerical value.

thus, if the numerical value is 01, then the date is 012805
...02 then 012905
...03 then 013005

any suggestions are certainly appreciated. thanks for looking and let me know if further details are needed!

Good morning,
I have searched Google for last few hours and found no solution for this problem.

I have a workbook which has two sheets: Dashboard and Data Sheet

Data Sheet is a big list of information and Dashboard presents the information nicely into charts, graphs etc. In the data sheet, the user adds new records to the top of the sheet. one of the columns is called type of incident of which there are 5 different values and is input through a dropdown box.

On the dashboard, I want to show the number of each incident so I have this:

V is the datasheet column that holds the user input for type of incident and F35 is a text field with one of the names of the
incidents.

This works fine and pulls of the results as expected, the problem lies when adding a new row to the top of the datasheet, the formula above will change to

note that V12 has changed to V13.

I need V12 to stay as V12 so the formula always uses that as the first value in the range so am using indirect. I have tried the following:

BUT GET A #VALUE ERROR. 

I can get the forumula to work if I create it in the data sheet and just refer to the results in the dashboard but i really want the calculation to be done in the dashboard

Can anyone offer any help?

Hello,

I'm depserately trying to do the following: I have a list of data which I'd like to be used in a sort of VLOOKUP function. The list looks as follow

Data sheet
Data.jpg

Then I have a working list containing a cell with some string values, for instance:

working.jpg
So here in my D1 cell I need to return a value based on a substring in cell E1, i.e. Phone House. Based on this value, I'd like Excel to find the corresponding row in table in Data sheet and return its corresponding value from column Vend for instance, like in a typical VLOOKUP function. The challenge here is that I cannot find a way to search within the Working sheet for a value in a string corresponding to one of the entries under Text in my Data sheet.

Any ideas?

Cheers,
Ekser

Is there a way to search a spreadsheet (updates weekly) and return a particular value and all sub values for that value? For example there is a "location name" column header. Within each location there is a varying amount of rows listing items located at the location. I want to be able to pull a one location and ALL the products associated with this location on to a separate sheet. I "location" will have 5-45 sub-items. Can this be done? Hope this makes sense?

Hi,

I'm hoping there might be someone who can help with editing a VB Macro in Excel.

At the moment the macro takes a list of keywords that have been scored ('Keywords and Scores' sheet in attached file) and searches down two columns of text (in 'Data' sheet), highlighting which keywords are mentioned and summing the scores associated with them.

At the moment the matching is case sensitive and doesn't include when the keywords are contained within a longer word (for example it doesn't recognise ABC when it appears within 123ABC123).

I'm not sure how to edit the macro so it is not case sensitive and also registers the keyword however it appears.

Thanks in advance for any help you can provide.

Andrew.

Hi...

I looked for a search and replace solution but could not figure out a wildcard solution. Following is the problem.

I have a cell that has the following information in it.

Shop By Theme/Collegiate Edition/Air Force Academy
Shop By Product/Coffee Mugs

(For the rest of the column down the page, all text after the search by theme or product is different)

I need to have a separate column with each of the above line in it.

Like... "search by theme" (and the rest of the line) in one column and "search by product" (and the rest of the line) in the next column.

I saved two copies of the file. one named "search by theme" and one named "search by product"

I WAS going to use search and replace and put search by theme in the find area and leave the replace area blank. Then I was going to do the other file the opposite way but I can not figure out a wild card to use that would find the first portion of a text string in a cell but would effect the rest of the text in the column.

This is tough to explain, I hope you guys/gals understand what I am trying to do.

In the end, I need the theme.xls file to have the complete search by theme line and the search by product.xls have the complete search by product line.

Thank you in advance for your help.
John

PS - This file has 3,000 lines in it so I can not do it manually.

Hi,
Any suggestions you can provide for me on this problem are very apppreciated.
I am trying to pull specific data from within a large number of files into
one single listing in an excel sheet. I have a list of files (approx. 1000)
that are saved without extensions or in some cases with meaningless
extensions. (e.g. .12n, .101, .m, etc.). The file name actually represents
a part number. I need to pull the complete file name of each file into one
column of excel. I also need to pull utilization data from each file and
place it in the corresponding row of the excel list. For example, the output
in excel would look something like this:

Filename Utilization
101222 0.67
1032.5n 0.55
10b55.1 0.76

Within each file, the utilization numbers are specified in various ways. In
some cases, the are following the text "SHEET UTILIZATION". In other cases,
they follow the text "Efficiency". There may be other cases as well. In
addition, in some cases there is no utilization number in the file - in which
case I would like to return "not available".

I apologize in advance if this is already posted somewhere - I searched the forum for about 30 minutes and wasn't able to find the answer.

I'm trying to find a formula that will ignore text when searching for duplicates contained within the same column. For example, the column contains the following:

WS-X4606-X2-E
WS-X4606-X2-E=
WS-X4606-X2-E=
WS-C4960=
WS-C4960

The = should be ignored and these should all be highlighted as duplicates.

Any help would be greatly appreciated!

Hello,
I am trying to write some VBA to search a column (ex A1:A100) and returns the number of times the word "Buy" occurs. So, for example, this would return a value of 3.
Also, I cannot add anything to the spreadsheet itself. I need to have it solely within the VBA code.

A
1 Buy
2 Sell
3 Buy
4 Buy
5 Sell
...

Thanks!

Hi, new to this forum. Have been searching for a good few hours now and can't quite find a solution to my problem. I hope you can help.

I'm in the process of creating a database with several steps.

Step 1, name and address entry.
- I've set up a macro to extract the details from one sheet and add it to a table on a summary sheet by inserting a new row, cutting and pasting the data into seperate columns within the same row. i.e. A1 = Title - Name, B1 = Title - Address, C1 - Title, etc.. A2 = Name X, B2 = Address X etc.. When the user adds another entry from the form sheet, the table now becomes:
A1 = NAME, B1 = ADDRESS
A2 = Mr. Y, B2 = Address Y
A3 = Mr. X, B3 = Address X

My problem however starts when I want to fill in a third column (e.g. C1 = CUSTOMER RESPONSE) at a later stage from a separate sheet again.

The problem I am having is that I cannot think of how to add the step 2 data to the same row of data on the summary sheet from step 1.

Is there a way I could set up a separate sheet and have say a drop down menu of a step 1 entry as the first option and have a different form where a macro would be able to find the relevant row in the summary sheet and paste the new data into the correct cells?

I.e.

-user fills in step 1 form (name"X", address"X")
-user might fill in another step 1 form (Mr. Y, address Y)
-user clicks on a step 2 form, selects "X" from a drop-down menu, fills in step 2 form (response X, comments X, notes X etc.), clicks SUBMIT

All data filled into the step 1 form (data = "Mr. X") is transferred into a table on a separate sheet, where the macro inserts a new row on top for each new entry. I want the user to be able to fill in a step 2 form at any stage in order to add data to the same row of the ("Mr. X") step 1 data.

I could work with a macro that could recognise "Mr. X," find that text in the summary table and select the cell in say column 4. Effectively a VLOOKUP formula, but instead of copying the text from cell E5 to cell A2, select cell E5, then cut and paste step 2 form.

I'm really stuck here, I know it's a bit of a handful to understand, but any suggestions would be greatly appreciated.

Thank you

Dan

Hi,

I am having trouble with an excel file that I have created to help me put together automated costings for my business. In a nutshell, I want the spreadsheet to automatically calculate the total weight of items based on the "type" (column C), the "material type/section type" (column D), the "dimensions" (columns F,G,H), "Quantity" (Column J), "Lengths" (Column K). When these columns are filled, it should automatically populate columns L, M, N.

Due to the nature of performing these calculations, I have used a mixture of nested IF functions, SUM functions, and Vlookup functions. All functions I have used appear correct but return only some of the correct values i.e. with some values the functions return #Value instead of the correct value. I believe this to be a problem caused by my vlookup functions which refer to 3 different sheets (within the same workbook) that contain the data lists. They search for a match (text description of item) within the relevant datasheet and then enter the number in the column next to the description.

I have tried all the usual procedures to solve this. I have added FALSE to the 4th argument, I have checked that the format for the data sheets is general, I have removed additional blank spaces, I have checked that the data range is absolute. I cannot seem to solve this problem.

Please see the file attached.

Any help would be greatly appreciated.

I've been doing a lot of research to identify the correct way to force Excel 2010 to auto advance to the next cell (to the right) upon entry of data of 1 digit within 0 and 9. I found that a userform or text box must be used. I found the below code on the site (I'm sorry, but I can't remember exactly where--I've been doing a lot of searching on this topic!), but I couldn't get it to work for some reason. It was written for Excel 2003.


	VB:
	
 MSForms.ReturnInteger)****Range("A65536").End(xlUp).Offset(1, 0).Value = Chr(KeyAscii) 
    ****TextBox1.Value = "" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a document with roughly 120 columns and 300 rows. Data needs to be entered into each cell within that matrix (roughly 36000 cells), and not having to press tab each time would be a great help.

Thanks.