On Sun, 9 Jan 2011 00:52:56 -0800 (PST), Snowy wrote:

I have data in excel like below.
I want to concatenate 1 cell after every blank cell to there related
set of values.

For e.g:

0023.00 Brucellosis
0023.00 Brucella melitensis
0023.10 Brucella abortus
0023.20 Brucella suis
0023.30 Brucella canis
0023.80 Other brucellosis
0023.80 Infection by more than one organism
0023.90 Brucellosis, unspecified

Should Look like

0023.00 Brucellosis
0023.00 Brucellosis - Brucella melitensis
0023.10 Brucellosis - Brucella abortus
0023.20 Brucellosis - Brucella suis
0023.30 Brucellosis - Brucella canis
0023.80 Brucellosis - Other brucellosis
0023.80 Brucellosis - Infection by more than one organism
0023.90 Brucellosis - Brucellosis, unspecified

Eg 2.

0024.00 Glanders
0024.00 Infection by:
0024.00 Actinobacillus mallei
0024.00 Malleomyces mallei
0024.00 Pseudomonas mallei
0024.00 Farcy
0024.00 Malleus

It should look like

0024.00 Glanders
0024.00 Glanders - Infection by:
0024.00 Glanders - Actinobacillus mallei
0024.00 Glanders - Malleomyces mallei
0024.00 Glanders - Pseudomonas mallei
0024.00 Glanders - Farcy
0024.00 Glanders - Malleus


I have so many sets separating with the blank row.

Please help me on this.

Thanks
Snowy

I note that your sets, although separated by blank rows, are also characterized by the integer portion of the ID being the same. Ordinarily, I would "key" off that. But looking at your later clarification, it seems you really want to key off the blank entry.

Accordingly, assuming that B1 is blank, and your first entry is in B2,

C2: =IF(B2="","",IF(OFFSET(B2,-1,0)="",B2,LOOKUP(2,1/($B$1:B2=""),$B$2:B3)&" - " & B2))

and fill down as far as needed. The references will self adjust.

If you have to change the cell references, note that for the LOOKUP function, the lookup_vector and result_vector are offset by 1.