Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding The Missing Data
I have 2 worksheets in a file. The first worksheet has six thousand
rows and 10 columns and the second sheet has only 4593 rows and 8 columns. All the names on the second sheet are already on the first sheet. I need a quickway to find all the missing names and to insert these names that are missing from the second sheet into the second sheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding The Missing Data
One play ..
Assume the 1st sheet is named as: X, data in cols A to J, from row1 down, where the key col is col A (names). Assume the 2nd sheet is named as: Y, data in cols A to H, from row1 down, where the key col is also col A (names). In a new sheet, Put in A1: =IF(X!A1="","",IF(ISNUMBER(MATCH(X!A1,Y!A:A,0)),"" ,ROW())) Put in B1: =IF(ROW()COUNT($A:$A),"",INDEX(X!A:A,SMALL($A:$A, ROW()))) Copy B1 across 10 cols to K1. Select A1:K1, fill down to cover the extent of data in X, ie down by 6000 rows to K6000. Cols B to K will return only the missing lines from X, ie lines with names not found in Y, with all results neatly bunched at the top. You could then just select the result lines & do a copy n paste special as values below the data in Y, assuming of course, that the first 8 of the 10 cols in X are identical to the 8 cols in Y. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Biruzz" wrote in message oups.com... I have 2 worksheets in a file. The first worksheet has six thousand rows and 10 columns and the second sheet has only 4593 rows and 8 columns. All the names on the second sheet are already on the first sheet. I need a quickway to find all the missing names and to insert these names that are missing from the second sheet into the second sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Finding Missing Data | Excel Worksheet Functions | |||
Missing data using the autofilter | Excel Discussion (Misc queries) | |||
Numbers/Text data missing from excel to excel query? | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions |