+ Reply to Thread
Results 1 to 2 of 2

OFFSET and ADDRESS error

  1. #1
    Registered User
    Join Date
    06-22-2020
    Location
    Florida
    MS-Off Ver
    365
    Posts
    1

    OFFSET and ADDRESS error

    I have a table with two columns. In the first column are names of people. In the second column are scores on a quiz. There are four potential quizzes, and the database that creates this report exports data with a separate name for each quiz. For example, if John Smith completed quizzes 1, 2, and 3, the data would list John Smith three times in the first column with the corresponding quizzes listed, without quiz labels, in the next column over. If Susan Jones completed only quiz 1, she would be listed once. And so on for up to 100+ people. It looks like this:

    Column A Column B
    John Smith 97
    John Smith 75
    John Smith 50
    Susan Jones 100

    I need to be able to transpose this into a table that lists each person once down the table's first column and each quiz result separately in the same row but individual columns, like this (using underscores to try to correct the formatting in this post):

    Column J K____L__M_N
    John Smith 97 75 50
    Susan Jones 100

    I tried using OFFSET with ADDRESS to do so, but no luck. My formula was: =OFFSET(ADDRESS(MATCH(J2,A:A,0),1),1,1,1,1)

    J2 is the cell where John Smith's name would be in the transposed table. It returned an error (I don't know why), but I also realized that a problem with this approach is I need to account for the fact that John did only 3 quizzes. If I just use the above formula in K, L, M, and N with different offsets, the formula would pull a fourth quiz result for John Smith that he didn't do (it would grab the results for whoever was listed after him in the A:A list).

    Advice on how to do this? I don't yet know how to do VBA, and whatever I do, I want to make sure I understand how it works. Currently, I'm doing this manually by copying the cells and using Transpose in paste, but that's tedious and error-prone for large numbers of quizzes/students.

    Thank you.

  2. #2
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    725

    Re: OFFSET and ADDRESS error

    Since you're on O365, you should be able to use the new Dynamic Array Formulas.

    If your test results are in range A2:B5, to get the names, in any cell you can enter:

    =UNIQUE(A2:A5)

    Then you can enter this formula next to that one (and copy it down as far as needed):

    =TRANSPOSE(FILTER(B2:B5,A2:A5=J2))

    I think there's a better way to be able to enter in one formula without having to copy it down (it would expand automatically), but I haven't figured that out yet.
    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

+ 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] Run time error '1004': The address of this site is not valid. Check the address the addre
    By baig123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2014, 06:59 AM
  2. Offset(Address()) Formula "Has an error"
    By leafs4life22 in forum Excel General
    Replies: 3
    Last Post: 08-06-2014, 03:01 AM
  3. Display offset address
    By Jaeger in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2014, 07:50 AM
  4. Define an Offset Address as a Range
    By Brian48072 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2013, 08:49 AM
  5. Offset, Address & Match
    By veeru_php in forum Excel General
    Replies: 1
    Last Post: 06-28-2011, 03:13 AM
  6. [SOLVED] ???? =offset(ADDRESS(ROW(),COLUMN()),1,1,1,1)
    By Creator in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-17-2006, 10:20 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