| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding out missing entries!
hi all! i am having data in 2 workbooks! in workbook1 sheet 1 COL"A" contains cheque numbers & COL"B" contains amounts as under chqno amount 123456 100 234561 200 546326 300 123406 400 654896 300 689647 150 465721 200 in workbook2 sheet1 - same two columns as in workbook1 sheet 1 the records are the same with a few lesser than in workbook1 sheet1 also the records are not in the same order but shuffled randomly! example as under: chqno amount 689647 150 546326 300 465721 200 123456 100 234561 200 now i want to trace out the following missing 2 entries in the workbook2sheet1! 123406 400 654896 300 help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
finding out missing entries!
Visit Chips site and look for his stuff on duplicates
http://www.cpearson.com/excel/duplicat.htm -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "via135" wrote in message ... hi all! i am having data in 2 workbooks! in workbook1 sheet 1 COL"A" contains cheque numbers & COL"B" contains amounts as under chqno amount 123456 100 234561 200 546326 300 123406 400 654896 300 689647 150 465721 200 in workbook2 sheet1 - same two columns as in workbook1 sheet 1 the records are the same with a few lesser than in workbook1 sheet1 also the records are not in the same order but shuffled randomly! example as under: chqno amount 689647 150 546326 300 465721 200 123456 100 234561 200 now i want to trace out the following missing 2 entries in the workbook2sheet1! 123406 400 654896 300 help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
finding out missing entries!
It would be easy to go from the spreadsheet that contains more entries and do
a vlookup on the sheet that contains fewer entries. The entries that didn't exist on the second sheet would show up as #N/A. But it seems you want to do the opposite, so this is what I came up with: In column C enter this formula (and copy down): =MATCH(A2,Sheet3!$A$2:$A$8,0) This will tell you on what row matching numbers were found. Then in column D enter the starting through ending range of your numbers. You can use Edit/fill series. For this example I used Series in columns, step value of 1, stop value of 7, so this gave me the numbers 1 - 7 in column D. Then in column E, I entered this formula (and copied down): =IF(COUNTIF($C$2:$C$6,D2)=0,1,"") This entered the number 1 in the rows where a match was not found. In column F, I used this formula: =IF($E2<"",INDEX(Sheet3!$A$2:$B$8,$D2,1),"") This returned the missing value in the first column and the formula in column G is: =IF($E2<"",INDEX(Sheet3!$A$2:$B$8,$D2,2),"") which is exactly the same as the previous formula except it returns the 2nd column's data. Doable, but as I said earlier, a lot easier to work from the other spreadsheet. -- Kevin Vaughn "via135" wrote: hi all! i am having data in 2 workbooks! in workbook1 sheet 1 COL"A" contains cheque numbers & COL"B" contains amounts as under chqno amount 123456 100 234561 200 546326 300 123406 400 654896 300 689647 150 465721 200 in workbook2 sheet1 - same two columns as in workbook1 sheet 1 the records are the same with a few lesser than in workbook1 sheet1 also the records are not in the same order but shuffled randomly! example as under: chqno amount 689647 150 546326 300 465721 200 123456 100 234561 200 now i want to trace out the following missing 2 entries in the workbook2sheet1! 123406 400 654896 300 help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
finding out missing entries!
thks..Kevin! though the route is circuitous, i am able to achieve the result! thks again for the help! -via135 Kevin Vaughn Wrote: It would be easy to go from the spreadsheet that contains more entries and do a vlookup on the sheet that contains fewer entries. The entries that didn't exist on the second sheet would show up as #N/A. But it seems you want to do the opposite, so this is what I came up with: In column C enter this formula (and copy down): =MATCH(A2,Sheet3!$A$2:$A$8,0) This will tell you on what row matching numbers were found. Then in column D enter the starting through ending range of your numbers. You can use Edit/fill series. For this example I used Series in columns, step value of 1, stop value of 7, so this gave me the numbers 1 - 7 in column D. Then in column E, I entered this formula (and copied down): =IF(COUNTIF($C$2:$C$6,D2)=0,1,"") This entered the number 1 in the rows where a match was not found. In column F, I used this formula: =IF($E2<"",INDEX(Sheet3!$A$2:$B$8,$D2,1),"") This returned the missing value in the first column and the formula in column G is: =IF($E2<"",INDEX(Sheet3!$A$2:$B$8,$D2,2),"") which is exactly the same as the previous formula except it returns the 2nd column's data. Doable, but as I said earlier, a lot easier to work from the other spreadsheet. -- Kevin Vaughn "via135" wrote: hi all! i am having data in 2 workbooks! in workbook1 sheet 1 COL"A" contains cheque numbers & COL"B" contains amounts as under chqno amount 123456 100 234561 200 546326 300 123406 400 654896 300 689647 150 465721 200 in workbook2 sheet1 - same two columns as in workbook1 sheet 1 the records are the same with a few lesser than in workbook1 sheet1 also the records are not in the same order but shuffled randomly! example as under: chqno amount 689647 150 546326 300 465721 200 123456 100 234561 200 now i want to trace out the following missing 2 entries in the workbook2sheet1! 123406 400 654896 300 help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
finding out missing entries!
thks Bernard! for the prompt guidance!! -via135 Bernard Liengme Wrote: Visit Chips site and look for his stuff on duplicates http://www.cpearson.com/excel/duplicat.htm -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "via135" wrote in message ... hi all! i am having data in 2 workbooks! in workbook1 sheet 1 COL"A" contains cheque numbers & COL"B" contains amounts as under chqno amount 123456 100 234561 200 546326 300 123406 400 654896 300 689647 150 465721 200 in workbook2 sheet1 - same two columns as in workbook1 sheet 1 the records are the same with a few lesser than in workbook1 sheet1 also the records are not in the same order but shuffled randomly! example as under: chqno amount 689647 150 546326 300 465721 200 123456 100 234561 200 now i want to trace out the following missing 2 entries in the workbook2sheet1! 123406 400 654896 300 help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
finding out missing entries!
You're welcome.
-- Kevin Vaughn "via135" wrote: thks..Kevin! though the route is circuitous, i am able to achieve the result! thks again for the help! -via135 Kevin Vaughn Wrote: It would be easy to go from the spreadsheet that contains more entries and do a vlookup on the sheet that contains fewer entries. The entries that didn't exist on the second sheet would show up as #N/A. But it seems you want to do the opposite, so this is what I came up with: In column C enter this formula (and copy down): =MATCH(A2,Sheet3!$A$2:$A$8,0) This will tell you on what row matching numbers were found. Then in column D enter the starting through ending range of your numbers. You can use Edit/fill series. For this example I used Series in columns, step value of 1, stop value of 7, so this gave me the numbers 1 - 7 in column D. Then in column E, I entered this formula (and copied down): =IF(COUNTIF($C$2:$C$6,D2)=0,1,"") This entered the number 1 in the rows where a match was not found. In column F, I used this formula: =IF($E2<"",INDEX(Sheet3!$A$2:$B$8,$D2,1),"") This returned the missing value in the first column and the formula in column G is: =IF($E2<"",INDEX(Sheet3!$A$2:$B$8,$D2,2),"") which is exactly the same as the previous formula except it returns the 2nd column's data. Doable, but as I said earlier, a lot easier to work from the other spreadsheet. -- Kevin Vaughn "via135" wrote: hi all! i am having data in 2 workbooks! in workbook1 sheet 1 COL"A" contains cheque numbers & COL"B" contains amounts as under chqno amount 123456 100 234561 200 546326 300 123406 400 654896 300 689647 150 465721 200 in workbook2 sheet1 - same two columns as in workbook1 sheet 1 the records are the same with a few lesser than in workbook1 sheet1 also the records are not in the same order but shuffled randomly! example as under: chqno amount 689647 150 546326 300 465721 200 123456 100 234561 200 now i want to trace out the following missing 2 entries in the workbook2sheet1! 123406 400 654896 300 help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510173 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with finding duplicate entries | Excel Worksheet Functions | |||
| Inserting and Tracking Missing Sequence | Excel Discussion (Misc queries) | |||
| Finding duplicate cell entries in a column of data | Excel Discussion (Misc queries) | |||
| How do I change multi-line entries to single line entries in Exce. | Excel Worksheet Functions | |||
| Finding numbers missing from a sequence | Excel Discussion (Misc queries) | |||
Linear Mode
