Go Back   Team-BHP > Around the Corner > Shifting gears > Gadgets, Computers & Software


Reply
 
Thread Tools Search this Thread
Old 15th April 2016, 11:14   #46
BHPian
 
Join Date: Dec 2010
Location: Bangalore
Posts: 301
Thanked: 98 Times
Default Re: Microsoft Excel : Tips, Issues, Etc.

Quote:
Originally Posted by MunnabhaiMBBS View Post
Hi guys, Would really appreciate your help.

Need a formula / function that reads the entire row and displays particular text / value in case the row contains a certain word.

For instance :

Row indicates : House is big : If row contains the word "House", then display "Good"

Thanks in advance.
If search is on particular cell, you can use below formula:
=IF(FIND(LOWER("house"),LOWER(A1),1)>0,"Good","Bad ")

This will return 'Good' if the word 'house' is found in cell A1. Else, it will return 'Bad'. I used 'Lower' to make it case insensitive.

EDIT:
To search for entire row, you can use the below formula.
=IF(MATCH("house",A:A,-1)>0,"Good","Bad")

Last edited by nfsfreak : 15th April 2016 at 11:22.
nfsfreak is offline   Reply With Quote
Old 15th April 2016, 11:55   #47
BHPian
 
Join Date: Apr 2006
Location: Mumbai
Posts: 321
Thanked: 158 Times
Default Re: Microsoft Excel : Tips, Issues, Etc.

Try this :
If the Row number 1 has House use formula ISNUMBER(SEARCH("House",1:1)) in say cell A2. It will return true if the row 1 has word House(does not mater in which cell of the Row 1) and False otherwise.
malgudi is offline   Reply With Quote
Old 18th April 2016, 16:15   #48
BHPian
 
MunnabhaiMBBS's Avatar
 
Join Date: Jul 2014
Location: Pune
Posts: 294
Thanked: 581 Times
Default Re: Microsoft Excel : Tips, Issues, Etc.

Quote:
Originally Posted by nfsfreak View Post
If search is on particular cell, you can use below formula:
=IF(FIND(LOWER("house"),LOWER(A1),1)>0,"Good","Bad ")

This will return 'Good' if the word 'house' is found in cell A1. Else, it will return 'Bad'. I used 'Lower' to make it case insensitive.

EDIT:
To search for entire row, you can use the below formula.
=IF(MATCH("house",A:A,-1)>0,"Good","Bad")
Thank you so much for the help.

Formula 1 : Worked partially, Showing "#VALUE" in place of Bad.

Formula 2 : Returned " #N/A" everywhere.

Quote:
Originally Posted by malgudi View Post
Try this :
If the Row number 1 has House use formula ISNUMBER(SEARCH("House",1:1)) in say cell A2. It will return true if the row 1 has word House(does not mater in which cell of the Row 1) and False otherwise.
Thanks Malgudi,

Formula returned "0" in every row.
MunnabhaiMBBS is offline   Reply With Quote
Old 18th April 2016, 16:34   #49
BHPian
 
Join Date: Apr 2006
Location: Mumbai
Posts: 321
Thanked: 158 Times
Default Re: Microsoft Excel : Tips, Issues, Etc.

Quote:

Thanks Malgudi,

Formula returned "0" in every row.
Not Sure Why but this is what I tried
Name:  temp.jpg
Views: 222
Size:  20.2 KB
malgudi is offline   Reply With Quote
Old 18th April 2016, 17:31   #50
BHPian
 
Join Date: Dec 2010
Location: Bangalore
Posts: 301
Thanked: 98 Times
Default Re: Microsoft Excel : Tips, Issues, Etc.

Quote:
Originally Posted by MunnabhaiMBBS View Post
Thank you so much for the help.
Formula 1 : Worked partially, Showing "#VALUE" in place of Bad.
Corrected formula: =IF(ISNUMBER(FIND(LOWER("house"),LOWER(A2),0)),"go od","Bad ")


Quote:
Originally Posted by MunnabhaiMBBS View Post
Formula 2 : Returned " #N/A" everywhere.
Corrected formula: =IF(ISNUMBER((MATCH("house",2:2,-1))),"Good","Bad")

2:2 denotes entire 2nd row. change accordingly for related row number.
To search on entire columns, you can replace that with A:A or B:B (related column name)
nfsfreak is offline   Reply With Quote
Old 19th April 2016, 18:28   #51
BHPian
 
MunnabhaiMBBS's Avatar
 
Join Date: Jul 2014
Location: Pune
Posts: 294
Thanked: 581 Times
Default Re: Microsoft Excel : Tips, Issues, Etc.

Quote:
Originally Posted by malgudi View Post
Not Sure Why but this is what I tried
Attachment 1498688
Quote:
Originally Posted by nfsfreak View Post
Corrected formula: =IF(ISNUMBER(FIND(LOWER("house"),LOWER(A2),0)),"go od","Bad ")



Corrected formula: =IF(ISNUMBER((MATCH("house",2:2,-1))),"Good","Bad")

2:2 denotes entire 2nd row. change accordingly for related row number.
To search on entire columns, you can replace that with A:A or B:B (related column name)
Thanks a lot both of you. Issue is now resolved.

Another puzzle:

- 3 different sheets with 100, 200 and 300 rows respectively. Can't predict the rows for subsequent sheets.
- Using Macro for Pivot table, current selected range is 300 (Rows)
- Need to select range for the available rows for future sheets, could be 500, 600, we don't know yet.
- Can we give specific range selection instruction so to accommodate all the rows in future.

Thanks in advance.
MunnabhaiMBBS is offline   Reply With Quote
Old 20th April 2016, 00:08   #52
BHPian
 
Join Date: Apr 2006
Location: Mumbai
Posts: 321
Thanked: 158 Times
Default

Quote:
Originally Posted by MunnabhaiMBBS View Post
Thanks a lot both of you. Issue is now resolved.



Another puzzle:



- 3 different sheets with 100, 200 and 300 rows respectively. Can't predict the rows for subsequent sheets.

- Using Macro for Pivot table, current selected range is 300 (Rows)

- Need to select range for the available rows for future sheets, could be 500, 600, we don't know yet.

- Can we give specific range selection instruction so to accommodate all the rows in future.



Thanks in advance.

Instead of selecting say a1:c300 as range for pivot table do select a:c . This will select all the data in a,b & c columns (irrespective of no of rows).
malgudi is offline   Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads
Thread Thread Starter Forum Replies Last Post
Microsoft Office; Tips & Shortcuts prateek99 Shifting gears 10 9th August 2011 15:24
Tata 407 vs Force Excel 4 LCV. Which one? itsmevivek Commercial Vehicles 12 3rd April 2007 09:43
Tax Calculator (excel) required protege Shifting gears 4 30th July 2006 22:40
Fiat Panda to be launched as Tata Excel Minardi The Indian Car Scene 28 5th June 2006 12:15


All times are GMT +5.5. The time now is 11:50.

Copyright 2000 - 2017, Team-BHP.com
Proudly powered by E2E Networks