Hello all! I have a workbook with 2 worksheets. Worksheet "A" has the data. In worksheet "B" I would like to return the value of Column A from Worksheet "A" if a partial value of column A in worksheet "A" exists. Example:

Worksheet A

Column A Column B
2445-ALLOC-21 TRAINING EXPENSE
2445-DAAS-1 CMIPS II Development and Implementation
2445-CFS-3 PROFESSIONAL SVCS PROVIDED
2445-PERC-1 PROFESSIONAL TRAINER FEES - EIS (HR TRAINING EXP)
2445-TAD-ELIG-10 MEDICAL EXAMS FOR GR CLIENTS
2445-CFS-4 SSI DETERMINATION
2445-CFS-5 PEER QUALITY REVIEW
2445-PERC-2 CUSTOMER SERVICE TRAINING
2445-CNET-1 Reimbursement for Prof Services
2445-TAD-ELIG-3 Local Share M&O
2445-TAD-ELIG-4 IMAGING
2445-TAD-ELIG-5 Call Center (Facilities and P/O)
2445-TAD-ELIG-6 Call Center (One Time/Recurring Services)
2445-TAD-ELIG-7 MISC RECURRING CHARGES for ONGOING SERVICES
2448-ALLOC-1 SERVICES PROVIDED BY COUNSEL
2450-ALLOC-1 SYSTEMS DEVELOPMENT CHARGES-ONGOING

Worksheet 2

Cell A:1 ="TAD-ELIG"

I'd like to return any data from column A of worksheet "A" That includes the value of Cell A:1

I hope that makes some sort of sense...

Thank you.

I believe that Excel, like Access, offers an InStr() function you could use to test where, in your ColumnA, the string in your ColumnB shows up. If the value of that function "against" ColumnA is greater than zero, what you're looking for is there.

You could "wrap" an IF() around an InStr()...

Do you want to do this as a manual process or as a VBA macro.

If you are working with Excel 2007 or higher, you can put a filter on the column and one of the options on the text filers is "Contains" - so it will select anything that has the string embedded in it anywhere. You can copy and paste into the new spreadsheet.

Here is a little snippet of code I use that does this in VBA

It allows you to use wildcards (*) in Cell B1 to select from the list. So TAD-ELIG* gets you any string starting with TAD-ELIG, *TAD-ELG gets you any string that ends in TAD-ELG and *TAD-ELG* gets you any string that contains TAD-ELG. More powerful matching patters can also be used like *-2012*.xls which will get you any XLS file with -2012 in it. A blank in Cell B1 clears the filter.

You'll still have to write a copy and paste routine.