Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    99

    Unanswered: closing gaps on columns?

    I have an Excel spreadsheet for a mailshot with coname, address, city etc and then several columns with exec1, exec2 etc up to exec30.

    Assuming I only have exec1, exec2, exec3 and exec9 filled is there any way I can close the gap between exec3 and exec9.

    Can't think of the techie term but basically I want to bring the gaps together to make table neater.

    Any ideas how to do this much appreciated!!

    Thanks,

    David
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by pachamama
    Assuming I only have exec1, exec2, exec3 and exec9 filled is there any way I can close the gap between exec3 and exec9.

    Can't think of the techie term but basically I want to bring the gaps together to make table neater.
    Here's a few things you can do.

    1. Rightclick the column header and select "Hide Column" to hide the columns you do not wish to see.

    2. Use Excel's "Pivot Table" Feature to create a pivot table of your data that will show a list of companies and non-blank Exec fields for each company.

    3. make a companyID field for each company then make a new sheet and list all your execs (in a list format, all in a single column vs across several columns) with the companyID to associate to a company. Then use the "Excel Filter" feature to filter just the execs for a company. You can create a macro to take the companyID as filter criteria, when you double click or click a button from your company worksheet, which then activates the Exec list worksheet with that company's execs showing.

    An advantage of having a separate Execs worksheet in a list format is you can have columns for contact info, birthday, dogs name, kids name etc...
    ~

    Bill

  3. #3
    Join Date
    Feb 2004
    Posts
    99

    thanks for the answer, woosh it has gone way over my head

    Thanks Bill, see that solution you gave me.... woosh it has gone way over my head!!!

    Have had a tinker with it but little success, the Pivot Table answer looks great but I can't get my head around it!

    Will fiddle about later with it.

    Thanks again,

    David
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    The pivot tables are great. But I thought about how you say the data is, and see where the pivot table will not do what I said without changing your data.

    You have data like this:

    Company1, Exec1, Exec2, Exec3, Exec4...
    Company2, Exec1, Exec2, (blank), Exec4...

    For better data handling, and effective use in pivot tables you would need data like this:

    Company1, Exec1
    Company1, Exec2
    Company1, Exec3
    Company2, Exec1
    Company2, Exec2

    You see how this way can eliminate the blank exec fields in this form and have room to add more than 9 execs per company? This is known as Data Normalization, one of the necessary things to know if you are going to do much database or list work. It may be worth the effort to reformat your list to work like this.

    Once in a more 'Normalized' format you can use the pivot table to summerize the data. the Pivot table wizard will help you. It shows a data grid where you can drop the headers to construct the pivot table. You will put the Company and Exec Name fileds as Row Headers, and can put Exec name in the Data area of the grid to give a count of Execs per company.
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •