Saturday, August 31, 2013

Google Spreadsheet ImportRange In Reverse Order

Question:

( by Ernst103 )


Hi All!

For days I have searched for a formula that can help me with a import range that update in reverse order, so to speak, no success.

I have data sheet 'SMSDB" with 5000 plus lines, and every day it updates with sms's (there is only 100 or so numbers in the entries). I import and filter the the data in a separate sheet. All works well, From the first date the value is imported to the 1st column, tomorrow the 2nd value appears in the 2nd column and so on,, sweet.

But now after 150 days there is 150 columns and you have to scroll all the way to the end to see the latest value, so IS it possible to have the range update the 1st column and move the whole range on one column?

Or what may also do the job is if the import range only import the 10 latest values from the SMSDB. The main idea is to see the latest 10 values, the farmer just needs to know if the latest value is up or down.  

The SMSDB:


The Import sheet:


Thank you all you geniuses out there!!!!
Ernst 

Solution:


Have a look at the following screenshot:


I have the following formula in Cell C4:
=Transpose( query( ImportRange("0AghC7uJYvbXYdEd1OFUtUmVMdUlXSmNRYnNFNXBCSXc","Sheet 1!A:F") ; "select Col6 where Col4='+27782437428' order by Col1 desc limit 10" ; 0 ) )

I have the following formula in Cell C5:
=Transpose( query( ImportRange("0AghC7uJYvbXYdEd1OFUtUmVMdUlXSmNRYnNFNXBCSXc","Sheet 1!A:F") ; "select Col6 where Col4='+27730467475' order by Col1 desc limit 10" ; 0 ) )

I have the following formula in Cell C6:
=Transpose( query( ImportRange("0AghC7uJYvbXYdEd1OFUtUmVMdUlXSmNRYnNFNXBCSXc","Sheet 1!A:F") ; "select Col6 where Col4='+27810565555' order by Col1 desc limit 10" ; 0 ) )

I have the following formula in Cell C7:
=Transpose( query( ImportRange("0AghC7uJYvbXYdEd1OFUtUmVMdUlXSmNRYnNFNXBCSXc","Sheet 1!A:F") ; "select Col6 where Col4='+27832477651' order by Col1 desc limit 10" ; 0 ) )

Similarly you can get for the remaining rows...

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,

2 comments:

  1. HALLELUJAH!!!! Thank you so much for posting this. The ability to perform queries within the spreadsheet to return sorted, limited datasets... do you realize what this means? No more scrolling through thousands of rows... users can see the latest-submitted data at the top of the report and SO much more. (Forgive my excitement..)

    I've always liked excel, but Google Sheets is really turning into something special. Thank you for sharing and thanks to Google for being so innovative

    ReplyDelete
  2. Could you use this formula without the "select Col6 where Col4='+27810565555' portion? I have a similar sheet but I just need to import A4:D inverted and adding new rows with each entry.

    ReplyDelete