RE: Match and Sort for two range of data on different worksheets?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

This link may help you. http://www.contextures.com/xladvfilter02.html

"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 *** 1 and e other range of data on
*** 2.

[Data given to me]...
The data on *** 1 had info on 1st half of financial year whereas e data on
*** 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 *** 2 (2nd half) to say, top 200
performing companies. The sample data on *** 2 above will becomes the one
like below after sorted:

*** 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 *** 1.
Sample as follows (**denotes matching entries):

*** 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 *** 1, I have to delete away entries for BAA Limited and CCC
Limited bcos they r unmatched entries, meaning not found in *** 2.

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

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

*** 1 (1st half) *** 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.





.

Quantcast