finding out missing entries!

[via135]

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

 

[Bernard Liengme]

Visit Chips site and look for his stuff on duplicates
http://www.cpearson.com/excel/duplicat.htm

 

[Guest]

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.

 

[via135]

thks..Kevin!

though the route is circuitous, i am able to achieve the result!

thks again for the help!

-via135

 

[via135]

thks Bernard!
for the prompt guidance!!

-via135

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

in
message news:[email protected]...

 

[Guest]

You're welcome.
--
Kevin Vaughn

thks..Kevin!

though the route is circuitous, i am able to achieve the result!

thks again for the help!

-via135