Find Dropouts in Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Find Dropouts in Excel

Author

Topic

younker1983
Starting Member

3 Posts

Posted - 2006-08-11 : 06:49:58

hi all

i m facing one problem.. in excel

i have 2 different spreadsheets
sheet 1 and sheet 2
in sheet 1 i have suppouse following data
Name Prod. value
a 24 100
b 26 240
a 33 120
a 21 200
c 26 240
b 33 120
d 24 100

in sheet 2

Name Prod. value
a 24 100
d 26 240
a 33 120
e 26 240
a 21 200
c 26 240
d 24 100

in this situation i want to find out the dropouts of sheet 1. (that persons which are not present the sheet 2)
how can i compare these sheets?
in this case i want the result as

Name Prod. value
b 26 244
b 33 120

Thanx & Regards
Manu Verma

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-11 : 07:36:52

Try this...

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Test.xls;HDR=YES','SELECT * FROM [Sheet1$]') as x
where not exists (Select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Test.xls;HDR=YES','SELECT * FROM [Sheet2$]') as y
where x.Name = y.Name)

Harsh Athalye
India.
"Nothing is Impossible"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-08-11 : 10:58:16

You could always invoke the most suitable "Excel" function to do this...ie an implementation of the 'VLookup' function!! Bringing 'SQL' into the equation just complicates matters!

younker1983
Starting Member

3 Posts

Posted - 2006-08-14 : 04:38:29

if i merge the both sheets

in sheet1

Name-- custcode -- Prod. --value --year
-- a -- 001 -- 24 -- 100 -- 2004
-- b -- 002 -- 26 -- 240 -- 2004
-- a -- 001 -- 33 -- 120 -- 2004
-- a -- 001 -- 21 -- 200 -- 2004
-- c -- 003 -- 26 -- 240 -- 2004
-- b -- 002 -- 33 -- 120 -- 2004
-- d -- 004 -- 24 -- 100 -- 2004

in sheet 2

Name -- CustCode -- Prod. -- value -- year
-- a -- 001 -- 24 -- 100 -- 2005
-- d -- 004 -- 26 -- 240 -- 2005
-- a -- 001 -- 33 -- 120 -- 2005
-- e -- 005 -- 26 -- 240 -- 2005
-- a -- 001 -- 21 -- 200 -- 2005
-- c -- 003 -- 26 -- 240 -- 2005
-- d -- 004 -- 24 -- 100 -- 2005

firstly i create the unique values from both sheets

Name-- custcode -- Prod. --value --year
-- a -- 001 -- 24 -- 100 -- 2004
-- b -- 002 -- 26 -- 240 -- 2004
-- c -- 003 -- 26 -- 240 -- 2004
-- d -- 004 -- 24 -- 100 -- 2004

sheet2
Name -- CustCode -- Prod. -- value -- year
-- a -- 001 -- 24 -- 100 -- 2005
-- d -- 004 -- 26 -- 240 -- 2005
-- e -- 005 -- 26 -- 240 -- 2005
-- c -- 003 -- 26 -- 240 -- 2005

then i merge the sheets copy all rows into one single sheet

sheet3
Name-- custcode -- Prod. --value --year
-- a -- 001 -- 24 -- 100 -- 2004
-- b -- 002 -- 26 -- 240 -- 2004
-- c -- 003 -- 26 -- 240 -- 2004
-- d -- 004 -- 24 -- 100 -- 2004
-- a -- 001 -- 24 -- 100 -- 2005
-- d -- 004 -- 26 -- 240 -- 2005
-- e -- 005 -- 26 -- 240 -- 2005
-- c -- 003 -- 26 -- 240 -- 2005

from this 3rd sheet i want to choose to retrieve only that records which are present in 2004 but not in 2005. in this example i want to retrieve only one record which is b--002-26-240-2004. the name B is the dropout in 2005 year. plz tell me how can i do this i have more than thousands row.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources