Hey there! love your videos, great and organized content, just one quick note, for the last example, it didn't remove duplicates when you tried to do it from the "Data" menu as you still had the "UNIQUE" value in that same table, so if you remove duplicates, the function will still refer back to the original range and re-populate it For it to work, you need to copy the range that you want to remove the duplicates from and CTRL+C then CTRL+SHIFT+V for it to be pasted as a text, then you can remove duplicates using the "Data" menu just fine Keep it up my friend, you're doing a great job!
You could also generate the header with ={ (A1:D1) ; (whatever function you are doing with A2:D13) } I like this way because it hides the formula in the header so less likely is gets deleted if you do not lock your cells
Hi MS Excel has the "Consolidate" function. Is there anything in google sheets that can do the same thing? I'd really love to have something in google sheets that can do that.
Hello, I really love your videos, you helped me a lot! I have a question, is there any way for me to use a 'Filter' function to automatically paste a data based on 'column background color' from other spreadsheet? Such as '=FILTER(ORDER!B2:B,ORDER!N2:N="#ff0000")' ? Thank you :)
Hello icemaa, You can use a script that will show you the color's "hex code" in a column based on the background colors in another column and then use a drop-down in a cell that is populated by the "hex codes" and from there use that cell reference in your FILTER formula like so... =iferror(filter(A2:F30,B2:B30=J1)) ...where my data is in the range A2:F30, the "hex codes" are in B2:B30 and my drop-down is in J1. Below is the script I use (not written by me).... -------------------------------------------------------------------------------------------------------------------------------------- function getBackgroundColour(cell) { return (SpreadsheetApp.getActiveSheet().getRange(cell).getBackground()); } -------------------------------------------------------------------------------------------------------------------------------------- ...my "background" colors are in column A and I then I have to use a "custom formula" in column B that grabs the color's hex code by using the scripts FUNCTION "getBackgroundColor" as the function for the formula... =getBackgroundColor(ADDRESS(ROW(), COLUMN()-1)) ...the ADDRESS function will show the cell reference the color is in by using the ROW() (current row that formula is sitting in) and COLUMN()-1 (current column that formula is sitting in minus 1). So if the formula is in B2 then the row is currently 2 and column is A (cause the formula is currently in column B and -1 makes it column A) so then we get a cell reference of $A$2. Then you will drag it down to get hex codes for all the colors in column A. To use the script from the sheet's menu go to "Extensions > Script editor" and paste over the default code and then "File > Save" in the editor's menu and give it a file name. Any questions please ask away! James/mreighties 😀
@@mreighties2860 Thank you so much James!😄 It's worked really well as what I really want! May I ask you one more question? My sheet has become more slower as it have to generate a lot of hex codes and at the same time filter data. What should I do to make sure my sheet works faster?
in your case, how to make an table of "Item" only with no duplicate. for example is showing only: Wrench, pliers, saw, screws. basically select 1 of duplicate
Hey there! love your videos, great and organized content, just one quick note, for the last example, it didn't remove duplicates when you tried to do it from the "Data" menu as you still had the "UNIQUE" value in that same table, so if you remove duplicates, the function will still refer back to the original range and re-populate it
For it to work, you need to copy the range that you want to remove the duplicates from and CTRL+C then CTRL+SHIFT+V for it to be pasted as a text, then you can remove duplicates using the "Data" menu just fine
Keep it up my friend, you're doing a great job!
Super helpful, thank you.
Best Content - perfect, understandable, clear, easy to follow! TOP thank you...
Very nice! Thank you.
Hi. How can I sort a newly queried table using UNIQUE(FILTER and sort that new table by a value in another column?
You could also generate the header with ={ (A1:D1) ; (whatever function you are doing with A2:D13) } I like this way because it hides the formula in the header so less likely is gets deleted if you do not lock your cells
Thanks for the tip!
Where was the link to the other channel where you had an in depth video on Query?
The channel is here... It's super good;) th-cam.com/channels/K9St2FSU-4r7xV1DeGa9eg.html
Nice relaxed style. Easy to follow
Ooh yeah
how to return cell address with XLOOKUP function of a last non-blank cell in google sheets?
Hi MS Excel has the "Consolidate" function. Is there anything in google sheets that can do the same thing? I'd really love to have something in google sheets that can do that.
I haven't used that feature before. You could probably do something similar with QUERY, but not the same way for sure.
I love your content friend, I always learned a lot!!!
Thank you. It's nice to have you around.
is Querry same as Filter function to Combine different sheets in one master sheet?
No. I think QUERY would be better for that
Hello, I really love your videos, you helped me a lot! I have a question, is there any way for me to use a 'Filter' function to automatically paste a data based on 'column background color' from other spreadsheet? Such as '=FILTER(ORDER!B2:B,ORDER!N2:N="#ff0000")' ? Thank you :)
Hello icemaa,
You can use a script that will show you the color's "hex code" in a column based on the background colors in another column and then use a drop-down in a cell that is populated by the "hex codes" and from there use that cell reference in your FILTER formula like so...
=iferror(filter(A2:F30,B2:B30=J1))
...where my data is in the range A2:F30, the "hex codes" are in B2:B30 and my drop-down is in J1.
Below is the script I use (not written by me)....
--------------------------------------------------------------------------------------------------------------------------------------
function getBackgroundColour(cell)
{
return (SpreadsheetApp.getActiveSheet().getRange(cell).getBackground());
}
--------------------------------------------------------------------------------------------------------------------------------------
...my "background" colors are in column A and I then I have to use a "custom formula" in column B that grabs the color's hex code by using the scripts FUNCTION "getBackgroundColor" as the function for the formula...
=getBackgroundColor(ADDRESS(ROW(), COLUMN()-1))
...the ADDRESS function will show the cell reference the color is in by using the ROW() (current row that formula is sitting in) and COLUMN()-1 (current column that formula is sitting in minus 1). So if the formula is in B2 then the row is currently 2 and column is A (cause the formula is currently in column B and -1 makes it column A) so then we get a cell reference of $A$2. Then you will drag it down to get hex codes for all the colors in column A.
To use the script from the sheet's menu go to "Extensions > Script editor" and paste over the default code and then "File > Save" in the editor's menu and give it a file name.
Any questions please ask away!
James/mreighties 😀
@@mreighties2860 Thank you so much James!😄 It's worked really well as what I really want! May I ask you one more question? My sheet has become more slower as it have to generate a lot of hex codes and at the same time filter data. What should I do to make sure my sheet works faster?
Create multiple tabs and rename tabs as value every cell from range of Google sheet, can you make video tutorial to handle of this problem?
This video on extracting sheet names may help: th-cam.com/video/W6DhIM53eIM/w-d-xo.html
Not so. From a list of multiple cells, create multiple tabs with the tabs name being the selected multi-cell value
I use QUERY all the time now - I wish I'd known about it years ago!
Game changer!
in your case, how to make an table of "Item" only with no duplicate.
for example is showing only: Wrench, pliers, saw, screws.
basically select 1 of duplicate
Look into using the UNIQUE function.
@@ProlificOaktree is there a way to get it inside the query? because unique is outside of query
@@Gaussen Not that I know of, but I'm not an expert with it.