Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Match and Sort two range of data?
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Match and Sort two range of data?
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,Sh eet2!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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tan" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting data on protected worksheet | Excel Discussion (Misc queries) | |||
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 |