Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding DUplicate Data set in Worksheets
How can I find duplicate data set..Say My data is in Column A B And C from
Row 6 to row 200, Now I want to check whether the data depicted in Column A, B and C are also stored else where say in Columns G, H and I..? I have three fields In column A is Date, In Column B is IDs and In column C is stored AMount, Now I want to Match the DAte, Id and AMount (1 set of Data) to the data in Coumns G H and I.. and if the same is not found I want it to be Listed saperately.. Can ANyone help me pls.? SAt |
#2
|
|||
|
|||
One play ..
Using 4 empty cols to the right, say, cols K to N Put in the formula bar for K6, array-enter i.e. press CTRL+SHIFT+ENTER: =IF(ISNUMBER(MATCH(1,($G$6:$G$200=A6) *($H$6:$H$200=B6)*($I$6:$I$200=C6),0)),"",ROW()) Put in L6: =IF(ISERROR(SMALL($K$6:$K$200,ROWS($A$1:A1))),"", INDEX(A$6:A$200,MATCH(SMALL($K$6:$K$200, ROWS($A$1:A1)),$K$6:$K$200,0))) (Normal ENTER will do) Copy L6 across to N6 Format L6 as date Select K6:N6, fill down until the last row of data in cols A to C, i.e. to N200 The required listing / results will be returned within L6:N200, all neatly bunched at the top, i.e. those data-sets within A6:C200 which are not found within G6:I200 Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "SAT" wrote in message ... How can I find duplicate data set..Say My data is in Column A B And C from Row 6 to row 200, Now I want to check whether the data depicted in Column A, B and C are also stored else where say in Columns G, H and I..? I have three fields In column A is Date, In Column B is IDs and In column C is stored AMount, Now I want to Match the DAte, Id and AMount (1 set of Data) to the data in Coumns G H and I.. and if the same is not found I want it to be Listed saperately.. Can ANyone help me pls.? SAt |
#3
|
|||
|
|||
Here's a link to a sample file with the implemented construct:
http://www.savefile.com/files/5733440 File: Finding Duplicate Data set in Worksheets_SAT_wksht.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#4
|
|||
|
|||
Dear MAx..
Thanx.. I have downloded the example.. I will try it on my data and let you know.. Thanx again for all the trouble. Regards. bye. SAT "Max" wrote: Here's a link to a sample file with the implemented construct: http://www.savefile.com/files/5733440 File: Finding Duplicate Data set in Worksheets_SAT_wksht.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
|
|||
|
|||
You're welcome !
Trust it'll work for you -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique and duplicate data between 2 Excel worksheets | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
How do I sort data into 2 worksheets in excel vba? | Excel Worksheet Functions | |||
Finding Data in multiple worksheets | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |