asked on

MS Excel ... simple question

Hi, I've an excel sheet, having quite some columns; Title, First Name, Last Name, Email address and some more.

Need to copy all the rows or just these 3 columns are fine too (FN, LN & Email), having word "Design or Designer" and place only those matched into another sheet.

Please assist, how I can achieve this, Design is just an example, it can be writer or developer or anything too.

Thanks in advance for your assistance & help here.

Best regards

Rgonzo1971

Hi,

Could you send a dummy with the original and the result sheet

Regards

Professor J

can you upload a sample data?

Yamaafg

Hi,

You can try fuzzymatch UDF. Details:

http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

Hope it helps.

Professor J

try

Sub Test()
    Dim ws As Worksheet, wo As Worksheet
    Dim r As Long, lr As Long, lc As Long, n As Long, sr As Long, nr As Long
    sr = 1
    Set ws = Sheets("source")
    Set wo = Sheets("output")
    lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
    lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    For r = 1 To lr Step 1
        n = Application.CountIf(ws.Range(ws.Cells(r, 1), ws.Cells(r, lc)), "Design*")
        If n > 0 Then
            nr = wo.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            If nr < sr Then nr = sr
            ws.Rows(r).Copy wo.Rows(nr)
        End If
    Next r
    wo.Activate
End Sub

Open in new window

Edit please see attached.
Book1.xlsb

Rob Henson

Try the Advanced Filter feature.

This can extract filtered data to another sheet and use Wild Cards in the Filter Criteria.

Thanks
Rob H

Mushfique Khan

ASKER

this looks good ProfessorJimJam, but with 2 points:

1. Designer is not under First Name column, basically it's written under column Job Title (AF)
2. where to write this code ... and how to execute too, I think it's macro, have no idea :(

Please assist, and thanks a lot ProfessorJimJam & everyone too :)

Mushfique Khan

ASKER

I executed this code, but it gives only one row, got the email address, but just one, e.g.,

?????? ???????? john.smith@live.com

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hakan Yılmaz

One can also modify current solutions to be triggered by double clicking on any value on your table. It may be filtered in its column, and pasted on other sheet.

Mushfique Khan

ASKER

Thank you :)

Professor J

You are welcome