Aligning vertically a series of tables with text

Question

Hi I need the text to be in a specific format in a spreadsheet to be able to upload it on a translation tool.

I have already used the text split function to separate the text in a cell with bullet points, moving each bullet point to a separate cell.

enter image description here

Then I used the transpose function to separate each set of data. For context, you are looking at fashion products. The name of the product is on the first row, followed by a list of features (e.g. "Bracciale" means bracelet and it is followed by the list of materials)

enter image description here

Now for the last step, I need these sets to be vertical, not horizontal. Like this:

enter image description here

I would like to set up an automatic system so that every time we receive a list with hundreds of these products we do not need to copy-paste them one below the other.

With pivot tables maybe? Keep in mind that if it is too complex it might be hard to train the translators to do it each time. Please let me know your suggestions. Thank you!

I am not a programmer. I tried pivot tables but the data was in the wrong order and I am not sure how to get the data out from the pivot table with values only without the sub-menus.

Answer 1

Based of this trick, maybe the following is helpfull:

Formula in A5:

=DROP(REDUCE(0,A1:A3,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,,HSTACK(CHAR(10),"^"),1)))),1)

• TEXTSPLIT() will use a combination of newline chars and the circumflex to split the input directly into a vertical array;
• Iteration in REDUCE() will allow for stacked results;
• DROP() the initial value from results.

Answer 2

My suggestion would be to use the 'Unpivot Columns' feature in the Power Query Editor - it would be really simple.

Steps:

1. Select the whole range
2. Go to Data // Get & Transform Data // From Table/Range
3. Uncheck 'My Table has headers' (unless it does - but doesn't look like it?)
4. Press OK. This will open Power Query Editor and will have actually given you column names Col1/2/3 etc, but ignore that.
5. Go to Add Column // Index column
6. Select all columns EXCEPT the new index column by Shift+clicking on those headers
7. Go to Transform // Unpivot Columns
8. Assuming the order is important, click in the Attribute column and Sort Ascending
9. Click in the Index column and Sort Ascending
10. Remove the Attribute and Index columns if you want (right click header)
11. Go to File // Close & Load

You will get a new table - dynamically linked to the first (ie. can be updated/refreshed) - in the unpivoted format.

Let me know if you need more details / screenshot?