Data sorting & Arranging

Dear Friends,

I have grabbed database from website, generally my grabbed data’s I save in notepad, and datas comes in below format

1)Company Name:
2)City/Province:
3)Country:
4)Address:
5)P.O.Box:
6)International Area Code:
7)Phone:
8)Fax:
9)Mobile:
10)Email:
11)Category Activities:
12)Area:

And also in same website I am getting datas with few details more OR less

1)Company Name:
2)City/Province:
3)Country:
4)Address:
5)P.O.Box:
6)International Area Code:
7)Phone:
8)Fax:
9)Mobile:
10)Email:
11)Category Activities:
12)Area:
13)Contact person:
14)No Of Employee:

I try to do this by transpose method but I am getting problem in this, because some company having 12 fields, some company 13 fields, and some company having 10, by doing transpose my datas are collapsed and misarranged

What I need is, I need all the datas in one format, if any company name or its detail available means that field will be filled in specific cell other wise need to be blank.. My final ourput format given in attachment

Kindly advise me

Hi jessi, this is probably along the lines of what you are looking for. You just need to change the ranges. Data is copied starting Q7 to Q something. Then vlookup searches the colum Q7 through Qsomething for the header in Q1 and if it finds a match it pulls from R. Otherwise it leave the cell blank.

Outpt format.xlsx

Dear Ford,

Thanks for your reply.

Kindly check my raw datas extracted, and i need to arrange this raw data

Samples:

1)

Company Name :
JOY STUDIO & SHOP L L C
Brief Description :
JOY STUDIO & SHOP L L C is a United Arab Emirates company, located in Abu Dhabi. Its contact method and company information is as below.
Business :
Photography

Contact Method

City :
Abu Dhabi
Country :
United Arab Emirates
PO Box :
25852
Telephone :
02-6343489
Fax :
02-6217795
Email :
[email protected]

2)

UNION COLOUR FILMS - L L C - Abu DhabiBRANCH
Brief Description :
UNION COLOUR FILMS - L L C - Abu DhabiBRANCH is a United Arab Emirates company, located in Abu Dhabi. Its contact method and company information is as below.
Business :
Photography

Contact Method

City :
Abu Dhabi
Country :
United Arab Emirates
PO Box :
2984
Telephone :
02-6771967
Fax :
02-6771447
Email :
[email protected]



3)

Company Name :
PHOTOGRAPHYA
Brief Description :
PHOTOGRAPHYA is a United Arab Emirates company, located in Abu Dhabi. Its contact method and company information is as below.
Business :
Photography

Contact Method

City :
Abu Dhabi
Country :
United Arab Emirates
PO Box :
42925
Telephone :
02-6335550
Fax :
02-6351516
Email :
[email protected]

4)

EMIRATES TECHNO CASTING LLC
Brief Description :
EMIRATES TECHNO CASTING LLC is a United Arab Emirates company, located in Ajman. Its contact method and company information is as below.
Business :
Cathodic Protection

Contact Method

City :
Ajman
Country :
United Arab Emirates
PO Box :
2895
Telephone :
06-7433669
Fax :
06-7437635
Email :
[email protected]
contact person :
Jaffar

Dear JDI,

Let i try by using Vlook & Hlook and i will let you know,thanks for your direction for this