Re: How to sort/update large excel db

From: Dave Peterson (ec35720_at_msn.com)
Date: 10/04/04 

Date: Mon, 04 Oct 2004 16:02:28 -0500

Each one of those rows represents one record, right?

Once you get the key, then you'll be able to match up on that. And you
shouldn't have to worry about fields that are empty.

===
But I think you should spend some time thinking about how your database should
be laid out. Define the key and each field and make sure everyone plays by the
rule.

I don't speak the Access, but I'd be you get the same kind of suggestions--spend
some time thinking/designing your database in Access. (As someone who doesn't
know Access, I bet it's even more stringent when it comes to following the
fields defined.)

ConfusedNovice wrote:
>
> Thanks a lot. I'll find out how the db repairs worked out
> sometime soon.
>
> For a unique key value, I am thinking of date &
> incrementing record number: "100404-1" Only 1 person is
> likely to be entering data at any given time, but it would
> be easy to add a terminal designation if needed. There are
> *many* records that have blank cells in various fields, &
> I haven't been able to come up with any other ideas for
> unique values.
>
> About "slipping," maybe I should try to illustrate:
> 1)Older version of db:
> Last First Company Address
> Bush GW 1600 Penn
> Doe 123 Main St
> Co & Co 25 Oak St
> Jones John 111 Elm St
> Jones Mary 456 Main St
>
>
> 2)Later version of db:
> Bush GW 1600 Penn
> Doe John 123 Main St
> Co & Co 25 Oak St
> Jones Pedro 111 Elm St
> Jones Mary 456 Main St
>
> Sometimes the shift seems to involve several nearby
> records & simple fill-in's of blank cells, but other times
> it's not that straightforward. Some blanks were preserved,
> some weren't. Some records were ok, some weren't. I've
> been doing a lot of hand comparisons among whatever db
> versions & paper records I could get. Just guessing, I'd
> say serious problems could have been introduced on any or
> all of 3 monthly updates. Lots of reconstruction, very
> tedious.
>
> A few yrs ago I was an Access user, but I've never set it
> up for something like this. Seems like it might be harder
> to break. How could it be any worse than what I've got now
> with Excel? Only other db experience = Unidata, never did
> anything more than simple math spreadsheets in Excel.
>
> Jane
>
> >-----Original Message-----
> >I'm not sure I understand the slipping into empty cells
> stuff.
> >
> >But I find it better to use a field that must be entered
> in the list as the
> >key--if you make an arbitrary key value (some numbering
> system that only you
> >know is important), then it won't be updated (correctly)
> by the users--well, I
> >wouldn't!
> >
> >But by using a field that must be completed--even if it
> is a concatenation of
> >cells to make it unique, it makes more sense to most
> users--and if it's used,
> >there's a better chance of typing the values correctly.
> >
> >(That won't always happen, but hopefully it'll be small
> potatoes--not a giant
> >problem.)
> >
> >And as long as you have that key, you can sort/filter by
> anything you want. And
> >just resort by that key.
> >
> >And I don't speak the Access, but if you really want
> multiple people to update
> >records, you may want to consider doing it in Access (or
> a different real
> >database program). From what little I understand, you're
> forced to use a key
> >and it allows simultaneous edits (to different records (I
> think).
> >
> >====
> >One last note. Save often. And do File|SaveAs often.
> You may find that you
> >want to go back a couple of updates if you screw
> something up. And I bet you're
> >making changes at a furious rate.
> >
> >(Disk space is cheap--starting over is expensive!)
> >
> >
> >
> >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 *** 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: Re: How to sort/update large excel db
> >> From: "Dave Peterson" <ec35720@msn.com>
> >> 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 work*** 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 work***.
> >>
> >> And use the equivalent to check the other work***.
> >>
> >> 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 work***--
> 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 work***.
> >> > (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 ***?
> >> > 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.)
> >> > >
> >> > >
> >
> >--
> >
> >Dave Peterson
> >ec35720@msn.com
> >.
> > 

-- 
Dave Peterson
ec35720@msn.com