excel question

[rahulsharma012]

Akay Agencies

1576, Bhagirath Place,

Chandni Chowk,

Delhi-110 006.

Phones : 22964596, 22964783.


Albert David Ltd.

 4/11, Asaf Ali Road,

New Delhi-110 002.

Phones : 23270220, 23277667.


I have 1000 entries in excel 2007 like the one above as shown in a single comumn A having company name address and phone number.The next company information in the same column is after every single row space like this i have 100o cpmany information data with 1 cell space as a blank row.


all i want is that all information like comp name address and contact number etc should get transposed in columns for all companies with there respective information.


kindly suggest with formula

 

[rahulsharma012]

this is the link to my excel file


i want the data to be get transposed with formula


kindly help


http://www.fileconvoy.com/dfl.php?id=g78fd2eefe1c08e5f9992724659771636b8f95ca7f

 

[bobhc]

Good day rahulsharma012


Your title reads "excel question" as this is an Excel forum I suppose you are right in that but it tells the members nothing about your problem as all questions on this forum are "excel question"(s). Members have diffrent levels of Excel intrest and a Title which goes some way to describe the problem will get more intrest.

 

[rahulsharma012]

sorry bobhc


will make it sure for future questions.

 

[rahulsharma012]

kindly help with the above problem and post the excel file for the same link on fileconvoy site

 

[rahulsharma012]

waiting for anyone reply sorry but i need the answer some what early

 

[Hui]

Rahulsharma012


Please note Bobhc's comments about your Urgency not being our problem

If you want to pay somebody to help you you can put a time limit on that job and then badger them, but not here please


Now


Insert a new Column A

In A2:

=IF(B2="","-",IF(B1="","Company",IF(A1="Company","Add1",IF(A1="Add1","Add2", IF(AND(A1="Add2",B3<>""),"Add3",IF(LEFT(B2,3)="Pho","Phone", IF(LEFT(B2,3)="Fax","Fax",IF(LEFT(B2,3)="Web","Web",IF(LEFT(B2,3)="Mob","Mobile", IF(OR(LEFT(B2,3)="E-m",LEFT(B2,3)="Ema"),"EMail","Add4"))))))))))


Copy down to end of data

Copy/paste as values the new Column A overitself


In D1:M1 put the labels

[code]Company	Add1	Add2	Add3	Add4	Phone	Mobile	Fax	Email	Web


D2: =IF(A2=$D$1,B2,"")

E2: =IF($D2<>"",IF(MATCH(E$1,INDIRECT("A"&(1+MATCH($D2,$B$2:$B$1081,0))&":A1081&#34,0)>MATCH("-",INDIRECT("A"&(1+MATCH($D2,$B$2:$B$1081,0))&":A1081&#34,0),"",OFFSET($B$1,MATCH($D2,$B$2:$B$1081,0)+MATCH(E$1,INDIRECT("A"&(1+MATCH($D2,$B$2:$B$1081,0))&":A1081&#34,0)-1,0)))[/code]


Copy E2 Across to M2


Copy D2:M2 down to the end of your data


Copy D2:M1081

Paste as values over itself


Sort D2:M1081


You will see that a lot of the data in the B's etc doesn't have gaps between each Address

You'll have to fix that manually between before doing the above


Also some fields have multiple Phone Numbers or combined Fields like email/web etc

You'll have to fix that manually


Enjoy

 

[Hui]

Yep

This was so urgent, that after 5 days you can't be bothered responding?

 

[bobhc]

Good day Hui


I have noticed a trend on the forum that those that need help "urgently" or post with a non-descript title are among those that are least likley to say thank you.

 

[Hui]

Bobhc


I Agree


Luckily formulas like =IF($D2<>"",IF(MATCH(E$1,INDIRECT("A"&(1+MATCH($D2,$B$2:$B$1081,0))&":A1081"),0)>MATCH("-",INDIRECT("A"&(1+MATCH($D2,$B$2:$B$1081,0))&":A1081"),0),"",OFFSET($B$1,MATCH($D2,$B$2:$B$1081,0)+MATCH(E$1,INDIRECT("A"&(1+MATCH($D2,$B$2:$B$1081,0))&":A1081"),0)-1,0)))

just roll off the tongue (I say in jest)


I don't even care if people don't say Thank You, it would just be nice to know that the solution offered was right or not?