Excel question, if cell contains certain word
Solved/Closed
Related:
- Excel question, if cell contains certain word
- Excel formula for cell containing a word ✓ - Forum - Word
- Excel find number of times a word appears in a cell ✓ - Forum - Office Software
- Excel formula if cell contains text then return value in another cell ✓ - Forum - Excel
- Duplicate rows in excel based on cell value ✓ - Forum - Excel
- Excel formula if cell contains date ✓ - Forum - Office Software
4 replies
venkat1926
Sep 23, 2010 at 01:28 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Sep 23, 2010 at 01:28 AM
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;.
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;.
that doesn't work...it just copies the data to the second sheet exactly the way it is on the first sheet.
venkat1926
Sep 23, 2010 at 09:49 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Sep 23, 2010 at 09:49 PM
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.
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.
Dec 29, 2015 at 11:55 AM