Fill in the blanks?

[wisemank]

Hi there,

I have a large data set from an export of phone system. In the data set there is data i can pivot tabel to find my results but the format isnt conducive to pivot without some modifications. I need to find the "DNIS:" in column A and also the information in Column B and add the type to each line item below until the next DNIS is found. Please note there is data in every cell, but I simplified to demonstrate. So I need to add a column after the CALL ID and insert the DNIS type to each line. Any ideas? Macro or formula?

ps. there are 30k rows

Time	Agent	Call	ANI	Name	State	Queue	Duration	Group Name	Call ID
DNIS	ARC
DNIS	EMER

<tbody>
</tbody>

 

[jjasmith4]

Very unclear. Can you make a table with column headings A,B,C,... that has the first 100 or so rows of real data?

 

[DanteAmor]

Try whit this macro

Sub Macro1()
    With Range("A1:J" & Range("A" & Rows.Count).End(xlUp).Row)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
End Sub

 

[wisemank]

How can I upload an image of the screen shot? The table I tried to create obviously didn't work correctly.

 

[Fluff]

There are add-ins available here that will allow you to post data to the thread, that can then be copy/pasted into a sheet. https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Also put on another sheet the result you expect.