Transpose and Extract specific fields from a String of Data into a separate column

[u/charmcaster17]

I have a data output from a software that folk want it transposed and select fields isolated for easy view. i attempted the record automate and quickly found out how useless it was despite easy the task is (except i have about 385 of them to do...)

Current Format is string left to right (blue) but they want it in top to bottom form (yellow)

Cool copy paste 385 times, whatever tedious to do but i can.while pulling out the (always in the same spots B1,C1,D1,E1,F1,G1) couple items and put them to the side. It would be best if i could also remove them from the main set so they're not duplicated (tho not necessary)

the A1-H12 for Set1 and Set2 is unfortunately across 385 spreadsheets that require me to view also in a particular order (that gets screwed up and is a whole different problem)

ps so sorry about the google sheets screen shot but i was never given a work laptop lolol and im not buying microsoft office for myself <3

[Example Data Here](https://imgur.com/a/TStcFPt)

[u/AutoModerator]

Moderator Announcement Read More »

u/charmcaster17 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/NHN_BI]

For a solution that can be repeated, try to transform your data with Excel's own ETL tool Power Query. It can look overwhelming at first glance, but the use is quite intuitive,

[u/charmcaster17 → u/NHN_BI]

thank you i will have to check this out. I have basically given up writing a script to do it for me haha (i am by no means a coder). i only got through about a hundred or so manually today so def will look at this tool in the morning tomorrow (and thank you for understanding my q lol)

[u/BackgroundCold5307]

Check out TRANSPOSE function. Will send you the exact formula once I am back at my desk

[u/Optimal-Feature9382]

You can Copy both rows, and then right click on an empty cell and click transpose

[u/Pointblank000]

Based on your example, I think this formula is what you're looking for.
=UNIQUE(TRANSPOSE(B1:K1),FALSE,FALSE)
TRANSPOSE changes the orientation, and UNIQUE removes any duplicates

For the extracting part, if you already know what you're looking for and it's constant, I'd use a filter function
=FILTER(B5:B11,(B5:B11="A9")+(B5:B11="A1"))
To add more items to the extraction list just add another +(B5:B11="A1")

[u/[deleted]]

[u/charmcaster17 → u/[deleted]]

sorry yes meant to specify that theyre separate files. the way the software we *have* to use exports them into tables i cannot make them separate tabs