google sheets
I'm making a list of all the items I own. I want to place all the items in a list on a single sheet. Each Item will have a "category code" attached to it. I would like additional sheets to:
What command do you use to search the column for a value, and then tell sheets to print the associated row?
I've added the result of your question in the Google Spreadsheet you shared with us.
In the SUMMARY sheet I collected all data that fits the criterion D<>0, with the following formula:
=QUERY(DATA!A:D;"SELECT A,C,D WHERE D<>0")
I also added an ID number, for the two companies and added the column to the summary sheet. As you can see, this formula can be easily extended.
With the following little piece of code you can accomplish that.
function myFind() {
var ss = SpreadsheetApp.getActive(), output = [];
var findData = ss.getSheetByName('fr').getDataRange().getValues();
var searchData = ss.getSheetByName('cql').getDataRange().getValues();
for(var i=1, iLen=findData.length; i<iLen; i++) {
for(var j=0, jLen=searchData.length; j<jLen; j++) {
for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
var find = findData[i][0];
if(find == searchData[j][k]) {
output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
}
}
}
}
return output;
}
Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.
find
search
result
I've created an example file for you: get cell row and column index
Best Answer
I have very little information to work on. I'm assuming you have the "category code" in a different column for the row entry. "Addition sheet" means you want it in different tab of same spreadsheets. You can use this code in your cell from where you want to start printing the rows
Suppose Sheet name is Sheet1, Column range is A2:Z, column with category code is B and category code you're looking for is "Medicines".