Trying to compile list of data into another list without blanks

[KelblerElf12]

Hello,
I have looked through some posts about this topic, but i can't seem to get anything to work. I have an O.O.S. list which lists a set of trucks and trailers that have been put out of service. I probably should start by stating that this is a google sheets document. I am not for sure how much that makes a difference. The process starts by a mechanic filling out a form, the raw data then populates under the O.O.S. Form tab. I created a new tab to help make the data aesthetically pleasing called O.O.S. Record and it is under the Current O.O.S. heading. The mechanic can put the unit back into service using a different form, which will take the unit off of the current out of service record.

I would like to essentially create a dashboard / another tab that actively lists the units currently out of service. Basically I would like the current out of service list to not have blanks. Actually, if that could be done on o.o.s. record tab and also on a separate tab that would be great. FYI units will be subtracted and added to the lists. Any help would be greatly appreciated.

Thank you

[alansidman]

This can be accomplished quickly with Power Query, but only if you are using excel 2010 or later. It will not work with Google Sheets. If this is something that will work for you, post back and I will work it up. With PQ, every time you update the "Form" and click on Refresh it will update the output. Please advise.

[KelblerElf12]

I am not familiar with PQ, but I looked at some videos online about it and i don't think it will work for our everyday use of google sheets. It is nice with google sheets because those who have access can access it and see live updates as it is happening. Does PQ do that?

[alansidman]

Once you have developed your query in PQ, any updates to the original data is processed and immediately updated by clicking on the Refresh button. You usually have to select the refresh twice. Once to get the updated info into the query and once to reload the data back to excel. Very slick and immediate.

[KelblerElf12]

Sounds like I could look into that, but is there a way to get blanks out of the data in google sheets? You can see the different equations I tried in row 5.

[alansidman]

I don't work with Google Sheets and am not familiar with their protocols. Is there a filter feature where you can unselect blanks?

[alansidman]

As an aside if you are interested and I don't know how this will work or be adapted to Google Sheets, here is a Macro that will list all the Out of Service to the OOS Record tab. Hopefully, you can adapt this as needed for the Google Sheets.

Please Login or Register  to view this content.

[KelblerElf12]

I haven't found a filter to do that. I just know that Google Sheets is almost a basic form of Excel, but there are a lot of things you can do in Excel that you can't do in Sheets. As far as I know, any formula would work, but one to take out blanks is somehow not working. I appreciate the help though.

[alansidman]

Look at this link which I googled "Filter Blank Cells in Google Sheets"

https://www.benlcollins.com/spreadsh...lter-function/

[KelblerElf12]

I found query on google sheets that asks for Google Visualization API Query Language? I tried the filter equation and it still not working. I don't know if it has something to do with the fact that I have code in each cell of the O.O.S. columns?

[JeteMc]

Not familiar with google sheets however here is a formula based solution that may work.
1. Modify the formulas in columns A:B so that instead of the Value if False being a space it is null i.e.: =IF(COUNTIF('O.O.S. Form'!B:B,E5)>(COUNTIF('Return to Service'!B:B,E5)),E5,"")
2. The array entered formula* that populates columns K:L with Trucks and Trailers is: =IFERROR(INDEX(A$4:A$54, MATCH(0, IF(ISBLANK(A$4:A$54), 1, COUNTIF(K$4:K4,A$4:A$54)), 0)),"")
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
I noticed that after employing step 1, the values in columns P:R also arranged without the blank rows.
Let us know if you have any questions.

[KelblerElf12]

Thank you for the help!!! The first formula works, but the second formula had an "Iterative calculation" error. Their is a setting that controls this in google sheets under "file" then "spreadsheet settings" then "calculation" tab. It is turned off in settings, then when i turn it on it gives me 2 values to adjust. The first is "Max number of iterations" which it defaults to 50. The second is "Threshold" and it defaults to .05. When I saved that, the results were just blank instead of errors. I tried one of the other formula's I found when searching online and this one seems to work for the time being:
=ArrayFormula(IFERROR(INDEX($A:$A,SMALL(INDEX(($A$4:$A$54="")*10^10+ROW($A$4:$A$54),0),ROW(A1))),""))

Thank you both for your help!!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.