Re: Need help from an Excel guru

Subject: Re: Need help from an Excel guru
From: Peter Neilson <neilson -at- windstream -dot- net>
To: techwr-l -at- lists -dot- techwr-l -dot- com
Date: Thu, 16 Dec 2010 02:00:04 -0500

I'd export to a CSV file and then use my favorite powerful text editor
(emacs, of course) to solve the problem, Then I'd import the CSV back
into Excel.

In the course of the project I would worry that some of the cells in the
table might contain "other" data such as comments, or notes describing
exceptional aspects of the data in other cells. This is a very common
misuse of tabular data. Here's what I mean:

xxxxx erergkerg
juwe
(pink) ejrhghjkerg

Here's another, more elaborate:

TRUCKNUM REG STATE
35 NC
SC
GA
36 VA
NC
MD
DEL VAN AL OWNS IT, JOE HAS IT REG IN
MD
85 (FAKENUM) BUT KEPT IN DC MD REG OK
VA
37 PA
38 PA
VA
NC

That delivery van was shoehorned into a table where it simply does not
belong. Contrary to appearances, it is not registered in Alabama. Its
number, 85, is a fake, with that value having some hidden meaning.
Perhaps "regular" trucks don't have numbers beyond 60.

I would also worry that some of the cells in John's column two might
contain multiple values. For example, instead of:

xxxxx erergkerg
juwe
ejrhghjkerg

one or more sets of rows might be in this form:

xxxxx erergkerg, juwe
ejrhghjkerg

Straying further afield, into a more general view of this kind of work,
the underlying project (perhaps not what John is seeing right now) may
involve converting the table into something that matches the
requirements of a database system. The process is sometimes called
normalizing, and often requires consultation and argumentation with the
SME people to figure out what is REALLY in the table and how it REALLY
is going to be used. Actions previously applied to data (a limit to the
number of characters in a field, or the use of "hidden-meaning" codes)
may require careful unwinding that is not initially obvious. The DB
gurus will almost always find that they need to create additional tables.

Even in large companies with good DB systems, such as Oracle, Teradata,
or DB2, it is common to find business functions being handled through
misuse of MS Access and even MS Excel files. Sometimes the method for
"getting work done" involves massive daily updates of tables from one
department's PC to others', rather than making use of proper DB
resources already available.

On 12/15/2010 09:00 PM, John Posada wrote:
> Hi, guys...long time...
>
> I find myself with a task where I have an Excel spreadsheet with about 5,000
> rows. It is a database of URLs from my knowledgebase.
>
> It looks similar to the following:
>
>
> xxxxx erergkerg
> juwe
> ejrhghjkerg
> zzzzz ueeefjvbhfb
> jwerfjerjef
> erfpiquer
> yyyyy ehqerf
> wjkf;kjqbb
> kjkf;kjb
>
> You get the idea.
>
> I want to automate the process of copying each string in column one to each
> row below it until the value changes, then copy that one until it changes,
> to the end.
>
> I would end up with:
>
> xxxxx erergkerg
> xxxxx juwe
> xxxxx ejrhghjkerg
> zzzzz ueeefjvbhfb
> zzzzz jwerfjerjef
> zzzzz erfpiquer
> yyyyy ehqerf
> yyyyy wjkf;kjqbb
> yyyyy kjkf;kjb
>
> I'm aware that I can highlight the string and all the empty ones below it
> and press CTRL-d, but I have about 1600 sets. Is there was a way automate
> it?
>
> Thanks
>

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Create and publish documentation through multiple channels with Doc-To-Help.
Choose your authoring formats and get any output you may need. Try
Doc-To-Help, now with MS SharePoint integration, free for 30-days.
http://www.doctohelp.com

---
You are currently subscribed to TECHWR-L as archive -at- web -dot- techwr-l -dot- com -dot-

To unsubscribe send a blank email to
techwr-l-unsubscribe -at- lists -dot- techwr-l -dot- com
or visit http://lists.techwr-l.com/mailman/options/techwr-l/archive%40web.techwr-l.com


To subscribe, send a blank email to techwr-l-join -at- lists -dot- techwr-l -dot- com

Send administrative questions to admin -at- techwr-l -dot- com -dot- Visit
http://www.techwr-l.com/ for more resources and info.

Please move off-topic discussions to the Chat list, at:
http://lists.techwr-l.com/mailman/listinfo/techwr-l-chat


Follow-Ups:

References:
Need help from an Excel guru: From: John Posada

Previous by Author: RE: New rules for business correspondence
Next by Author: Re: New rules for business correspondence
Previous by Thread: Re: MadCap Flare - footnotes with hyperlinks
Next by Thread: Re: Need help from an Excel guru


What this post helpful? Share it with friends and colleagues:

Sponsored Ads


Sponsored Ads