Kioskea
Recherche
Ask a question Report

Excel question, if cell contains certain word [Solved]

Christin - Latest answer on Sep 23, 2010 09:49PM
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!!!
Read more 
Answer
+0
moins plus
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;.
Add comment
Answer
+0
moins plus
that doesn't work...it just copies the data to the second sheet exactly the way it is on the first sheet.
Add comment
Answer
+0
moins plus
I want...
Every name Company Name Address Phone Fax email

in each column...
Add comment
Answer
+0
moins plus
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.
Add comment
This document entitled « excel question, if cell contains certain word » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.