Address list transformation

Hi everybody,
I have little Excel experience, but this is beyond my skills and although I read plenty of posts and tried various ways I can't get anywhere with it.
Can you help me with this one ?

I have a approximate 6000 row contact file where all information is stored vertically and in two to three columns like this:

Company:	ABC Co Ltd
Address:	1 Scotts Road
Tel:	+81 1111 2222
Fax:	+81 5555 6666
Email:	[email protected]
Website:	www.mydomain.com
Company:	Another Co Ltd
Address:	Random Street 44
Tel:	+86 6666 7777
Email:	[email protected]

This makes the list pretty long and looking for contacts is rather difficult. I'd like to arrange them horizontally, but can't figure out how to do that.
As you can see in the example above the amount of rows for each contact is not consistent. For instance if the contact doesn't have a fax number and website registered the relevant rows are missing.

Basically this would be the result I'd like to have:

Company	Address	Tel	Fax	Email	Website
ABC Co Ltd	1 Scotts Road	+81 1111 2222	+81 5555 6666	[email protected]	www.mydomain.com
Another Co Ltd	Random Street 44	+86 6666 7777		[email protected]

Every help would be appreciated, as I tried it with mixture of FIND and OFFSET and some other strange ways, but I am not getting the logic right.

Thanks very much in advance !

Max

This is a very similar thread:

http://www.excelforum.com/excel-char...to-tables.html

although the layout is different to yours. Perhaps you can amend the formulae to suit, but if you have difficulty then attach a sample workbook and I'll show you how to do it.

Hope this helps.

Pete

Hi again,

thank you all so much for the great replies and welcome message.
I tried to amend the functions to match my exact layout of the table, but I just got presented with a new version of the clients list.
The data is nearly he same, but fields have been added and some of the triggers, such as the Prefix 'Company:' and 'Address:' are not available anymore.

I attached a short version of the current workbook with two sheets. 'Current' reflects the data as presented to me and 'Final' might be a wishful thinking, but anything getting me close to this is better than what I have now.

Maybe as an assistance:
1/ every company will have either a 'Pte' or 'Ltd' in its name
2/ the postal code of Singapore is always 6 digits
3/ all entries are located in Singapore
4/ every phone and fax number will have the same country prefix, i.e '+65'
5/ some rep names are written the chinese way where the first name comes last, but I would be able to manually rectify that
6/ 'Membership type' and 'Member since' and the rep title are irrelevant to us

Again, thank you very much for your amazing assistance and I should have continued playing with the functions, but after I saw the approached you had I have to admit, that this is way out of my league.

Thanks
Max

I have some work to do first, but I'll look at this during the day - it is a bit involved.

Pete

I managed to find some time to work on this. I inserted a new column A in the Current sheet and put the number 1 in cell A1 and this formula in A2:

=IF(B2="","-",IF(OR(ISNUMBER(SEARCH(" pte",B2)),ISNUMBER(SEARCH(" ltd",B2))),INT(MAX(A$1:A1))+1,IF(B2="Tel:",INT(MAX(A$1:A1))+0.2,IF(B2="Fax:",INT(MAX(A$1:A1))+0.3,IF(B2="Email:",INT(MAX(A$1:A1))+0.4,IF(B2="Website:",INT(MAX(A$1:A1))+0.5,IF(B2="Services:",INT(MAX(A$1:A1))+0.6,IF(B2="Industries:",INT(MAX(A$1:A1))+0.7,IF(B2="Representatives:",INT(MAX(A$1:A1))+0.8,IF(AND(B1="Representatives:",OR(B2="Mr.",B2="Ms.")),INT(MAX(A$1:A1))+0.9,MAX(A$1:A1)+0.01))))))))))

which was then copied down beyond the extent of your data (coloured yellow in the attached file) to help identify each record and field.

In the Final sheet the data can be extracted in the layout that you indicated - basically using INDEX/MATCH, but with slight variations in each field. I extracted the reps' names twice so that you can more easily make the manual adjustments for forenames and surnames - just fix the data in those columns first.

Hope this helps.

Pete

Hi Pete,

well I consider myself pretty lucky that you had 'some spare time' to look at this !
This would have taken me decades to figure out - amazing.

Thanks very much for your excellent help on this. It will save me tons of hours of copying/pasting.

Case closed.

Thanks again everybody for your help
Max