Unique rows to columns issue

[lesliefenwick]

Hi everyone,

I have a dataset that has two fields. One field is my column heads and the other is the values. I am attempting to crosstab them so that I have a functioning dataset. The field names repeat and I am not quite sure how to convert this.

The dataset is the parsed html/xml from a kml file to add some context.

Final fields should be: ZIP_CODE | STATE | POP2016 | POP16_SQMI | SQMI | PO_NAME

Any help is appreciated and please let me know if you have any questions in case I need to clarify.

Current:

Fields	Values
ZIP_CODE	1
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	16279.47
PO_NAME	N Dillingham Census Area
ZIP_CODE	2
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	95704.75
PO_NAME	Yukon Flats Nat Wildlife
ZIP_CODE	3
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	14491.7
PO_NAME	Alaska Peninsula NWR
ZIP_CODE	4
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	6568.13
PO_NAME	W Kenai Peninsula Boroug
ZIP_CODE	5
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	3713.14
PO_NAME	N Lake and Peninsula Bor
ZIP_CODE	6
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	16293.2
PO_NAME	Matanuska-Sustina Bor
ZIP_CODE	7
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	65388.41
PO_NAME	Southerly North Slope Bo
ZIP_CODE	8
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	832.76
PO_NAME	Aleutians West
ZIP_CODE	9
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	2769.67
PO_NAME	Lake Clark National Pres
ZIP_CODE	10
STATE	AK
POP2016	-99
POP16_SQMI	-99
SQMI	5215.68
PO_NAME	Yukon Delta Wilderness

[ADerbak]

I was able to use the crosstab, split to columns, and join multiple tools to get it to come out the way I think you wanted it.

Start of WorkflowEnd of Workflow

I feel like there is a more efficient way than what I have created, but this should get the job done. I have attached my workflow so you can try it yourself.

Let me know if that helps!

-AD

[LordNeilLord · Accepted solution]

Hey @ADerbak

If you're looking for a simpler method, you can use the multirow formula to add a recordID to each "group" of records and then crosstab back:

[ADerbak]

I knew there was a simpler way! Thank you @LordNeilLord!

[lesliefenwick]

Thanks for this! #GODLIKE @LordNeilLord @ADerbak