Tuesday, August 6, 2013

Sorting Data Imported From Another Spreadsheet

Question:

( by Marcello Netto )


I have found the script below, which allowed me to automatically sort a column according to it's value. It worked like a charm, until I tried to sort a column on a sheet which brought imported data from another spreadsheat, i.e. the first column had values fed from another spreadsheet and the second column was sorted with the script, using values which were input manually.

When the second column is sorted, the first one (with imported data) becomes a mess; the values aren't rearranged according to the rearrangement of the second column. What is happening?

I also would like to know if there is a way of automatically sorting a column of imported values directly. I tried to use the same script on the imported values column without success.


Solution:


Have a look at the following link of Spreadsheet "Feeder":



Have a look at the following link of Spreadsheet "Feeded":


I have the following formula in Cell A1:
=ImportRange("0AmMTqpzD9YRndHlvY2Mtb2FZWjVTbkM4OFd1ZXlYWUE";"Sheet1!A:B")

the above formula auto populates the Columns A and B, then you have to manually input values in Column C.

Now you cannot sort the range A:C in the columns A:C itself, as data is being imported from another spreadsheet.

And if you want to sort this range "A:C" then you can use the following formula in Cell E1:
=sort(A:C;3;true)

And if you want to get this sorted data on another sheet then use the following formula:
=sort('Sheet1'!A:C;3;true)

replace "Sheet1" with the appropriate sheet name.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thanks Kishan. I didn't even need the script I had been using. It worked exactly as expected. Very nice tip.

    ReplyDelete
  3. Hi Kishan,
    Thank you for this tip. It is very useful. I have taken it a step further as I want to sort classes alphabetically from the whole school roll:
    =sort(filter('School Roll'!$A:$E,'School Roll'!A:A=A1))
    A:E are the columns containing the information I require on each student.
    A1 is the Class Code
    This means that I make a dynamic register sorted alphabetically according to the class name I type in the top right corner.

    ReplyDelete