Ask a question Report Reply to this topic

Excel question, if cell contains certain word [Solved/Closed]

Christin - Sep 22, 2010 at 04:23 PM - Latest reply: Kym

- Dec 29, 2015 at 11:55 AM

Word Ask a question Report Reply to this topic

Hello,

I have a excel spreadsheet that has a bunch of companys with full addresses, phone numbers, etc. They are all in column A. I want the information to be horizontal on the spreadsheet. So all the company names in COlumn a, all the address in column b, phone in c...and so on. Is there a formula that I can run?

Please help!!!

5 replies

Report Respond to venkat1926

venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 23, 2010 at 01:28 AM

Helpful

suppose data is like this from A1 down to A9

a
ad1
1
s
ad2
2
d
ad3
3

now in sheet 1 in A1 type (or copy paste this formula)

=(IF(ROW(A1)<=COUNTA(Sheet1!$A:$A)/3,OFFSET(Sheet1!$A$1,(COLUMN(A1)-1)*3+ROW(A1)-1,0,1,1),0))

copy A1 both down and to the right till you get 0

delete 0s. do you get what you want?
In sheet1 now you are having 3 sets of data. you can have as many sets as you like but in each set the first is name, second address and third phone no;.

Report

Kym - Dec 29, 2015 at 11:55 AM

If it's just a one time thing - you copy the information, then paste special - hit the 'transpose' button

Report Respond to Christin

Christin - Sep 23, 2010 at 07:03 AM

Helpful

that doesn't work...it just copies the data to the second sheet exactly the way it is on the first sheet.

Report Respond to Christin

Christin - Sep 23, 2010 at 07:04 AM

Helpful

I want...
Every name Company Name Address Phone Fax email

in each column...

Report Respond to venkat1926

venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 23, 2010 at 09:49 PM

Helpful

there was a typo in my answer. the formula is to be copied in A1 of SHEET2 repeat sheet 2 .

I have parked this file )"christin.xls" in the following web page. download the file and see.

http://www.speedyshare.com/files/24385259/christin.xls

see sheet 1 for data and sheet 2 for results. see the formula in A1 of SHEET2 IN THE FORMULA BAR.

Ask a question