Not sure how many lines you have down from top to and include "Company Name..."

if you do not have these heading rows, try this.
I have not tested it myself.

Sub main()
Dim rng As Range
Sheets(2).Range("A1").CurrentRegion.Sort key1:=Range("B1"),
Order:=xlDescending

Sheets(1).Activate
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
For i = 1 To 200
rownr = Application.WorksheetFunction.Match(Sheets(2).Rang e("A" &
i), rng, 0).Rows
Cells(rownr, 3) = i
Next i

Range("A1").CurrentRegion.Sort key1:=Range("C1"), Order:=xlAscending
Columns(3).Delete
lrow = Range("A65536").End(xlUp).Rows
Rows("201:" & lrow).Delete
End Sub

"Tan" wrote:

Hi all,

I had a tedious problem here. I had two worksheets on e same workbook
whereby I have one range of data on Sheet 1 and e other range of data on
Sheet 2.

[Data given to me]...
The data on sheet 1 had info on 1st half of financial year whereas e data on
sheet 2 had info on 2nd half of financial year. It appears as below:
Sheet 1 (1st half) Sheet2 (2nd
half)
--------- --------
Company Name Sales Fig. Company Name
Sales Fig.
BAA Limited 6000 XYZ Limited
1000
ABC Limited 7000 AAA Limited
8000
XYZ Limited 1000 ABC Limited
2000
AAA Limited 8000 BBB Limited
6000
BBB Limited 1000
CCC Limited 500
......the range goes on for maybe few thousand rows.


These 2 range of data here at my example has matching entries but different
sequence-- logical bcos different company performS differently at each half
of financial year.

[Problem Identified]...
I have to first sort my data in Sheet 2 (2nd half) to say, top 200
performing companies. The sample data on Sheet 2 above will becomes the one
like below after sorted:

Sheet 2
---------
Company Name Sales Fig.
AAA Limited 8000
BBB Limited 6000
ABC Limited 2000
XYZ Limited 1000
...goes on till 200 rows.

Now I need to match both range of data such that, in our sample here, AAA
Limited, BBB Limited, ABC Limited and XYZ Limited also appears in Sheet 1.
Sample as follows (**denotes matching entries):

Sheet 1
---------
Company Name Sales Fig.
BAA Limited 6000 (to delete)
**ABC Limited 7000
**XYZ Limited 1000
**AAA Limited 8000
**BBB Limited 1000
CCC Limited 500 (to delete)

Here in Sheet 1, I have to delete away entries for BAA Limited and CCC
Limited bcos they r unmatched entries, meaning not found in Sheet 2.

After deletion in Sheet 1, I have to match manually at present such that AAA
Limited in Sheet 1 is my first entry bcos in Sheet 2, AAA Limited is e first
entry.

[After Analysis]
Both Sheet 1 and Sheet 2 should look like this after all the effort on
sorting and deletion.

Sheet 1 (1st half) Sheet 2 (2nd half)
-------- ---------
AAA Limited 8000 AAA Limited 8000
BBB Limited 1000 BBB Limited 6000
ABC Limited 7000 ABC Limited 2000
XYZ Limited 1000 XYZ Limited 1000

Could anyone enlighten me on this. Thanks in advance...



Tan.