How do I compare info in on sheet to info in another?

[Fanney]

Hi out there.
I wanted to ask if it is at all possible to compare info in one sheet to
info in another sheet (in the same workbook) when double info is not
necessarily located in the same cell in both sheets.

Background: I have one list of files registered in a data base and a list of
files actually on a hard drive. I need to compare the two list both ways to
check if there are any files on the drive, not registered in the data base
and, vice versa, if there are any files registered into the data base that
are not on the hard drive.

Any help would be greatly appreciated.

[Max]

One way ..

Try this previous sample for a similar query (details inside):
http://cjoint.com/?cwlDMQRsgf
Compare 2 data sets & extract differences_matsgulis.xls

The construct assumes that the key col data to be compared is placed
side-by-side in cols A and B on a single sheet (you could just copy > paste
from your 2 sheets into a new sheet), and extracts out neatly the items in
one set not found in the other set.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fanney" <[email protected]> wrote in message
news:[email protected]...
> Hi out there.
> I wanted to ask if it is at all possible to compare info in one sheet to
> info in another sheet (in the same workbook) when double info is not
> necessarily located in the same cell in both sheets.
>
> Background: I have one list of files registered in a data base and a list
of
> files actually on a hard drive. I need to compare the two list both ways
to
> check if there are any files on the drive, not registered in the data base
> and, vice versa, if there are any files registered into the data base that
> are not on the hard drive.
>
> Any help would be greatly appreciated.
>
>

[Fanney]

Hi Max.
Thank you for your answer :o)

I have a problem. The data that I need to compare is not always exactly the
same. The numbers in list B [set2] are contained within the numbers in list A
[set1].
An example: 50-02-1294-C2 in list A would be 1294 in list B.

What complicates things even a little further is that there is not always a
"-" separating the different "fragments" of the numbers in list A.

As my lists are now, using the functions directly from your example gives me
a duplicate of [set1] in [set1 items not in set2], and a duplicate of [set2]
in [set2 items not in set1].

Is my goal at all attainable?

Regards, Fanney.

"Max" wrote:

> One way ..
>
> Try this previous sample for a similar query (details inside):
> http://cjoint.com/?cwlDMQRsgf
> Compare 2 data sets & extract differences_matsgulis.xls
>
> The construct assumes that the key col data to be compared is placed
> side-by-side in cols A and B on a single sheet (you could just copy > paste
> from your 2 sheets into a new sheet), and extracts out neatly the items in
> one set not found in the other set.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Fanney" <[email protected]> wrote in message
> news:[email protected]...
> > Hi out there.
> > I wanted to ask if it is at all possible to compare info in one sheet to
> > info in another sheet (in the same workbook) when double info is not
> > necessarily located in the same cell in both sheets.
> >
> > Background: I have one list of files registered in a data base and a list
> of
> > files actually on a hard drive. I need to compare the two list both ways
> to
> > check if there are any files on the drive, not registered in the data base
> > and, vice versa, if there are any files registered into the data base that
> > are not on the hard drive.
> >
> > Any help would be greatly appreciated.
> >
> >
>
>
>

[trav]

I know this isn't an excel answer, if you are comparing file structures of a hard drive versus your server, you should look into some FTP programs and server back up programs. They would do a much better job of keeping track of files, syncronizing and alerting you to missing files. I can't recommend one off the top of my head, but i know our Server Admin has some nifty tools that run one or twice a day syncronizing and backing up different drives. do some google searching, or talk to some server guys. But I think it might be more helpful.

also, if you are looking to compare data and run reports, you might want to consider building a small access database and then you could run queries that compare only parts of the file name, and you could also set it up to just run a report and spit out the files you need to look at. I would be fairly easy to set up a data base like that. I am not as familiar with access and VB as i am with mysql and php, but i know that it is possible in php and mysql to create a cron job that runs through a site and stores the path and file name as a string in the data base. I am sure you could do this with vb and access.

trav

[Max]

Probably not foolproof given the circumstances, but we could try this
revised formula in the sample sheet's E2, copied down:
=IF(B2="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(B2,$A$1:$A$20)))>0,"",ROW()))

Col B: 1294, 1356, etc
Col A: 50-02-1294-C2, 50-02-1356C2,etc

Adapt the range A1:A20 to suit
(but we can't use entire col refs)

[no change to the other formulas]

See whether the above gives you better results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fanney" <[email protected]> wrote in message
news:[email protected]...
> Hi Max.
> Thank you for your answer :o)
>
> I have a problem. The data that I need to compare is not always exactly
the
> same. The numbers in list B [set2] are contained within the numbers in
list A
> [set1].
> An example: 50-02-1294-C2 in list A would be 1294 in list B.
>
> What complicates things even a little further is that there is not always
a
> "-" separating the different "fragments" of the numbers in list A.
>
> As my lists are now, using the functions directly from your example gives
me
> a duplicate of [set1] in [set1 items not in set2], and a duplicate of
[set2]
> in [set2 items not in set1].
>
> Is my goal at all attainable?
>
> Regards, Fanney.

[Max]

Here's the revised sample implementation with some test data:
http://cjoint.com/?cycIER0v0M
Compare 2 data sets & extract diff (Fuzzy search).xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Fanney]

Thanks for your advice, Trav. I have the IT-guys looking into it.

Max,
This worked, when looking for the "short names" (list B) among the "long
names" (list A) :o)
I now have a nice list of files registered in the database, but missing on
the drive.

However, looking for the long names among the short, is difficult, since
Excel can not distinguish which part of the long names it should be searching
for.

Maybe I will just have to shorten all the long names, to mach the short ones?
I'm examining my options there.

PS. Great web-site you have. & have a nice weekend.


"Max" wrote:

> Here's the revised sample implementation with some test data:
> http://cjoint.com/?cycIER0v0M
> Compare 2 data sets & extract diff (Fuzzy search).xls
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
>
>

[Max]

"Fanney" wrote:
...
> Max, This worked, when looking for the "short names" (list B) among the
"long names" (list A) :o) I now have a nice list of files registered in the
database, but missing on the drive.

Glad it helped !

> However, looking for the long names among the short, is difficult, since
Excel can not distinguish which part of the long names it should be
searching for. Maybe I will just have to shorten all the long names, to mach
the short ones? I'm examining my options there.

Yes, it's tough. Perhaps you might want to put in a *new* post just
focusing on this topic. Post some samples of your actual data. Maybe other
responders could offer you their insights.

> PS. Great web-site you have ..

Thanks. It's just a samples archive for ref by those interested.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---