confused,

i know your project is probably over by now but i came across your post
today and had to reply. i too suffer from "data slip" and you are the first
person to talk about. i am wondering if you have had any luck resolving the
problem. i understand select all needs to be done before sorting and i am
assuming you do this as well.

i have a similar spreadsheet in terms of size but mine deals with projects
and milestones so there are lots of dates on a single row. i began to find
dates that were way out of place. soon i began testing. i would enter a date
in a cell, save the ss, close, reopen, and the data was in a different cell.
with mine it didn't just slip to empty cells. i suspect your data copied over
other data also but it was harder to spot. my ss is shared by multiple users.
i have also noticed that the change history will not record changes correctly
(i.e. a cell which has not been changed will show a change made to it while
the cell whose data was changed does not show a change). the only way i can
resolve the problem is to unshare the ss, fix the cells and then reshare it.
this has really become a problem.

none of the techs i have spoken with have been able to recreate the problem.
the latest suggesting i have been given is to make sure all versions of excel
which edit the ss have been fully updated. like you i sort and filter the ss
all the time.

as a side note, i too am a novice compared to the average person answering
posts on this site but i had a few thoughts about your project if it could
still help. first, don't sort while the ss is filtered, this will hang your
computer forever - sort first then filter. second, this is a low tech
solution for comparing a ss without having a unique key column - what about
creating one ss which at least has all the last names filled in and make
another which has at least the zip and so on. compare each to another ss
which has been prepared the same way. when finished merge them back together.
i'm not sure it that makes any sense but maybe it will help.

anyway, anything you can share about "data slip" would be appreciated. thanks,

scott


"ConfusedNovice" wrote:

Thanks, Dave. This thread is getting unwieldy, but I
*think* I understand what you're advising.

Sticking with the issues at hand, yes, the records are all
one per row. Last Name is probably the best key, but there
are some records with only a Company Name, & a few with
only an email name. I have been using Last Name & Zip
Codes to try & match things up.

I discovered that when sorting some of the intermediate db
versions by "new" entries, those newest entries were
mostly correct. I am thinking of isolating these records &
copy/pasting them on to the oldest db version. The idea is
to try for a cleaner sheet to compare to the current one.
Maybe it would be better not to merge these? It's crunch
time - I will try your formulae on a test file today.

And moving ahead: If I can execute the comparisons you
suggest, and if I can achieve a cleaner list, then what's
the best way to prevent data from slipping into the empty
cells in the future? Should I make a new index column,
number each record, and append & number all new records?
Can people safely sort & filter the data? What if they
update a record in a filtered list & then re-sort? Or
Find/Replace & then re-sort? I still don't understand how
some of the data slipped out of one record & into another.
Until I do understand, I'm afraid it will continue to
happen & all this work will be for nothing.

Thanks again! It's an ugly way to learn Excel.

Jane

Subject: How to sort/update large excel db
From: "Dave Peterson"
Sent: 9/30/2004 1:05:46 PM

Just to clarify, is your data laid out one record per
row? You don't use
multiple rows to represent one "logical" record, do you?

If you do, then this will make it even more difficult.

But to match/merge two different worksheets, I would think
that you'd need to
have a unique key/index into the data.

If you don't have this key, how do you match up your data
to look for
differences?

I wouldn't put too much faith in having that unique key in
the last name
column--but maybe you can use a couple of columns that
could serve as that key.

For instance, if you had the lastname, firstname, address
in 3 separate columns,
you could use a helper cell and do something like:
=A2&char(10)&b2&char(10)&c2
(The char(10) probably won't appear in your data and will
serve as a field
delimiter--just in case there's records that might look
identical when
combined.)

But even if you did this, you'd have to make sure that
these fields weren't
changed. If any were changed, then you couldn't use them
for a match between
worksheets.

To find out if the records in one worksheet are in the
other (based on any
single column you can use--in your data or derived from
your data):

=isnumber(match(a2,sheet1!a:a,0))
and drag down.
You'll see True if it appears on that other worksheet.

And use the equivalent to check the other worksheet.

Once you've found your Falses--you'll want to inspect them
to see if they should
be cleansed--cleaned up to make them match.

You could end up fixing the data on either worksheet--just
to find the matches.

Once you get rid of all the Falses, you can do the =vlookup
() stuff to match
merge.

I'm not sure if all this work can be done on a quick
schedule. This kind of
stuff usually means fix, check, fix, check....until you
can't find any more
differences. Then you let someone else review it and it
starts again.

Good luck,



ConfusedNovice wrote:

Still trying to understand what I've got. There is no
column with all unique entries. Every column has blank
cells someplace. On 2 of the earlier database versions,
the leftmost column (Column A) is set up as "Index
Column," but not all the records have an "Index" number
in
this column - some of them are blank, & some have zeros
in
Column A. The most recent master does not have Index
column, it starts with a Last Name field.

This brings me back to the question of how to prevent
names/values from slipping into the blank cells when
sorting & updating records. I have been reading a lot of
Excel info, but this is *not* my area at all, & I want to
fix these problems, not compound them. Should I be able
to
identify key column(s)? If yes, how? And if no, is there
any safe way to sort when blanks exist in every column?
(Or can it be a Key & contain multiple blank cells?)

There are ~17,000 records, & when the wrong values move
into a blank cell, that record doesn't indicate that it
has been changed. (Probably because it was not opened?)

I'm seriously running out of time. I've got a pretty big
list of changes ready to enter directly on db records. I
also have isolated a few hundred recent entries by date,
verified them, & placed them in a separate worksheet.
(Hoping to append to a clean main list & sort into
place.)
I don't think it's possible to verify every row.

Yes, no matter how you look at this, it's a pain! I'm not
really dumb, just a total rookie, & your help is
invaluable.
To summarize:
Whatsup with "Index Column"?
How to identify/designate Key columns?
Implications of having blank cells throughout sheet?
Fastest way to add/edit records if I go back to an older,
cleaner version of the database?

Thanks again!

Jane

-----Original Message-----
I'm saying that if you have two "live" copies of the
workbook, it'll be a pain
to merge one of them back into the other.

There's nothing built into excel that will force you to
make sure your key
column entries are unique.

When I do this, I spend some time verifying that the
data
is at least
consistent--no additional entries with the same key were
added. If you can
believe/verify this, then, yeah, you can do =vlookup()'s
to merge the updated
values into the one real workbook.

It's just in my experience, avoiding this is usually
better. (Just give the
file to someone else and tell them to return it when
they're done--and put a
freeze on any other changes.)