?
Solved

Excel 2007 - Find & copy data based on cell value

Posted on 2010-01-11
6
Medium Priority
?
267 Views
Last Modified: 2013-11-10
I would like to have Excel perform the following operation:

Go one by one through the values of range A1:A10, doing the following to each:
Find the cells where the value in the selected cell exists in any of the cells in the range B2:B100
For any cell that has the value anywhere in it, select the row of that cell and copy it to sheet 'Results'

In the end I am looking to copy every row that has the value in any of the cells in A1:A10 as part of the value in the B column cell of that row.

Does anyone have any idea for the code for this? Thanks very much in advance for any help.

Andrey
0
Comment
Question by:andreyman3d2k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 26289508
Like this using autofilter for speed

Cheers
Dave

Sub Copy()
    Dim rng1 As Range, rng2 As Range, c As Range
    Dim ws As Worksheet
    Set rng1 = [A1:A10]
    Set rng2 = [b1:B100]
    Set ws = Sheets("results")
    ActiveSheet.AutoFilterMode = False
    For Each c In rng1
        If c.Value <> vbNullString Then
            rng2.AutoFilter 1, c.Value
            rng2.Offset(1, 0).Resize(rng2.Rows.Count - 1, rng2.Columns.Count).EntireRow.Copy ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, -1)
        End If
    Next c
    ActiveSheet.AutoFilterMode = False
End Sub

Open in new window

test.xls
0
 
LVL 6

Author Comment

by:andreyman3d2k
ID: 26290338
I did not get a chance to test this yet, but quick question: Will this work if the cell only contains a part of the value? Ex: If looking for 'def', it should find 'abcdefgh' and copy that row as well. I am not sure if this is possible with the autofilter, so I am not sure if you accounted for this. Will try first thing tomorrow. Thanks.

Andrey
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 26290358
It will now that you have specified it :)

I have added widlcards to the filter below

Cheers
Dave

Sub Copy()
    Dim rng1 As Range, rng2 As Range, c As Range
    Dim ws As Worksheet
    Set rng1 = [A1:A10]
    Set rng2 = [b1:B100]
    Set ws = Sheets("results")
    ActiveSheet.AutoFilterMode = False
    For Each c In rng1
        If c.Value <> vbNullString Then
            rng2.AutoFilter 1, "*" & c.Value & "*"
            rng2.Offset(1, 0).Resize(rng2.Rows.Count - 1, rng2.Columns.Count).EntireRow.Copy ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, -1)
        End If
    Next c
    ActiveSheet.AutoFilterMode = False
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Author Comment

by:andreyman3d2k
ID: 26294481
Hey, this is phenomenal, thanks! Can I ask you to make one addition to it:

Can it do one of the following, whichever is easiest:

1. Add a heading on top of each copied block to identify which label in A1:A10 was used to get that resultset.

Ex.

abc

defabcwz    123
abcrg          456

def

ftdefab      432
adefbv       789

OR

2. add the label to the end of each copied row.
Ex:

defabcwz    123    abc
abcrg          456    abc
ftdefab      432   def
adefbv       789   def

Please let me know if this is a tiny modification, or if I should open a separate question for it to be fair. I wish i could give you 1k points for this, because I did not ask for it in my initial query (just realized I get a semi-disorganized mess at the end : )  Thanks very much,

Andrey
0
 
LVL 6

Author Closing Comment

by:andreyman3d2k
ID: 31675878
This is awesome, exactly as I asked, thanks Dave.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 26298525
Andrey,

thx for the grade :)

Would you mind opening a new question for this. There will be some tinkering involved

Cheers

Dave




0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Simple Linear Regression

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question