• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pull all rows with two matched columns and paste into worksheet

Micki Hall

New Member
Hello!

Unfortunately I can not upload a sample because of security risks, as I work for a finance firm. I have searched high and low on the internet with no solution. So I have an excel sheet with 3000+ rows, I am trying to match the name of the security and the date and pull all rows that have that matched criteria. Basically i'm looking through a list of securities that are repeated more than once and I need both of the rows with matching dates and security names.

I am trying to submit this very soon because basically i'm creating the macro to avoid hours and hours of work, but I also have a deadline. Any help would be appreciated, thank you!!
 
Last edited:
Hello!

Unfortunately I can not upload a sample because of security risks, as I work for a finance firm. I have searched high and low on the internet with no solution. So I have an excel sheet with 3000+ rows, I am trying to match the name of the security and the date and pull all rows that have that matched criteria. Basically i'm looking through a list of securities that are repeated more than once and I need both of the rows with matching dates and security names.

I am trying to submit this very soon because basically i'm creating the macro to avoid hours and hours of work, but I also have a deadline. Any help would be appreciated, thank you!!


Just create a dummy file that looks exactly like your file structure but with bogus data - that makes it exponentially more likely that you will receive a correct answer to your problem. Otherwise, the Ninjas have to guess what you're trying to achieve, and although they're pretty smart ....
 
You might looking something like as attached.
You may also get the same by advance filter too.

If it doesn't fulfill the goal then must share what David Evans suggested.

You might be looking for macro !
 

Attachments

  • Matched_Data.xlsx
    13.7 KB · Views: 1
So I made a sample document (attached). As you can see, there are names of securities, dividends, and dates. What I want to do is copy and paste all of the rows where Column A and B match to other rows. For example, Micki Inc. has two dividends posted on the same day (7/29/15). I would like these two rows to be pasted into the sheet called "Double Dividend".

Also, the actual file has 17 columns, thus it can't go on the same sheet like the sample posted. Thank you again!
 

Attachments

  • Test.xlsx
    9.8 KB · Views: 0
Last edited:
Check this..

You just need to change the bold part.

CSE formula!!

=IFERROR(INDEX(Report!$A$1:$D$9,SMALL(IF(COUNTIFS(Report!$A$2:$A$9,Report!$A$2:$A$9,Report!$B$2:$B$9,Report!$B$2:$B$9)=2,ROW(Report!$A$2:$A$9)),ROW()-1),MATCH(A$1,Report!$A$1:$D$1,0)),"")


You may also use named range/table for the same.
 

Attachments

  • Test (2).xlsx
    11 KB · Views: 1
Is there a way to make this a VBA macro? So my boss can press a button and it will copy and paste all the rows to the Double Dividend sheet. Thank you in advance!
 
Is there a way to make this a VBA macro? So my boss can press a button and it will copy and paste all the rows to the Double Dividend sheet. Thank you in advance!

Anything is possible, but it will require knowing a lot more about how and from where your data is derived. I also think you'd be advised to use Excel Tables (As Deepak has suggested also), so that you don't run into the issue of having to define dynamic ranges as your data changes.

VBA can make it easier for the end-user, undoubtedly, but it requires a lot of behind the scenes work, preparation and an ability to maintain it. You'd be best advised to get it working as efficiently as possible without any VBA, before you attempt to automate it ... Automating processes only *appears* to make them simpler - they are in fact a lot more complex.
 
I have been making VBA macros for a long time, I am willing to do the work behind it. Thank you for your advice, but if anyone else could help me I would be grateful.
 
Try this demonstration :​
Code:
Sub Demo1()
                        R& = 1
Application.ScreenUpdating = False
 
With Sheet1.Cells(1).CurrentRegion.Rows
                  C& = .Columns.Count
    Do
                   R = R + 1
        With .Item(R).Resize(2)
          If .Cells(1).Text & .Cells(2).Text = .Cells(C + 1).Text & .Cells(C + 2).Text Then
            L& = L& + 2:  R = R + 1
            .Copy Sheet2.Cells(L, 1)
          End If
        End With
    Loop While R < .Count - 1
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Last edited:
Try this demonstration :​
Code:
Sub Demo1()
                        R& = 1
Application.ScreenUpdating = False
 
With Sheet1.Cells(1).CurrentRegion.Rows
                  C& = .Columns.Count
    Do
                   R = R + 1
        With .Item(R).Resize(2)
          If .Cells(1).Text & .Cells(2).Text = .Cells(C + 1).Text & .Cells(C + 2).Text Then
            L& = L& + 2:  R = R + 1
            .Copy Sheet2.Cells(L, 1)
          End If
        End With
    Loop While R < .Count - 1
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​

How could I apply this if the two columns were L and M?
 
Back
Top