Data re-arrangement

**SCT** · 10-16-2009, 02:53 PM

Greetings,

I am having a heck of a rearranging some data, and I was hoping that someone might be able to point me in the correct direction. I have a series of individual 'records' similar to this:

Name, John
Location, Las Vegas

Name, Jane
Location, Denver

Name, Joe
Location, Boston

I need to come up with a way to get this data into typical column-style format:
Name, Location
John, Las Vegas
Jane, Denver
Joe, Boston

Has anyone had to do something similar before? Any advice would be tremendously appreciated!!

**StephenR** · 10-16-2009, 03:11 PM

Are these values (e.g. Name, John) in one cell?

**SCT** · 10-16-2009, 03:15 PM

No (apologies - I should have specified). The comma was meant to indicate a seperate column.

Basically, column A contains a repeated list of "Name" and "Location" (followed by an empty cell). Column B contains their corresponding values.

**StephenR** · 10-16-2009, 03:25 PM

OK, if your names/locations start in B1/B2, B4/B5 etc and your headings are in D1/E1 then add these formulae and copy down:

D2: =OFFSET($B$1,3*(ROW()-2),0,1,1)

E2: =OFFSET($B$1,3*(ROW()-2)+1,0,1,1)

If your data are arranged differently, these will need some adjustment so I suggest you post a sample workbook if you need further help.

EDIT: see attachment.

**SCT** · 10-17-2009, 07:05 PM

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...

**SCT** · 10-17-2009, 08:03 PM

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...