Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Excel 2007 - Find & copy data based on cell value

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
andreyman3d2k
Asked:
andreyman3d2k
  • 3
  • 3
1 Solution
 
Dave BrettVice President - Business EvaluationCommented:
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
 
andreyman3d2kAuthor Commented:
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
 
Dave BrettVice President - Business EvaluationCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
andreyman3d2kAuthor Commented:
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
 
andreyman3d2kAuthor Commented:
This is awesome, exactly as I asked, thanks Dave.
0
 
Dave BrettVice President - Business EvaluationCommented:
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

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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now