ExcelBanter - search function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - search function (https://www.excelbanter.com/excel-worksheet-functions/449510-search-function.html)

Hi!

I have data across the worksheet that I need to consolidate into one row.
For eg:

Column1 column2
sp a act a
sp b act b
act a sp c
act b sp d

I need a column to show the digit 1 for every cell that has "sp" in it. Basically i need to group the data which contains"sp" into one column.

Any ideas?

On Monday, November 18, 2013 12:34:11 PM UTC+5:30, Nitya Satheesh wrote:
Hi!

I have data across the worksheet that I need to consolidate into one column.

For eg:

Column1 column2

sp a act a

sp b act b

act a sp c

act b sp d

I need a column to show the digit 1 for every cell that has "sp" in it. Basically i need to group the data which contains"sp" into one column.

Any ideas?

sorry I need to consolidate data into one column not row.

Den torsdag den 21. november 2013 06.42.52 UTC+1 skrev Nitya Satheesh:
On Monday, November 18, 2013 12:34:11 PM UTC+5:30, Nitya Satheesh wrote:

Hi!

I have data across the worksheet that I need to consolidate into one column.

For eg:

Column1 column2

sp a act a

sp b act b

act a sp c

act b sp d

I need a column to show the digit 1 for every cell that has "sp" in it. Basically i need to group the data which contains"sp" into one column.

Any ideas?

sorry I need to consolidate data into one column not row.

Hi.
the formula could look like this:
=IF(AND(ISERROR(FIND(LOWER("SP"),A3)),ISERROR(FIND (LOWER("SP"),B3))),0,1)
https://dl.dropboxusercontent.com/u/28273423/Book1.xlsx

Steffen