Convert col to row in new tab, grouped by col heading

[sschinker]

My vb skills are beginner. I can follow, but don't know enough to write my own code.

I have a lot of recipes with ingredient data by the amount, measure type, and ingredient name, plus other cooking related data, in columns. The ingredient data is separated by grouping of three headings: item1amt, item1qty, item1 to a maximum of item28amt, item28qty, item28. This created a lot of columns. This style worked for a spreadsheet, but now I'm creating a database and this won't work.

I found examples of transposing from col to row. But need something more complex.

Example of the current format, each item is in a separate column, in the same row.
Brownies ¾ cup butter 4 ounces chocolate 2 cups sugar 3 each eggs 1 teaspoon vanilla 1 cup flour 1 cup almonds


I'd like the code to loop, due to some recipes with a few ingredients, and others with a lot. There is other information, that remains as is. The changes begin with column 'R', and end with column CU. I have a total of 1721 rows.

My goal is to to move the ingredient related data from columns to rows. Separating data into three new columns, while creating as many new rows as ingredients. Creating a new table of data on a new tab.

Move the data from current columns to new columns:
Old heading name item(*)amt to new heading name amount
Old heading item(*)qty to new heading name measure
Old heading item(*) to new heading name ingredient

With the 'A' column repeating the recipe name for each of the new rows, for each separate recipe. If possible I'd like to run this on the entire spreadsheet.

Recipe Name Amount Measure Ingredient
Brownies ¾ cup butter
Brownies 4 ounces chocolate
Brownies 2 cups sugar
Brownies 3 each eggs
Brownies 1 teaspoon vanilla
Brownies 1 cup flour
Brownies 1 cup almonds

Thanks in advance.

[JOHN H. DAVIS]

Welcome to the Forum. Can you attach a sample showing how your layout is at the beginning and another sheet showing how you would like it to appear after running a macro.

[sschinker]

John,
Thanks for the quick reply. Please review the attached file for a sample layout.

[JOHN H. DAVIS]

I'm going offline in a few. But I would be glad too help out when I log in again. Unfortunately that won't happen for a few days. However, I still would advise that you also attach a sample which has a result sheet as well.

[sschinker]

I'm so new that I do not know what you mean by 'result sheet'. Please let me know what information is needed and I'll comply. Again, thanks for the quick responses.

[JOHN H. DAVIS]

A result sheet is one showing how you want your data to look like after the macro is run.

[sschinker]

The 'Smpl Layout' file I'd previously attached had examples of two recipes on one tab. I modified the data to reflect the change on a 'New' tab. The 'Current' tab shows an example of the how the data is now. Please see the attached file 'Smpl Layout02' for results sheet.