Parsing Key Value pairs from inconsistent and incomplete data

Hey all,

I have a CSV file with the following data in rows:

{'gender': 'male', 'age': '35', 'hometown': 'Chicago', 'car': 'Civic', 'occupation': 'actor', 'salary': '80000'}
{'gender': 'male', 'age': '32', 'hometown': 'New York', 'occupation': 'banker', 'salary': '130000'}
{'gender': 'female', 'age': '33', 'hometown': 'Seattle', 'occupation': 'developer'}
{'age': '27', 'hometown': 'Detroit', 'car': 'Mustang', 'salary': '75000'}

As you can see, there are recurring attributes (i.e. Gender, Age, Hometown, Car, Occupation, Salary), but this data is complete for every record, making it difficult / impossible to simply separate via Column-To-Test techniques.

Is there a way to use Excel to parse this data into columns and rows?

I'd like to use the attributes as column headers, so that when a "value" is missing, it would simply be a blank cell:

table.jpg


Thanks in advance!

excel_lente!

Do you only have Excel 2010?

It's what we currently use at my company; is there some functionality in more recent Excel versions that could help with this task?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

As you arenew here I will add the link myself https://www.mrexcel.com/forum/excel-...lete-data.html

Yes, the header is "extracted information", and contains rows of structured yet inconsistent data.

To moderators: My apologies for the cross-posted link, I will be sure to avoid this in future.

If you have read the link provided (which you really should do) you will see that cross posting is not forbidden, but links to cross posts MUST be added.

Hi

Suppose your header in A1:F1 and data in P2:P_n

Use in H2:H_n the value 1 and for I2:I_n the formula

Formula:

Please Login or Register  to view this content.

Use this formula in A2 and copy them to F_n

Formula:

Please Login or Register  to view this content.

See the file for clarification

That's amazing, thanks so much, Jose!

You are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks.