+ Reply to Thread
Results 1 to 9 of 9

Advanced Transposing

  1. #1
    Registered User
    Join Date
    02-03-2023
    Location
    Brisbane
    MS-Off Ver
    2022
    Posts
    3

    Post Advanced Transposing

    G'day all,

    I have PDFs that contain questions for practice exams. I have attached one for reference.

    Each exam contains questions and when I copy them into excel it essentially looks like this.


    Row 1: Question
    Row 2: A) Answer
    Row 3: B) Answer
    Row 4: C) Answer
    Row 5: D) Answer
    Row 6: Question
    Row 7: A) Answer
    Row 8: B) Answer
    Row 9: C) Answer
    Row 10: D) Answer

    And it goes on for 40-odd questions per exam.

    At the end of the exam are answers which essentially have what unit the question is related to right next to it.

    We are trying to integrate them to a platform that will allow us to do it in bulk if we can somehow get the data into the following format.

    question type marks answer1 answer2 answer3 correctanswer threshold subject trainingBlock externalReference performanceCriteria questionExplanation.

    Please see attached spreadsheet.

    Does anyone know how I can transpose this data? We have about 40 questions per exam, 10 exams per subject and 7 subjects. Making a grand total of 2800 questions. The idea of manually doing this seems impossible.

    Regards,
    Joey
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Advanced Transposing

    Please amend your sample file to show the expeted answer. it's not clear from your description what you want to see.

    Are you confusing columns (up and down) and rows (across)??
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Advanced Transposing

    Is there an Ecel 2022??

    Hi. Before we get going on this one...

    Is your forum profile showing the Excel PRODUCT that you need this request to work with?

    The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

    The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

    Cheers,

    Glenn.
    Attached Images Attached Images

  4. #4
    Registered User
    Join Date
    02-03-2023
    Location
    Brisbane
    MS-Off Ver
    2022
    Posts
    3

    Re: Advanced Transposing

    Hey Glenn,

    Sorry mate, I am running Microsoft 365 version 2301.

    I have created a loom to provide a little more clarity, please see it here: www{dot}loom{dot}com/share/704a81689f6046e28176813be1e3fbd5

    Please also see attached pdf sample if that helps answer any questions:

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Advanced Transposing

    Instead of uploading pdfs and external links, can you upload ONE Excel file showing your input data and a few rows of your desired output?

  6. #6
    Registered User
    Join Date
    02-03-2023
    Location
    Brisbane
    MS-Off Ver
    2022
    Posts
    3

    Re: Advanced Transposing

    Hey mate, please see the attached CSV and note rows 5, 6, 7, 8, and 9 for question data and row 214 for answer data.
    This is for a single question only and I am essentially trying to transpose this whole spreadsheet.
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Advanced Transposing

    Unfortunately, you did not provide the expected answers requested.

    This will let you see that moving from a pdf to Excel is NOT simple. If the data were nice and regular, this >>>MIGHT<<< have produced your desired result. The first 5 rows are >>nearly<< right. the multipli choice letters are in the wrong cell, 'cos the pdf hasn't translated well into Excel.

    =WRAPROWS(A5:A2045,5)

    However, a poor correlation between pdf and Excel means that the simple approach won't work. From Q6 onwards, it all breaks down completely.

    If you can get a good way of going from pdf to Excel raw data... getting your (assumed...) output is easy.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Transposing

    Can you please add an example of the required output in Sheet1. This will [probably] require a VBA solution.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Transposing

    See the attached conversion of PDF to Excel. It still requires some "cleaning up" but is much better than the CSV file.

    So all we now require is a sample of your output which should be relatively straight-forward to achieve using the attached file as a base.

    NOTE: All exams do not have 40 questions.
    Last edited by JohnTopley; 02-03-2023 at 03:32 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Advanced form of transposing data from column to row
    By skylesh1999 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-24-2023, 06:58 PM
  2. Advanced transposing of data
    By Exc3lnoob in forum Excel General
    Replies: 10
    Last Post: 04-16-2018, 03:39 PM
  3. Advanced Transposing
    By EdwardSnowden in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-27-2018, 04:42 PM
  4. Advanced data transposing
    By heidilaw4 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-20-2013, 03:06 AM
  5. [SOLVED] Advanced Variable Transposing
    By ian.clark in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2013, 03:16 AM
  6. Help needed for advanced transposing
    By ncikusa in forum Excel General
    Replies: 3
    Last Post: 03-09-2011, 03:47 AM
  7. Advanced Transposing?
    By SHexceluser in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-12-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1