copying the row from sheet one based on the match in second sheet

sannuk7
Apr 24th 2016
Thread is marked as Resolved.

sannuk7

Beginner

Points

225

Posts

27
Apr 24th 2016
- #1
Hi

I have data in sheet 1. The data has a list of companies and their different directors with various attributes.
company directors remuneration appointed_date age
A Carpenter 1.1 27 Jan 2012 50
A Wood 1.2 1 Feb 2011 45
B Porter 1.1 27 Jan 2012 55
B William 1.2 1 Feb 2011 60
B Nataly 2.2 1 March 2010 54

The list has twelve thousand companies. Each company has on average 15-20 directors, and each directors has 25 attributes ( like remuneration, age, appointment date, meeting attended, education, experience etc.).
In Sheet2 I have a list of 450 companies. I need to have entire row copied from Sheet1 for each company in Sheet2.
Kindly help me.
skywriter

Professional

Reactions Received

1

Points

5,371

Posts

1,068
Apr 24th 2016
- #2
Re: copying the row from sheet one based on the match in second sheet

It would be more helpful if you uploaded a sample sheet and showed the desired results.

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.
Bruce :cool:
sannuk7

Beginner

Points

225

Posts

27
Apr 24th 2016
- #3
Re: copying the row from sheet one based on the match in second sheet

Quote from skywriter;769835

It would be more helpful if you uploaded a sample sheet and showed the desired results.

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

Hi
I have uploaded the sample data. The first worksheet is the total data. In second worksheet I have name (symbol) of companies. In the third worksheet is the desired result. In third worksheet, for each company in second worksheet all the information from first worksheet have been taken. Though I have taken only three companies in second worksheet, I have to get the data for 450 companies.

Thanks and Regards
Files

samle data of directors.xlsx 68.76 kB – 108 Downloads
skywriter

Professional

Reactions Received

1

Points

5,371

Posts

1,068
Apr 24th 2016
- #4
Re: copying the row from sheet one based on the match in second sheet

What's going to be the difference between total data and the desired results?

Are there companies on total data sheet that are not in the list of companies?
Bruce :cool:
sannuk7

Beginner

Points

225

Posts

27
Apr 24th 2016
- #5
Re: copying the row from sheet one based on the match in second sheet

1. The difference between "total data" and the "desired result" is that the "desired result" has the details only for the selected companies. The "desired result" is about fetching the information for only selected companies given in "list of companies".
2. Yes, there are many companies in "total data" that are not in the "list of companies". The total data contains a list of 12,000 companies, whereas the "list of companies" has 450 companies. I want data for only 450 companies in desired result.

Apr 24th 2016

#6

Re: copying the row from sheet one based on the match in second sheet

This is very basic code, see if this works as per your requirement...

Code

Sub CopyData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long, dlr As Long, dlr1 As Long, i As Long
Dim dRng As Range, dCell As Range
Dim symbol


Application.ScreenUpdating = False
Set sws = Sheets("Total Data")
Set dws = Sheets("List of Companies")


slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
dlr = dws.Cells(Rows.Count, 1).End(xlUp).Row
symbol = dws.Range("A2:A" & dlr).Value


dws.Range("A2:A" & dlr).Clear
For i = 1 To UBound(symbol, 1)
   With sws.Rows(1)
      .AutoFilter field:=1, Criteria1:=symbol(i, 1)
      If sws.Range("A1:A" & slr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
         sws.Range("B2:AQ" & slr).SpecialCells(xlCellTypeVisible).Copy dws.Range("B" & Rows.Count).End(3)(2)
         dlr = dws.Cells(Rows.Count, 1).End(xlUp).Row + 1
         dlr1 = dws.Cells(Rows.Count, 2).End(xlUp).Row
         dws.Range("A" & dlr & ":A" & dlr1).Value = symbol(i, 1)
      End If
   End With
Next i
dws.Range("A1").CurrentRegion.Borders.Color = vbBlack
dws.Columns.AutoFit
sws.AutoFilterMode = 0
Application.ScreenUpdating = True
MsgBox "Task Completed.", vbInformation
End Sub

Display More

Apr 24th 2016

#7

Re: copying the row from sheet one based on the match in second sheet

Try this.
Control + q is a shortcut to run the macro.

Code

Sub sannuk7()
Dim arrFilters
Application.ScreenUpdating = False
    With Worksheets("List of Companies")
    arrFilters = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
    End With
    
    Worksheets("Desired Result").UsedRange.ClearContents
    
    With Worksheets("Total Data").Range("A1").CurrentRegion
        .AutoFilter 1, arrFilters, 7
        .Copy Worksheets("Desired Result").Range("A1")
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

Display More

sktneer

Intermediate

Reactions Received

3

Points

1,228

Posts

245
Apr 24th 2016
- #8
Re: copying the row from sheet one based on the match in second sheet

skywriter

Pretty smart. Nice one.
Regards.
sktneer
sannuk7

Beginner

Points

225

Posts

27
Apr 24th 2016
- #9
Re: copying the row from sheet one based on the match in second sheet

Hi,
Thank you very much. It is a great relieve. Now I can work on this data. Thanks
skywriter

Professional

Reactions Received

1

Points

5,371

Posts

1,068
Apr 24th 2016
- #10
Re: copying the row from sheet one based on the match in second sheet

Quote from sktneer;769841

skywriter

Pretty smart. Nice one.

Thanks, nice to see you on this board.
Bruce :cool:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

Users Viewing This Thread

1 Guest

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.