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

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

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

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

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

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