Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find duplicate data in diffrent column
i wanna know how to find those cell in a worksheet which are same for example
question columnA columnB 1 9 3 2 2 8 4 7 5 6 6 5 7 4 need answer in following condition AA) following are maching with others columA columnB 2 2 4 4 5 5 6 6 7 7 BB) and these are no mach or diffrent with others 1 8 3 9 hope every think is clear if you need further please feel free to ask basicaly it is my big problem i m losting my lot of time bcz of this manual serching... Malik Nadeem |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find duplicate data in diffrent column
Here's one interp and a play to try ..
Sample construct available at: http://www.savefile.com/files/8194366 Compare 2 numbers col n Extr match n unmatch in ascend ord.xls Assuming source data are numbers in cols A and B, from row1 down to a max expected row10 (say) Place In C1: =IF(ROW(A1)COUNT(H:H),"",INDEX(A:A,MATCH(SMALL(H: H,ROW(A1)),H:H,0))) In D1: =IF(ROW(A1)COUNT(J:J),"",INDEX(B:B,MATCH(SMALL(J: J,ROW(A1)),J:J,0))) In E1: =IF(ROW(A1)COUNT(G:G),"",INDEX(A:A,MATCH(SMALL(G: G,ROW(A1)),G:G,0))) In F1: =IF(ROW(A1)COUNT(I:I),"",INDEX(B:B,MATCH(SMALL(I: I,ROW(A1)),I:I,0))) Then place in: In G1: =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"",A1+RO W()/10^10)) In H1: =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),A1+ROW()/10^10,"")) In I1: =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",B1+RO W()/10^10)) In J1: =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),B1+ROW()/10^10,"")) Select C1:J1, fill down to J10 (cover the max expected extent of data in cols A and B) Col C returns an ascending sort of the numbers in col A found in col B Col D returns an ascending sort of the numbers in col B found in col A Col E returns an ascending sort of the numbers in col A not found in col B Col F returns an ascending sort of the numbers in col B not found in col A Cols G to J are corresponding criteria cols with arb tie-breakers for extracting in ascending sort order -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Malik Nadeem" wrote: i wanna know how to find those cell in a worksheet which are same for example question columnA columnB 1 9 3 2 2 8 4 7 5 6 6 5 7 4 need answer in following condition AA) following are maching with others columA columnB 2 2 4 4 5 5 6 6 7 7 BB) and these are no mach or diffrent with others 1 8 3 9 hope every think is clear if you need further please feel free to ask basicaly it is my big problem i m losting my lot of time bcz of this manual serching... Malik Nadeem |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
How can I automatically remove duplicate data within a column? | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Another question on how to find duplicate data | Excel Worksheet Functions |