Searching for partial data in a column

I am searching for partial data in column and want to return all possible 
queries.

Example: Search item in cell B1
Find all items on a second sheet in column a1 that contain b1 from original 
sheet.
Not looking for exact matches, in other words a1 contains "exchangers" and 
on the other sheet column a1 might contain "heat exchangers" or just 
"exchangers"

I want to return not only "heat exchangers" or "exchangers" but also the 
info in the rest of the row.

   A1                       B1              
1 Item                     Location      
2 Exchanger             Tank Farm 1    
3 Heat Exchanger      Tank Farm 2

Thanks for any help.
If not clear ask and I will try and clearify.
0
Severin (1)
10/3/2005 1:03:32 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
362 Views

Similar Articles

[PageSpeed] 44

Assumptions:

On Sheet2...

A1:B10 contains your source data

First row contains your headers/labels

Formulas:

On Sheet1...

A1:  enter your search term, such as 'Exchanger'

B1:  enter 0 (zero)

B2, copied down:

=IF((Sheet2!A2<>"")*(ISNUMBER(SEARCH($A$1,Sheet2!A2))),LOOKUP(9.999999999
9999E+307,$B$1:B1)+1,"")

C1:

=LOOKUP(9.9999999999999E+307,B:B)

D2, copied down:

=IF(ROWS(D$2:D2)<=$C$1,MATCH(ROWS(D$2:D2),$B$2:$B$10,0),"")

E2, copied down and over to the Column F:

=IF(N($D2),INDEX(Sheet2!A$2:A$10,$D2),"")

Hope this helps!

In article <F48DD1A5-C425-426E-B785-5172B1CAF52C@microsoft.com>,
 "Severin" <Severin@discussions.microsoft.com> wrote:

> I am searching for partial data in column and want to return all possible 
> queries.
> 
> Example: Search item in cell B1
> Find all items on a second sheet in column a1 that contain b1 from original 
> sheet.
> Not looking for exact matches, in other words a1 contains "exchangers" and 
> on the other sheet column a1 might contain "heat exchangers" or just 
> "exchangers"
> 
> I want to return not only "heat exchangers" or "exchangers" but also the 
> info in the rest of the row.
> 
>    A1                       B1              
> 1 Item                     Location      
> 2 Exchanger             Tank Farm 1    
> 3 Heat Exchanger      Tank Farm 2
> 
> Thanks for any help.
> If not clear ask and I will try and clearify.
0
domenic22 (716)
10/3/2005 1:53:44 PM
Have you thought about using Data|Filter|Autofilter.

Then you can use the arrow in column A's header to choose 
Custom|Contains
exchanger

If you need the data in another spot, you could copy and paste from there.

Severin wrote:
> 
> I am searching for partial data in column and want to return all possible
> queries.
> 
> Example: Search item in cell B1
> Find all items on a second sheet in column a1 that contain b1 from original
> sheet.
> Not looking for exact matches, in other words a1 contains "exchangers" and
> on the other sheet column a1 might contain "heat exchangers" or just
> "exchangers"
> 
> I want to return not only "heat exchangers" or "exchangers" but also the
> info in the rest of the row.
> 
>    A1                       B1
> 1 Item                     Location
> 2 Exchanger             Tank Farm 1
> 3 Heat Exchanger      Tank Farm 2
> 
> Thanks for any help.
> If not clear ask and I will try and clearify.

-- 

Dave Peterson
0
petersod (12004)
10/3/2005 5:33:09 PM
Reply:

Similar Artilces:

Problem with data using IF and Nested IF statements possibly???
Afternoon All I am attempting to analyse data from multiple worksheets from numerous people the incoming data all has one thing in common column A this is a certain frequency a job is done. The problem is that there are many ways of entering the data ie 12 months or 52 weeks or 365 days all essentially meaning the same thing. My idea is to collate the data in col A and then using the Helper column as the standard frequencies ie if cell A1 = 12 months, closes frequency in helper is 52 weeks therefore value in C1 = 52 weeks. By using an IF statement I can change the value of one frequen...

Calendar from Excel Data
I currently keep a fairly complex calendar as an Excel spreadsheet. I would like to find a way to automatically generate a normal-looking month-by- month calendar from this. I really don't need the month-by-month calendar to show much of the complexity, just the events and times and possibly locations in the correct dates on the calendar. I did a little looking on the web and saw many calendar systems, but it wasn't clear to me which might be able to import my data from Excel. --David ...

transfer data from one worksheet to another
Can anyone please help I have a worksheet that contains 12 columns and a variable number of rows. the 4 main columns Col A = Identity Number Col B = Surname Col C = First name Col D = Department etc There is a separate worksheet for each department.There are 6 departments and I need to transfer the identity number,names and details held in the remaining columns to a separate worksheet depending upon what department the person belongs to. The data on the master list is not sorted in any particular order. Is there a way to copy this data to each sheet without having to sort and then copy/paste t...

COM Callout Partially Instantiated Object
I have a registered com callout and when I perform a function such as an update, I see in the component services snap-in that the package is being instantiated. However, the callout class is not. There are no errors in the logs and have the same issue with a component compiled with 1.0 of the framework as well as 1.1. Has anyone run into this before? Thanks in advance. Shannon, The following TechKnowledge article explains fairly completely how to get a callout running. Please verify that you have done the steps indicated. http://www.greatplains.com/techknowledge/techknowledge.asp...

Disable Adding or Deleting Rows and Columns
Is there a way to disable a user from adding or deleting rows and columns but still be able to enter information and run macros? You can put a value in cell IV65536 to prevent the user from adding rows or columns. Deleting would require some VBA. MVP John Walkenbach has some here using "undo": http://j-walk.com/ss/excel/tips/tip23.htm HTH Jason Atlanta, GA >-----Original Message----- >Is there a way to disable a user from adding or deleting rows and columns but >still be able to enter information and run macros? >. > ...

getting a column name
please help me with this one ok? lets say i have two columns. in the first raw i have the names: a1= daniel a2=bill the next two rows are numbers b1=10 b2=20 c1=20 c2=40 what i need to do is to know who got the 20 in the previous row. so the cell c3 will be "bill" because in the "b" row bill got "20" a in the "c1" cell. it looks like that ----3-----2-----1---- ----------Bill---Daniel--a ----------20----10-----b ---bill----40-----20----c please help asa -- mordo ----------------------------------------------------------------------- mordor's Profil...

Free/Busy data
When I try to set upa meeting and try to save and close it outlook gives me an error saying " Unable to update public free/busy data". It also gives me this error when I exit out of outlook. Also it seems like all my contacts are gone from the list. Any suggestion? ...

Search last 4 digits in an (account number)
I store bank accunt and credit card numbers in various Contact Notes. For example, a credit card # as xxxxxxxxxxxx1234. Outlook contact search would not find this contact if I enter the search for "1234". Is there a way to do this in Outlook 2007? TIA PS - this search works fine in Outlook Mobile. outlook searches from the beginning - so it will only work if you use xxxxxxxxxxxx 1234. Other search tools (like google desktop) may find it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: htt...

how do I display last two digits of a SSN in a separate column
I am trying to create a formula to take an existing list of 8 or 9 digit numbers and display the last two numbers in a separate column. Is this possible? Try =RIGHT(A1,2) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "katbone1" wrote: > I am trying to create a formula to take an existing list of 8 or 9 digit > numbers and display the last two numbers in a separate column. Is this > possible? Hi Use a formula =3Dright(A1, 2...

Formulas: Keeping same row/column reference when columns are inser
I'm trying to create a formula that will return the value of a cell based on its row and column position in the spreadsheet even if I insert another column. Example Formula in Cell C2 is: =A2+B2. I insert a new column A. This moves everything one column right and the formula in cell D2 is now: =B2+C2. What I'd like is a formula that references the row/column position in the spreadsheet repardless of if columns are inserted or deleted so that after inserting a new column A my formula is the value of the new information in A2 & B2. I'd like the formula that moved to D2 to...

Look for: Search In => Choose folders=> Search subfolders not working
When I search for an old email using Outlook's Look for: box and specify which folders to search in via the "Choose folders=>Search subfolders" checking a folder does not actually cause any subfolders to be checked. Unless there is some other setting and/or option hidden away somewhere, the "Search subfolders" check box is not working. I must manually check all the desired subfolders. What am I doing wrong? -- Regards, Peter Sale Santa Monica, CA USA To email me, just pull 'my-leg.' ...

Search & replace in formulae
Is it possible to do a search and replace which includes the cell formula contents ? I have a workbook that has cells which link to the contents of a cell in another workbook and I'd like to know if the naming has to remain constant or if I can replace a part of the name periodically. ie each year. thanks "Iain Rhodes" <iain@pricejam.com> wrote in message news:8ec201c49682$858c5d20$a501280a@phx.gbl... > Is it possible to do a search and replace which includes > the cell formula contents ? I have a workbook that has > cells which link to the contents of a cel...

Are there any good tools out there for importing data?
I am looking for a tool that will give me the DBA abilities I need to work with MSCRM. I could do some of this with Access, SQL, etc., but they say you can't even update records via other tools (not utilizing the APIs). Some of the basics that I need to do are: 1. Import contacts and link them to existing accounts (on import) 2. Import contacts and accounts together (and link them) 3. Do mass updates based on a query. For example, query all records that have "georgia" as the state and change to "GA". This is just one example of the many DBA activities. Does anyone ...

Looking Up the First Nonzero Value in a Column
Hello everyone, I am looking for a function that can return the first nonzero value in a column. I have a table that has months across the top and down the leftmost column. The data in the table are balances as of each month, essentially. It looks like this: J F M A M J 1 F 2 1 M 3 2 1 A 4 3 2 1 M 5 4 3 2 1 Is there a way to say, "Look in the column labeled "F" and return the first nonzero number"? Then the cell below it will need to be the number directly below that first number, and so on. I'd appreciate any help. Thanks! Sincerely, T...

Searching for Newsgroups
I subscribe to a Usenet service for accessing various news groups. After I download all the newsgroups from that server I can not find a way to search for the newsgroups I have interest in. When I put something like "RV" in the search box on the top right corner, it searches all of my Emails rather than just the list of groups that had "RV" in the title. Help tells me that there is a " Display newsgroups containing" box but for the life of me I can't find it. Any thoughts out there? I am running Entourage 12.2.3 Dunc Click the bar in th...

Normalizing data
I have an excel file which has over 5000 lines and will continue to add new data. I would like to convert this into an Access database. The problem is I'm not sure which field is my primary key as these fields have redundant data lines. I'm guessing that normalization is required but not quite sure how to go about it. Here are some sample data lines: Dealers Start Date Org Amt CUSIP Security Class BS 2/13/2008 22 07388YAE2 BSCMS 07-PW16 A4 Citi 2/13/2008 10 07388YAE2 BSCMS...

Can the left column panes be frozen when doing functional chart
Am doing a swim lane flow chart that is using many pages. Would be nice to freeze the functional names on the left of each page. Much like you can do in excel. Can this be done in Visio? ...

match up column A with column B
Hi, I have a column of numbers in A, and a column of numbers in B. There are 180 numbers in A (A1:A180) and 130 numbers in B (B1:B130). I need Excel to find which numbers match up between columns, e.g look down column A and find all the matching numbers in column B, then sort the columns so the matched numbers are next to each other (so I can see which numbers don't match between columns). (I have sorted the numbers from lowest to highest down the column, so they are in numerical order). I have no idea how to do this. :confused: Thanks for any assistance you can provide. :) -- ...

Search engine optimization
Hello all! My question today regarding this issue is: I have noticed some sites have the following on their sites "Source". meta name= "robots" content="Index, follow" meta name= "revisit-after" content="15 days" or (30 days) What does this function accomplish as far as search engines go? and will it really imporve your results ( rankings) Like for instance "robots" "index, all" what is the difference between this and saying "robots" "index, follow" I'm a bit confused about this. Can someone ...

combining data
I have a large amount of data formatted in the same way in three to four different worksheets. Each sheet holds about 1000+ rows of data. Is there a way to combine the data from all four sheets into one? Copy and Paste? Or do you need some code for this as you will be doing it regularly? -- Michael Hopwood (Phobos) "frank" <mrfrank73@aol.com> wrote in message news:018401c3a94e$75ce1850$a401280a@phx.gbl... > I have a large amount of data formatted in the same way in > three to four different worksheets. Each sheet holds > about 1000+ rows of data. Is there a...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Can I search identical fields from multiple tables?
I have a database to log details about staff in my school. I have a tables for staff details, courses, absence, educational visits etc. In each table I have a date field to show when the member of staff is not present. I want to produce a query to search all tables by entering a date - e.g. to bring back all results for staff who are not in school on a certiain day because they are either ill, on a course or on an educational visit. The query will work by entering a date once - it will then search all the other tables and bring back the results. Is this possible? Do I restructure? ...

count the occurence of number in a column
hi, may i ask: how do i count the occurence of a number in a column? an example: 1 5 8 1 3 4 1 5 1 occurence of 1 = 4 occurence of 3 = 1 occurence of 4 = 1 occurence of 5 = 2 occurence of 8 = 1 what would be the formula for that in excel, please? i have no clue at all. i tried google and it came up with something but i could not really find the answer to my question. tks, l. Hi =COUNTIF(A:A,1) -- Regards Frank Kabel Frankfurt, Germany "Laura" <no@spam.pls> schrieb im Newsbeitrag news:bzSbd.279333$vf1.14290064@phobos.telenet-ops.be... > hi, > may i ask: how do ...

What does outlook search when you search Contacts ?
When you do a search in oulook for contacts, what is the criteria that outlook searches? Or I guess how does outlook search? Why is it not consistant? The reason I am asking is that we have our customers contacts in public folders. In our customers we have 6 contacts for XYZ Foods. If you search in outlook for just "XYZ" it only comes back with 4 of the six. If you do a search for "XYZ Foods" it returns with all 6. And if you just entered "Foods" no contacts were found. And we actually have a few companies in our contacts who's names are a two part name ending...

average data from diff sheets
need a quick formulae help from you in excel. This is what am trying to do. I have 2 sheets (A and B ) . From sheet B am taking the average of (A1: B3) and want it in the A1 cell of sheet A. To put it concisely sheet A A1 Average (SheetB!(A1:B3)) B1 Average( SheetB!(C1:D3)) A2 Average (SheetB!(A4:B6)) B2 Average( SheetB!(C4:D6)) and so on. sheet B has (60*48) cells I want a common formulae or macro to do this but not sure how to go about using offset or index func...