Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Match and Sort for two range of data on different worksheets?
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Match and Sort two range of data? | New Users to Excel | |||
MATCH UP DATA IN COLUMNS | Excel Worksheet Functions | |||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
How to match sort and lineup 2 sets of data | Excel Worksheet Functions |