I am stuck. I use the import range to get a set of data however now I need to the unique values that that matches the import range that I have.

Say for example below is randome sample. I have import range the information but now I need to find the grades for Book 1. Since there are multiple grades reading book 1, what formula do I use to find the grades(Is it unique(filter(importrange?) and possibly have them in one row and how do I find this for each book? Any help would be appreciated!

enter image description here

  • 1
    Welcome. Please edit your question and provide a sample sheet. Your image does NOT relate to your question. – marikamitsos Nov 16 '19 at 6:22
  • 1
    Provide specific: sample data, desired output, & formula of your currently failing output. – CodeCamper Nov 17 '19 at 23:38

You have grade reading data sourced from another spreadsheet using IMPORTRANGE. You want to use that data to analyse the books being read by grade.

There are many ways to achieve this outcome. This answer covers the basics of two methods.

Assume that we have data in a spreadsheet, and this data is imported into the current spreadsheet using IMPORTRANGE.


Option 1 - Create a "table" of data

Table of data

This approach uses two formulas in two adjacent columns:

  • =UNIQUE(D6:D): returns unique Grades
  • =JOIN(", ",unique(FILTER($A$6:$A,$D$6:$D=G20))): filters Column A (Book id) and Column D (Grade) and returns results where the grade equals the unique grade number in the adjacent cell

Option 2 - Pivot Table

Pivot table

In this example:

  • Rows: 'Grades', ascending
  • Columns: 'Book id' , ascending
  • value: Count of the Book id

    If the value isn't important, thern you could "hide" the count by formatting the text colour the same as the background colour.

Pivot table counts hidden

  • Thank you so much. I wan't sure if I was being clear in my question but this was basically it.!...I was able to figure this out using JOINTEXT but yes JOIN also works as well. – Lulu Dec 4 '19 at 20:21
  • We have a thing in NSW called "The Premier's Reading Challenge"; this is the sort of thing that would/could be produced in/for a school to show the variety and popularity of books, and the range of reading ability for children in each grade. – Tedinoz Dec 4 '19 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.