How to Match and Sort two range of data?

[Tan]

Hi all,

Appreciate if anyone could help out on this issue. I have a range of datas
on sheet 1 and another range of data on sheet 2. Sample as below :

In sheet 1,

Company Name Sales Fig.
D Limited 4000
A Limited 1000
E Limited 5000
B Limited 2000
C Limited 3000

In sheet 2,

Company Name Sales Fig.
E Limited 5000
D Limited 4000
C Limited 3000


I want to match data in sheet 1 to my data in sheet 2 becos my priorty is
the data in my sheet 2.
As such, "A Limited" and "B Limited" must be flag as unmatch entry in sheet
1 and be deleted immediately. On top of these, the data in my sheet 1 must be
sorted the same sequence as sheet 2. That is, in sheet 1 shown as

Company Name Sales Fig.
E Limited 5000
D Limited 4000
C Limited 3000

That is same data as my sheet 2 at last. I cant use custom list in excel
becos my range of data will be as many as few hundred rows


Thanks

 

[Max]

One non-array formulas play to take it out in a new sheet ..

Assuming the 2 tables as posted are in cols A and B
in both Sheets 1 & 2, data from row2 down
(it's assumed companies listed in both sheets are unique)

In Sheet3,

With the same labels in A1:B1

Company Name Sales Fig.

Put in A2:
=IF(ISERROR(SMALL($C:$C,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2

Put in C2:
=IF(Sheet1!A2="","",IF(ISNUMBER(MATCH(Sheet1!A2,Sheet2!A:A,0)),MATCH(Sheet1!
A2,Sheet2!A:A,0),""))

Select A2:C2, fill down to say, C50,
to cover the max expected extent of data in Sheet1's col A

Sheet3 will auto-return the required results, i.e. only the companies in
Sheet1 which match those in Sheet2, with results sorted in the same order as
the list in Sheet2's col A, all lines neatly bunched at the top ..