Data re-arrangement

Stephen,

First off - thank you for introducing me to the OFFSET function. It is very useful to know. It wasn't working for all of my values, but then after I read the syntax (above) a bit more, I figured out why: it is assuming that all of my "records" are equally spaced throughout the spreadsheet (3 lines apart).

Unfortunately, this is not true.

Basically, what I am looking for is a function that will essentially say:
"If I find this value ("Name"), then copy the data located on cell to the right of it"

Does such a command exist? I have been trying my best to search through this forum (and am still searching), but haven't found anything just yet...

OK - I'm looking at this data more and more, and it is much more complicated that my overly simplified 'example' posted earlier. It also definitely exceeds my knowledge of Excel (and what will likely involve some VB).

Basically, here is the "raw" data:
http://www.ntia.doc.gov/broadbandgra...ns/results.htm

This is obviously a database of some sort, but due to the output format, it is not very easy to work with at all. I am attempting (poorly, I might add) to filter this into individual columns ("Applicant", "Location", etc...).

Any assistance would be greatly appreciated. The spreadsheet that I am currently working with is attached...

That's quite different, and why it's better to post your data as is. My initial approach doesn't work as you've found out.

Try this. For some reason the grant/loan didn't appear to pick up the values - see if it works for you. Appears to be an odd formatting issue.

Sub x()
 
Dim i As Long, rFind As Range, sFind As String, rApp As Range, n As Long, r

Application.ScreenUpdating = False

sFind = "Applicant"

With Sheet1.Range("A1", Sheet1.Range("A" & Rows.Count).End(xlUp))
    Set rFind = .Cells(.Rows.Count, 1)
    For i = 1 To WorksheetFunction.CountIf(.Cells, sFind)
        Set rFind = .Find(What:=sFind, After:=rFind, LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            Set rApp = rFind.CurrentRegion
            n = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1
            With Sheet2.Cells(n, 1)
                .Value = rFind.Offset(, 1)
                .Offset(, 1).Value = rFind.Offset(1, 1)
                .Offset(, 2).Value = rFind.Offset(2, 1)
                .Offset(, 3).Value = rFind.Offset(3, 1)
                .Offset(, 4).Value = rFind.Offset(4, 1)
                .Offset(, 5).Value = rFind.Offset(5, 1)
                .Offset(, 6).Value = rFind.Offset(6, 1)
                .Offset(, 7).Value = rFind.Offset(7, 1)
                .Offset(, 8).Value = rFind.Offset(8, 1)
                r = Application.Match("Grant request*", rApp.Columns(1), 0)
                If IsNumeric(r) Then .Offset(, 9).Value = rApp(r, 2)
                r = Application.Match("Loan request", rApp.Columns(1), 0)
                If IsNumeric(r) Then .Offset(, 10).Value = rApp(r, 2)
                r = Application.Match("Status", rApp.Columns(1), 0)
                If IsNumeric(r) Then .Offset(, 11).Value = rApp(r, 2)
                r = Application.Match("Description", rApp.Columns(1), 0)
                If IsNumeric(r) Then .Offset(, 12).Value = rApp(r, 2)
                r = Application.Match("Executive Summary", rApp.Columns(1), 0)
                If IsNumeric(r) Then .Offset(, 13).Value = rApp(r, 2)
            End With
        End If
    Next i
End With
        
Application.ScreenUpdating = True
        
End Sub