How to put 1. serial no. for rows with gaps

Workbook Question3 has a sheet "DATA" which has entries in B Column.

Example desired output is given in sheet "Results"

I need to do 3 works.

1. To put serial number for each each name, in A Column.
2. I need to fill C Column with "1" for each name.
3. if S/O (son of) is found anywhere in B column, corresponding D column should be marked with 1. If if W/O (wife of) is found anywhere in B column, corresponding D column should be marked with 2. If

How to do this. Thanks in advance.

Try these:

A2 =IF(B2="","",COUNTA(B$2:B2))

C2 =IF(B2="","",1)

D2 =IF(B2="","",IF(COUNTIF(B2,"*S/O*"),1,2))

If it is possible for a cell in column B to not have S/O or W/O then you can use this in D2 instead:

=IF(B2="","",IF(COUNTIF(B2,"*S/O*"),1,IF(COUNTIF(B2,"*W/O*"),2,"No gender specified")))

Originally Posted by 63falcondude

Try these:

Perfect solution. Thanks for great help.