Data in single column -aligned in different columns

[amit_gupta123]

Hiii All

I have some bills and their status in column A as follows
Bill no1
exception: delayed
Bill No 2
exception: delayed payment
Caution : No lending
Bill No 3
Caution : Discount

I am looking that the exceptions or cautions related to each bill reflects adjacent to it like as under

Bill no 1exception: delayed
Bill No 2exception: delayed paymentCaution : No lending
Bill No 3Caution : Discount
Regards

 

[shrivallabha]

What kind of solution you want, formula based or vba script?

 

[amit_gupta123]

Sir, would prefer formula?

 

[shrivallabha]

OK. I am assuming following based on your sample.

• Presence of words "Bill no" to find out the first column data.
• There will be at least two rows which will be either blank or non-blank and will associate with entry found by above method.

In cell D2 enter following formula ARRAY entered [CTRL+SHIFT+ENTER].
=INDEX($A$1:$A$100,SMALL(IF(ISNUMBER(SEARCH("bill no",$A$1:$A$100)),ROW($A$1:$A$100)),ROWS($D$2:D2)))
If entered correctly Excel will surround it with braces {}. Copy down this formula until you get an error.

In Cell E2 enter following formula.
=IF(INDEX($A$1:$A$100,MATCH($D2,$A$1:$A$100,0)+COLUMNS($E$2:E2))<>"",INDEX($A$1:$A$100,MATCH($D2,$A$1:$A$100,0)+COLUMNS($E$2:E2)),"")
Copy this formula down and across.

Make sure you adjust bold parts to match your maximum data filled row.

The second formula can be cut down if the formatting could be changed. Please test these on your actual data and post back.

 

[amit_gupta123]

Sir, I have checked the same on original data and it's working superb...Thanks a ton

 

[shrivallabha]

Very good. Thanks for the feedback.