software

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 3000I 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, quot;A Limitedquot; and quot;B Limitedquot; 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 rowsThanksOne 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 amp; 2, data from row2 down
(it's assumed companies listed in both sheets are unique)

In Sheet3,

With the same labels in A1:B1
gt; Company Name Sales Fig.

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

Put in C2:
=IF(Sheet1!A2=quot;quot;,quot;quot;,IF(ISNUMBER(MATCH(Sheet1!A2,Sh eet2!A:A,0)),MATCH(Sheet1!
A2,Sheet2!A:A,0),quot;quot;))

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 ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Tanquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; Appreciate if anyone could help out on this issue. I have a range of datas
gt; on sheet 1 and another range of data on sheet 2. Sample as below :
gt;
gt; In sheet 1,
gt;
gt; Company Name Sales Fig.
gt; D Limited 4000
gt; A Limited 1000
gt; E Limited 5000
gt; B Limited 2000
gt; C Limited 3000
gt;
gt; In sheet 2,
gt;
gt; Company Name Sales Fig.
gt; E Limited 5000
gt; D Limited 4000
gt; C Limited 3000
gt;
gt;
gt; I want to match data in sheet 1 to my data in sheet 2 becos my priorty is
gt; the data in my sheet 2.
gt; As such, quot;A Limitedquot; and quot;B Limitedquot; must be flag as unmatch entry in
sheet
gt; 1 and be deleted immediately. On top of these, the data in my sheet 1 must
be
gt; sorted the same sequence as sheet 2. That is, in sheet 1 shown as
gt;
gt; Company Name Sales Fig.
gt; E Limited 5000
gt; D Limited 4000
gt; C Limited 3000
gt;
gt; That is same data as my sheet 2 at last. I cant use custom list in excel
gt; becos my range of data will be as many as few hundred rows
gt;
gt;
gt; Thanks
gt;