Duplicating Google Sheets QUERY function in Excel

bljohnson13

New Member
Joined
Jan 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
1578424084920.png


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:

1578424162477.png

Developed/scheduled event sheet:
1578424278190.png


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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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

New Member
Joined
Jan 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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).
 

Forum statistics

Threads
1,136,201
Messages
5,674,384
Members
419,504
Latest member
tismail

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top