Fill Multiple Cells With Different Header Data

r_john

New Member
Joined
Aug 7, 2017
Messages
3
Have an extracted text file that was imported into Excel. This is the exact format subsequent to import:

Code:
[TABLE="width: 795"]
<tbody>[TR]
[TD]RVICE TY[/TD]
[TD]PE : TP[/TD]
[TD]SU[/TD]
[TD]SP[/TD]
[TD]ENSE TYPE : I[/TD]
[TD]AA[/TD]
[TD]REASON : Invalid Service[/TD]
[TD]Traffic Stream[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T139914[/TD]
[TD="align: right"]1/12/2016[/TD]
[TD="align: right"]9:53[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="align: right"]20:00[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T139914[/TD]
[TD="align: right"]1/12/2016[/TD]
[TD="align: right"]10:14[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="align: right"]0:14[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RVICE TY[/TD]
[TD]PE : TP[/TD]
[TD]SU[/TD]
[TD]SP[/TD]
[TD]ENSE TYPE : I[/TD]
[TD]AA[/TD]
[TD]REASON : Invalid calling[/TD]
[TD]Traffic Stream[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T140061[/TD]
[TD="align: right"]1/20/2016[/TD]
[TD="align: right"]11:20[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="align: right"]1:57[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T140061[/TD]
[TD="align: right"]1/20/2016[/TD]
[TD="align: right"]11:22[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="align: right"]16:23[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T140061[/TD]
[TD="align: right"]1/20/2016[/TD]
[TD="align: right"]11:51[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="align: right"]13:33[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T140062[/TD]
[TD="align: right"]1/20/2016[/TD]
[TD="align: right"]19:17[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="align: right"]0:20[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RVICE TY[/TD]
[TD]PE : TP[/TD]
[TD]SU[/TD]
[TD]SP[/TD]
[TD]ENSE TYPE : I[/TD]
[TD]AA[/TD]
[TD]REASON : Invalid Exchange[/TD]
[TD]Traffic Stream[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T140310[/TD]
[TD="align: right"]2/2/2016[/TD]
[TD="align: right"]13:39[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="align: right"]9:13[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
There is always a blank line before and after the rows beginning with "RVICE TY".

What I would like is to have adjacent cells in each block of rows (rows between each blank line considered a block) to be filled with the REASON - which is displayed just before each block.

Therefore would like to have the following - based on above example -:

Code:
 [TABLE]
<tbody>[TR]
[TD]RVICE TY[/TD]
[TD="width: 69"]PE : TP[/TD]
[TD="width: 40"]SU[/TD]
[TD="width: 21"]SP[/TD]
[TD="width: 84"]ENSE TYPE : I[/TD]
[TD="width: 79"]AA[/TD]
[TD="width: 166"]REASON : Invalid Service[/TD]
[TD="width: 90"]Traffic Stream[/TD]
[TD="width: 122"][/TD]
[TD="width: 162"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T139914[/TD]
[TD="class: xl65, align: right"]1/12/2016[/TD]
[TD="class: xl66, align: right"]9:53[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="class: xl66, align: right"]20:00[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD]REASON: Invalid Service[/TD]
[/TR]
[TR]
[TD]T139914[/TD]
[TD="class: xl65, align: right"]1/12/2016[/TD]
[TD="class: xl66, align: right"]10:14[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="class: xl66, align: right"]0:14[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD]REASON: Invalid Service[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RVICE TY[/TD]
[TD]PE : TP[/TD]
[TD]SU[/TD]
[TD]SP[/TD]
[TD]ENSE TYPE : I[/TD]
[TD]AA[/TD]
[TD]REASON : Invalid calling[/TD]
[TD]Traffic Stream[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T140061[/TD]
[TD="class: xl65, align: right"]1/20/2016[/TD]
[TD="class: xl66, align: right"]11:20[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="class: xl66, align: right"]1:57[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD]REASON: Invalid calling[/TD]
[/TR]
[TR]
[TD]T140061[/TD]
[TD="class: xl65, align: right"]1/20/2016[/TD]
[TD="class: xl66, align: right"]11:22[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="class: xl66, align: right"]16:23[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD]REASON: Invalid calling[/TD]
[/TR]
[TR]
[TD]T140061[/TD]
[TD="class: xl65, align: right"]1/20/2016[/TD]
[TD="class: xl66, align: right"]11:51[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="class: xl66, align: right"]13:33[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD]REASON: Invalid calling[/TD]
[/TR]
[TR]
[TD]T140062[/TD]
[TD="class: xl65, align: right"]1/20/2016[/TD]
[TD="class: xl66, align: right"]19:17[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="class: xl66, align: right"]0:20[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD]REASON: Invalid calling[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RVICE TY[/TD]
[TD]PE : TP[/TD]
[TD]SU[/TD]
[TD]SP[/TD]
[TD]ENSE TYPE : I[/TD]
[TD]AA[/TD]
[TD]REASON : Invalid Exchange[/TD]
[TD]Traffic Stream[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T140310[/TD]
[TD="class: xl65, align: right"]2/2/2016[/TD]
[TD="class: xl66, align: right"]13:39[/TD]
[TD]I[/TD]
[TD="align: right"]5578457844[/TD]
[TD="align: right"]7878515342[/TD]
[TD]ST LUCIA[/TD]
[TD="class: xl66, align: right"]9:13[/TD]
[TD]TP DD C8      I=    O=[/TD]
[TD]REASON: Invalid Exchange[/TD]
[/TR]
</tbody>[/TABLE]
Note: Each block varies in terms of number of rows.

Please assist, many many thanks.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG03Jan42
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("G:G").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Dn
        [COLOR="Navy"]If[/COLOR] Left(R.Value, 6) = "REASON" [COLOR="Navy"]Then[/COLOR]
            Txt = Dn.Value
        [COLOR="Navy"]Else[/COLOR]
            Dn.Offset(, 3).Value = Txt
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Forum statistics

Threads
1,089,200
Messages
5,406,794
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top