Restructuring data from single row to multiple rows

I’ve got some data in the format attached where there is multiple columns of data, however I need it in a format where the columns are transposed into new rows, but retain some of the original row data.

Is there a way to have a formula on another worksheet that would automatically add new rows with the data in this format? The complication is that I need a new row for each fruit. Ideally I would also have a way to assign a unique userID based on the user column.

Any help would be greatly appreciated! Thanks in advance.

excel example.xlsx

Is there a maximum number of "Fruits"?

Hi John,
Yes, there's about 40 'fruits' in total in my real data.

Another VBA offering:

Click RUN on Sheet2. I suspect it will need adjusting to your "real" workbook

Please Login or Register  to view this content.

Thank you so much for your replies. I can see this working, and the data comes out exactly as I had asked, however I didn't anticipate it would require a script. I guess I was hoping it would be formula based so I could customise it to our data more easily. I'll perhaps add a more useful sample data so you can see exactly what we need to do.

Yes .... don't give up!. BUT ensure that the data is exactly as you will have it "real life" to avoid solutions being provided which do not meet you needs

Thanks again for all your suggestions. The data is coming from a form, and the form has become even more complicated. I'm not sure what we want is even possible without manually editing the data.

I've attached some sample data in the actual format it's coming out of the form. Essentially we need a new row for each wildlife observation, duplicating some column data, but with the SpeciesCategory and Species alongside quantities. I've put in an example structure in a new worksheet.

If you are up for a challenge I would be overwhelmed with appreciation and admiration if you can solve this.

example-form-data.xlsx

Much more complex than your original posting!

You have "Species" categories which have to be "determined" from the raw data so it is not a simple matter of transcribing the data.