how to align matching series with data with multiple columns in excel

[stavpal]

I have tables like this with string data assigned to every subject (column) (see attached xlsx)

Instead of Alpha, beta, Charlie rows I have various strings. Each string has a numeric/ or string data cell assigned to it on the right.

I don’t know the number of different strings regarding rows (26 is just an arbitrary test number). and they are different in every table. I have duplicates between columns but I also have strings in each column that are unique in each subject column

The number of subjects (columns) is around 20-40 in each table. Also, the length of the strings is different for every string (see attached picture).

I need to find a way to transform the table so that I can align the same rows (strings) for every subject column while adding blank rows while having the data assigned to it moved together.

Sorry if I didn't describe it well enough.

[JeteMc]

Please paste the following into cell K2:

Please Login or Register  to view this content.

Once the formula is activated drag the fill handle down to K27 and then, while K2:K27 are still selected, drag the fill handle over to cell R27.
Let us know if you have any questions.

[bebo021999]

In K2:

Please Login or Register  to view this content.

L2:

Please Login or Register  to view this content.

Copy K2:L2 to M2 then O2 then Q2
Copy K2:R2 down

[stavpal]

Thank you both! It needed some alterations and a macro to create the list but it worked!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.