Looking up column values and apending to a row.

[Five5]

Hi,

I have a worksheet with a list of product specifications. For each product there is a specification type and value.

The issue is most products have multiple specification types/value, and the data is arranged with one spec/value per line.

What I would like to do is change the data structure so there is only one line per product, and each spec type and value is listed in seperate columns.

See the example attached.

I am not even sure what to call this type of transpose, so finding it hard to look up help for it.

Has anyone seen a utility, macro or know of a way to achieve this.

Thanks,
Five5

[Azam Ali]

see attatchment

[Five5]

see attatchment

Thanks Azam for going to the trouble to create an example solution. That worked perfectly. I was able to sort out 8 spreadsheets of around 1000 rows each, and avoided going insane as well.

[teylyn]

Five5, welcome to the forum.

Before you post anything else on this forum, please make sure to read and understand the following:

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[teylyn]

Thanks for taking the time to read and comply with the rule .

[Azam Ali]

Hi Five5

kindly explain your problem further. there is no ID 10 in your attatched book

[Five5]

Hi Five5

kindly explain your problem further. there is no ID 10 in your attached book

Sure. If you look at row 13, in the Outcome section, you will see product code 10 has suitable sucked in the Spec and Value data from the Source section. Values sucked in are: 3,33,5,55,6,66,8,88,9,99, etc. I have coloured them. But missing are: 1,11,2,22,4,44,7,77, etc.

Did that make sense?

[Azam Ali]

see attatchment
hope it help

It is recommended to always use evaluate formula option in the formula tab in order to understand the formula