Re: Need help from an Excel guru

[Peter Neilson <neilson -at- windstream -dot- net>]

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