Mixed short form/long form file - how to transform?

Question

Hi everyone,

I've searched and searched but can't quite find an example that fits my data problem.

I have a simple file which seems to be a mix of long form and short form data. I'm trying to transform the short form data into long form, but I'm hitting a wall.

RecordTypePropertyIDQuickRefIDPropertyNumberInstanceIDTypeDescriptionClassActYrBuiltEffYrBuilt550090R1000005910-04-022-0700-907505667AGAttached GarageRA219761976550090R1000005910-04-022-0700-907505667MAMain AreaRA219761976550090R1000005910-04-022-0700-907505667MA2Main Area 2nd StoryRA219761976550091R1000015910-04-022-0800-907505732AGAttached GarageRA119761976550091R1000015910-04-022-0800-907505732MAMain AreaRA119761976550091R1000015910-04-022-0800-907505732RPSwimming PoolsARP519761976

Ultimately, I'm trying to get one row for each property. The "type" and "description" fields need to be transposed so that each description/type is its own column. So if a property has a garage, that column would contain the word, "attached garage." If it doesn't, then the cell would be empty (or read "none" or something like that).

So, "property id" would be the unique identifier in this case.

I'm almost certain this is a job for the transpose and crosstab functions, but I just can't seem to get my head around it! Any ideas?

Sample Data.csv

Claje · Accepted Answer

Hi,

I wasn't sure how your long form format should look exactly, so I used the example listed below and came up with a column header format.  You can easily change the header format yourself as needed.

Take a look at the attached workflow - you were correct that transpose and cross tab is the easiest way to go about something like this.