create a contact database in excel

[contactcp]

I am trying to build up a database of contacts into a sales program i use. This program (ACT!) allows me to import data from excel, however, each contact must be in a single row for my sales software to import it. e.g row one must be as follows:
1a= name 1b= age 1c= tel 1d= e-mail
When i am copying and pasting each contact into excel the data goes downwards in the colum, which is no good to me i.e
1a= name
2a= age
3a= tel
4a= e-mail
Is there a way to make colums be rows and rows be colums or is there an easier way to solve this problem. I am very new to excel so please be gentle.
I can e-mail you a sample of the source data if it will help.
Thanks!

 

[DaleWatson123321]

There is a "transpose" option, but depending on your actual file and situation, there might be "better" options.

If you want to email the file, I'd be willing to spend some time in coming up with a good solution.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Storyteller]

Hello contactcp,
Where is the information comming from that you are entering into Excel?

I use ACT! extensively and I am very familiar with setting up Excel as a data clean up tool to move data from one application into ACT!

To answer you current question:
1. Select the data in Excel
2. <Copy>
3. Select a blank cell (J1-for example)
4. <Edit> select <Paster Special>
5. select the <Transpose> option then <OK>
I have found this the easiest way to &quot;Transpose&quot; data

As Dale indicated there are a number of ways of accomplishing this. Another way is to use the =Transpose() function.

Hope this helps,
Michael
Microsoft Office 2000 Master Instructor
Corel Certified Instructor WordPerfect 9

 

[contactcp]

Storyteller

The info is coming from a database on the net. I have tried some of the address grabbers (ACT! add-ons) but they only seemed to work if the address was American and they cost over $100.

I am going to try your method, however, the list i need to make before i import is of a few thousand contacts. Although i haven't tried your method yet, it seems like a long winded thing to do to every contact. But i will give it a try and let you know how i get on.
Thanks!

 

[bkpchs237]

contactcp,

If I understand your request properly you have a list of contacts with addresses in columnar format, i.e. the name, address & contact information for each contact in one column and approximately 4 rows, then you have another one following below it, then another and another and so on. The transpose function will not work for you in this situation.

I suggest that you make a copy of your worksheet and perform this on the copy to preserve your original data as a precaution.

There are a number of ways to accomplish this, but I use (and like) algebraic functions to accomplish this task relatively simply. I'll try to explain it further. In a column on the far right of your data place the numbers 1,2,3,4, etc. for each line of the address. The first line of each contact should begin again with the number 1. If your items are uniformly distributed this will be very easy as you can copy the pattern very quickly. Also the later formulas are much simpler. If you like this procedure, I suggest you go through your entire data set and uniformly distribute all of your contacts first before proceeding.

Then use the following formulas (assuming that your data is in column A with 4 lines of information for each contact):
The numbers 1 thru 4 are repeated throughout column B. (ie.B1=1, B2=2, B3=3, B4=4, in B5 place the formula =B1 then copy this formula down the entire B column. Highlight the numbers in column B and go to Edit, Copy, Edit, Paste Special Values to eliminate the formulas.
in cell C1 place the following formula: =IF(B1=1,A2,&quot;&quot
in cell D1 place the following formula: =IF(B1=1,A3,&quot;&quot
in cell E1 place the following formula: =IF(B1=1,A4,&quot;&quot

Then highlight the cells C1 thru E1 and use the fill handle to copy the formulas down these three columns entirely. This will place your addresses across the first row of the contact information only. Now with the items still highlighted go to Edit, Copy, Edit, Paste special values to get rid of the formulas.

Highlight the entire data set and sort it on column B in ascending order. Delete all the rows that contain the numbers 2 thru 4 in column B. Then delete column B. Your resulting items will be your addresses effectively &quot;transposed&quot; as you desire.

Hope this helps.

 

[wwgmr]

Contactcp, Storyteller is correct if your data as it is imported into excel holds the titles like First Name, Last Name all in column A and the data is in order across in rows from that Named field. When you copy the whole table and then select paste special and select Transpose. The data will then be in proper table format. IE row 1 will have column headings and the rows will contain data.