Team-BHP - Microsoft Excel : Tips, Issues, Etc.

Quote:

Originally Posted by MunnabhaiMBBS (Post 3952473)

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

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.

Quote:

Originally Posted by nfsfreak (Post 3952677)

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 (Post 3952724)

Thanks Malgudi,

Formula returned "0" in every row.

Quote:

Originally Posted by MunnabhaiMBBS (Post 3954640)

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 (Post 3954640)

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)

Quote:

Originally Posted by malgudi (Post 3954662)

Not Sure Why but this is what I tried
Attachment 1498688

Quote:

Originally Posted by nfsfreak (Post 3954709)

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.

Quote:

Originally Posted by MunnabhaiMBBS (Post 3955459)

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

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:

Microsoft Excel : Tips, Issues, Etc.-1.jpg

What I got from Pivot table:

Microsoft Excel : Tips, Issues, Etc.-2.png

What I really want:

Microsoft Excel : Tips, Issues, Etc.-3.jpg

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!

Quote:

Originally Posted by Spinnerr (Post 4549777)

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.

Quote:

Originally Posted by Spinnerr (Post 4549777)

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.

Quote:

Originally Posted by binand (Post 4549823)

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

Will keep that in mind.

Quote:

Originally Posted by binand (Post 4549850)

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

Magic indeed! clap: 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.