Duplicating Google Sheets QUERY function in Excel

[bljohnson13]

Hi Everyone,

To give you some background, I created a Microsoft Form to collect responses from individuals in my company that feeds into an Excel spreadsheet (this is a Forms for Excel form so I can access the spreadsheet and manipulate the data via excel online). The form branches into 2 sections of different sets of data. Instead of having 1 spreadsheet with all the responses (which will result in half blank rows depending on the responses in the Form), I'd like to separate and compile the responses in 2 separate sheets within the same Excel file.

This is my first time back to a company that uses Microsoft in a while so I'm not as familiar. I had been using Google and was able to find some help to do what I wanted in Google Sheets (and before you ask, the reason why I'd like to not use Google Sheets is that it would not be compatible with the other Microsoft-centric tools we're using. It could not be shared on the various tools that people use here so it would be a total mess):

First, the responses from a Google Form were compiled in a MASTER sheet:

Next, I was able to use a QUERY function to select just the rows that had responses in certain cells (based off the response to "Is this a..." question/column):
Idea for new event sheet:

Developed/scheduled event sheet:

As you can see, I was able to copy over only the rows that had responses to certain cells (essentially the cutoff was Columns "S" & "T") by asking to return cells that had data (i.e. "not null"). Also, there are no blank rows for either sheet as it would just add new rows to each of the separate spreadsheets. This makes it so much easier to read without having to scroll all the way to the right for responses to the "Idea for new event" sheets.

Basically, I wanted to know if something like this was possible in Excel. I have tried PowerQuery with the "remove blank rows" option, but that does not allow me to make changes to the queried data without it editing over it once the query runs again. You would think I could do something simpler with the IF function, but I can't figure out how that might work. I may very well be missing something simple but, again, it's been a while since I've used Excel.

Thank you in advance for your help!

 

[J.Ty.]

Select you whole data, hit Ctrl-T and let it be converted into a data table. You will get a filter for each column. Use it, uncheck the blank and you will get exactly the rows you need. You can copy and paste them elsewhere.
However, it will handle well dynamic data which changes over time, but it seems that you want to process the results after closing access to the form.

J.Ty.

 

[bljohnson13]

Thank you! Ideally, this form would be open over an extended period of time with new entries coming in over a period of months, so we would like to be able to see the results updated often and be able to manipulate responses as needed (say, need to change the date or time of an event). Would I be able to dynamically copy the table to another sheet (or another excel file) so that we can make changes as necessary but still see new responses as they're entered?

 

[bljohnson13]

Another question:

When trying to use the IF function, could I use the following formula to simply show the contents of a cell if it is not empty?

=IF(Form1!R2<>null,Form1!R2,"")

Is there a term or function that means "null" in Excel?

 

[J.Ty.]

Thank you! Ideally, this form would be open over an extended period of time with new entries coming in over a period of months, so we would like to be able to see the results updated often and be able to manipulate responses as needed (say, need to change the date or time of an event). Would I be able to dynamically copy the table to another sheet (or another excel file) so that we can make changes as necessary but still see new responses as they're entered?

Simply test one. If you add data, you must click on filter and then OK again.

J.Ty.

 

[J.Ty.]

Another question:

When trying to use the IF function, could I use the following formula to simply show the contents of a cell if it is not empty?

=IF(Form1!R2<>null,Form1!R2,"")

Is there a term or function that means "null" in Excel?

In this case, you need =IF(ISBLANK(Form1!R2),"",Form1!R2).

 

[bljohnson13]

Thank you, this is very helpful!