Formatting data to columns

[gumaro]

Hello,

I have a set of data where multiple attributes are in the same column, and I would like to add more columns so I get all the information for each individual on the same row. Here is an example, I have the data on the left and I would like to convert it to the format on the right:

example.jpg

I could then easily remove duplicates without losing any information.

I hope I was able to explain myself, thank you all in advance.

[thomglea]

Assume ID is Column B, Data is Column C, Value is Column D, and ID in your results is column F. Assume your Table Headers are in Row 3. Put this in Column G under Weight (would be G4 using these assumptions). Copy across and down. ID's must be unique for this to work.

Please Login or Register  to view this content.

[gumaro]

Assume ID is Column B, Data is Column C, Value is Column D, and ID in your results is column F. Assume your Table Headers are in Row 3. Put this in Column G under Weight (would be G4 using these assumptions). Copy across and down. ID's must be unique for this to work.

Please Login or Register  to view this content.

Thank you so much, worked perfectly. Except I think in your example, for anyone who stumbles across your solution, in your formula, $F2 should actually be $F4.

Thank you!

[thomglea]

Sorry, you are right. Hard to write the code in my head with nothing to reference I'll update my answer.

[gumaro]

I also figured out this will only work with numeric values, unfortunately I also have some data points that are text values. Do you think it's possible with formulas?
?

[thomglea]

OK, with Text values, you opened up a new can of worms. I attached a workbook as it will be much easier for you to understand. The formulas in F2:H7 are array formulas and must be entered by pressing CTRL+SHIFT+ENTER

[gumaro]

Thanks it worked! I figured it would be something more complicated when text was involved. I was complaining about the way this data set is organized, but I just found out it's actually the proper way to organize a data set if you follow "proper" database theory. Thank you for your help, it's a very useful method.

[thomglea]

Thanks it worked! I figured it would be something more complicated when text was involved. I was complaining about the way this data set is organized, but I just found out it's actually the proper way to organize a data set if you follow "proper" database theory. Thank you for your help, it's a very useful method.

Glad to help!