Sunday, September 30, 2012

Import data from one sheet to another

Question:

 ( by Frank Bernard )

 I am building a expense report. I also just downloaded an app that I can keep track of my expenses.  This app exports csv files and I would love to be able to copy and paste this data in to on of the sheets in my google document. With that I would love for the spreadsheet to sort everything out. I have different expenses that I have on another sheet that I separate in to different tables according to the expense.  I was wondering if there is a way to filter that data to the according table.  To be more specific with my answer I have two sheets. CSV file come with the headers Category, Source, Expense, Date, and Amount.  The other sheet I want to filter the data has only the headers Source, Expense, Date, and Amount. No Category. The table already contains that and there is no need for it. I have spent countless hours trying to figure this out with no luck. If somebody could let me know if this can be done and how I would be very grateful. If it can't then I know to stop wasting my time.  Thank you in advance.


 Solution:

 Here is the screenshot of Sheet1:


Now in Sheet2 type the following formula in Cell A1:
=ArrayFormula('Sheet1'!B:E)

 It will populate your Sheet2 with Columns B to Column E.
Here is the screenshot of Sheet2:





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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,


2 comments:

Saturday, September 29, 2012

Google Spreadsheet Script to get the Last Row

Google Spreadsheet Script to get the last row


function GetTheLastRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");

  //To get the last row of a Sheet
  var lastrow = s.getLastRow();

  //To display the last row in the msgbox
  Browser.msgBox(lastrow);

}


 And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,

No comments:

Shorten Google Spreadsheet or Form Links

Question:

 How can I create a simpler (shorter) link to the Forms (or Spreadsheets) that I create in Google Docs
Solution:
Google URL Shortener

 Have a look at the following link:
http://goo.gl/

 For example here is the link of my blog:
http://igoogledrive.blogspot.in/

 I have shortened it to:
http://goo.gl/b5t1U


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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,
No comments:

Filter from one sheet to another

Question:

 ( by Brian Podolak )

 Basically I have a workbook with 2 sheets

SHEET1 has data in it like this

A                                                   B
9/7/2012 13:14:00                          Brian
9/10/2012 10:19:00                        Bob

On Worksheet SHEET2, 
I want to total the occurrences of Brian and Bob based on a date I have in C7
So this way I can see

Brian  1
Bob     1
Solution:

 Put the following formula in Cell B2 in Sheet2:
=counta(filter('Sheet1'!B:B;'Sheet1'!B:B=A1;Day('Sheet1'!A:A)=Day($C$7);Month('Sheet1'!A:A)=month($C$7);Year('Sheet1'!A:A)=Year($C$7)))

 And then drag this down as far as you need...



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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,
1 comment:

Friday, September 28, 2012

Google Spreadsheet Time Zone Settings

Google Spreadsheet Time Zone Settings


 1) Open a spreadsheet, click on "File" menu, then "Spreadsheet Settings...", have a look at the screenshot below:




2) and change your timezone and locale settings, have a look at the screenshot below:


Then click "Save settings". 

 The changes will propagate to all the collaborators that can edit the spreadsheet.

 The timezone affects timestamps, revision history dates, and time-related functions like NOW() or TODAY(), while the local settings change the currency or the way numbers are displayed.


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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,

No comments:

Synchronizing Sheets and Master Sheet option2

Question:

 ( by Tim Welch )

 I have a problem i hope someone can help me solve. I have a master spreadsheet (gradebook) with multiple worksheets that are named with the teacher name. On the master (dashboard) is student data which is automatically added to the teacher spreadsheet with a query function. Now on the teacher worksheet, as the teachers add data, i need that data to flow back to the Master sheet. i have it working with a filter function, however, i have to change the filter for each row to reflect the correct sheet name. I am looking for a way to have a single filter formula in that column that will work to copy down through all the rows. 

Sheet name = t501, t502, t502,...etc.
Filter function (which works and does what i want) is =filter(t501!N$8:BD, (t501!A$8:A)=A9) which correctly pulls all the data from the teacher sheet based on the student ID (in this case=A9).

the problem is to have a formula that can build the sheet name as a variable so it can be copied down a long row of data.

I have partially solved the problem by sorting the students by this teacher code so they are all together, then one can copy down the formula for all the students that have that teacher, but it will be much cleaner to write a formula that will make the filter formula work automatically. I have tried the sheet name as a variable using indirect, and other ideas with no avail.

thanks in advance,

Tim Welch

Solution:

This is the second option for the solution...

Please check also check out the solution by option 1:

Have a look at the screenshot below:

(NOTE: I have changed the sheet name as per the values in Column D)



Put the following formula in Cell N8 and then drag it down:
=filter(indirect(D8 &"!N$8:BD");indirect(D8 & "!A$8:A")=A8)

NOTE: Cell D8 must be same as the sheet name and Cell A8 must contain Student ID, and after inserting it, then drag it down then you will note the formula in Cell N9 will change to:
=filter(indirect(D9 &"!N$8:BD");indirect(D9 & "!A$8:A")=A9)



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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,


2 comments:

Custom filter function solution

Question:

 ( by Heman )
I'm sure the answer is out there 100 times, but I can't find it...
I have a worksheet with part numbers and locations in a warehouse. Say "Part #'s are in column A and "Locations" are in column B.
I want to be able to enter into cell C1 a part# that corresponds to a number somewhere in column A, and then in C2, automatically display the cell contents of the one next to that cell in A.
To further explain, If I enter 350786-Q into C1 as my part #... it finds that match in cell A42. Now I want it to display the contents of B42 in cell C2. How do I go about this?
Thanks in advance.
Me.
Solution:

 Put the following formula in Cell C2:

 =FILTER(B:B;A:A=C1)

 The above formula will return you the value from Column B where C1 is equal to in Column A.
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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,


No comments:

Set a uniform date format to a column

Set a uniform date format to a column:

 1) Select the column which you want to set the format of date.
2) Then go to "Format" menu.
3) Then go to "Number"
4) Then go to "More formats"
5) And then select whatever format you want...

 Have a look at the following screenshot:




Now after applying the date format, you can see the below screenshot:




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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,
No comments:

Thursday, September 27, 2012

Synchronizing Sheets and Master Sheet

Question:

 (by Shetal)

 Ok... so the reason I'm writing to you is because I need some Spreadsheet expertise.
I have a spreadsheet that I need help with.
So in this spreadsheet the first tab is the grade master which have my students names, info, etc.  The rest of the tabs are the different 5th grade classes.  I was able to filter the students from the Master into the individual classes by the class code.
THIS IS WHERE I AM STUCK...
Each teacher will eventually enter in data in their class sheet and what I need to happen is that the data they enteredautomatically filters into the grade Master sheet so I can see all of this data at an entire grade.  Any ideas?
I have protected the master sheet as well as parts of the individual class sheets because of the formulas I have in there. With 200 students in the master it would be annoying for the teacher to find each of their students to enter in their data that's why I have used a query formula to filter out the students as well as some demographic data that I want in each individual class sheet. Below is a sample 5th grade spreadsheet. Hope you can figure this out. I'm not very good with scripts and have been stuck on this for over a week.




Solution:

 Please check also check out the solution by option 2:
http://igoogledrive.blogspot.in/2012/09/Synchronizing-Sheets-and-Master-Sheet-option2.html
First of all, you need to insert the following script in your script editor:

function VMerge() {
  var maxw=l=0;
  var minw=Number.MAX_VALUE;
  var al=arguments.length ;
  for( i=0 ; i<al ; i++){
    if( arguments[i].constructor == Array )l =arguments[i][0].length ;
    else if (arguments[i].length!=0) l = 1 ;
    // literal values count as array with a width of one cell, empty cells are ignored!
    maxw=l>maxw?l:maxw;
    minw=l<minw?l:minw;
  }
  if( maxw==minw) { /* when largest width equals smallest width all are equal */
    var s = new Array();
    for( i=0 ; i<al ; i++){
      if( arguments[i].constructor == Array ) s = s.concat( arguments[i].slice() )
      else if (arguments[i].length!=0) s = s.concat( [[arguments[i]]] )
    }
  if ( s.length == 0 ) return null ; else return s        //s  
  }
  else return "#N/A: All data ranges must be of equal width!"    
}
And now, first of all, create a new sheet and insert try the following formula in it:
=query(VMerge('5-300'!A8:BD;'5-302'!A8:BD;'5-304'!A8:BD;'5-305'!A8:BD;'5-307'!A8:BD;'5-309'!A8:BD);"select * where Col1 is not null order by Col1")

Then you will get the Idea of what is happpening...

 Now, you need to select the Columns in query that you need that from "Column N" to "Column BD" that is from Col 14 to Col 56

 So here is the formula (You need to put it in Cell N8 in Master Sheet):

 =query(VMerge('5-300'!A8:BD;'5-302'!A8:BD;'5-304'!A8:BD;'5-305'!A8:BD;'5-307'!A8:BD;'5-309'!A8:BD);"select Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29, Col30, Col31, Col32, Col33, Col34, Col35, Col36, Col37, Col38, Col39, Col40, Col41, Col42, Col43, Col44, Col45, Col46, Col47, Col48, Col49, Col50, Col51, Col52, Col53, Col54, Col55, Col56 where Col1 is not null order by Col1")

Note: You need to change the following formula in all sheet in "Cell A8"
=query(Grade5Master!$A8:$N,"select A,B,C,D,E,F,G,H,I,J,K,L,M,N where D = 506")
to
=query(Grade5Master!$A8:$M,"select A,B,C,D,E,F,G,H,I,J,K,L,M where D = 506")

Following is the screenshot of Sheet "5-300":


NOTE:
The above solution will work only when you have Student Id all in sequence in Master Sheet, as I have sorted the query in Cell A8 of master sheet, so it will return in ascending order...

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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,

No comments:

Split and Manipulate text in a Cell and then Join it back

Question:

 Lets say I SPLIT("1 2 3", " ") which results in the list {"1", "2", "3"}, how do I apply a function to each member of the list that mutates the current value and returns a list containing the mutated values?

For example, I might want to convert the list to integers and apply multiply(X, 2) to my list and get the list {2, 4, 6}

In javascript this would look like:
"1 2 3".split(" ").map(function(x) { return parseInt(x) * 2; });

I cannot figure out how to do "map()" in spreadsheet syntax.


Solution:

 Let us say you have 
1,2,3,4,5
in Cell A1

 Then put the following formula in any Cell where you want the results...
=join(",";arrayformula(multiply(split(A1;",");2)))

The above formula will give you the result
2,4,6,8,10
in a single Cell (where you insert the formula).

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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,


No comments:

Script for Hiding or Unhiding Columns or Rows in a Sheet

Following is the script for Hiding or Unhiding Columns or Rows in a Sheet:


function HideColumn()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");

   var datarange = s.hideColumns(2);//this will hide Column B
}

 function unHideColumn()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");

   var columnRange = s.getRange("B:B");
  var datarange = s.unhideColumn(columnRange);
}


function HideRow()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");

  var datarange = s.hideRows(2);//this will hide Column B
}

function unHideRow()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");

  var rowRange = s.getRange("A2:2");
  var datarange = s.unhideRow(rowRange);
}



And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

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 or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

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,
Kishan,


2 comments:
Subscribe to: Posts (Atom)