[nfsfreak]

Originally Posted by MunnabhaiMBBS

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")

[malgudi]

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.

[MunnabhaiMBBS]

Originally Posted by nfsfreak

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.

Originally Posted by malgudi

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.

[malgudi]

Thanks Malgudi,

Formula returned "0" in every row.

Not Sure Why but this is what I tried

[nfsfreak]

Originally Posted by MunnabhaiMBBS

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 ")

Originally Posted by MunnabhaiMBBS

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)

[MunnabhaiMBBS]

Originally Posted by malgudi

Not Sure Why but this is what I tried
Attachment 1498688

Originally Posted by nfsfreak

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.

[malgudi]

Originally Posted by MunnabhaiMBBS

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).

[Spinnerr]

Good morning!

Need a small help to display the data in the required format.

I have a master data sheet where 'Month' is a column. Rows are repeated for employees, when they have a record for the month. I would like to have this data displayed in a single row, kinda normalized view.

Hope the below pics explain it clearly.

How the data is:


What I got from Pivot table:


What I really want:


Note: The master table would not have a record if the employee is not active for any particular month, the same would have be shown as ZERO in the final data presented.

Any reusable solution is much appreciated. Thanks!

[binand]

Originally Posted by Spinnerr

I have a master data sheet where 'Month' is a column. Rows are repeated for employees, when they have a record for the month. I would like to have this data displayed in a single row, kinda normalized view.

Aren't you just asking for a Pivot Table?

Edit: Oh OK. I see now that you have tried Pivot Table already. Is this a one-off attempt or do you expect to see more rows each time a month passes? It might be a good idea to attach a sample spreadsheet which can then be used to publish suggestions.

Originally Posted by Spinnerr

Any reusable solution is much appreciated.

Gave it a try. Here's my attempt (attached).

Add raw data in the RAWDATA sheet and use drag fill in the OUTPUT sheet both ways (horizontally and vertically) to see the magic.

Adjusting to your own data is left as an exercise.

[Spinnerr]

Originally Posted by binand

It might be a good idea to attach a sample spreadsheet which can then be used to publish suggestions.

Will keep that in mind.

Originally Posted by binand

Gave it a try. Here's my attempt (attached).
... see the magic.

Magic indeed! Many thanks...

I will try to figure out the logic so that I can use it in future, but for now the data is ready in the right format.

Thanks again.