[pachamama]

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

[savbill]

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...

[pachamama]

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

[savbill]

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.