I had created a QUERY formula in Google Sheets and now trying to convert over to Excel. Since Excel doesn't have the equivalent "QUERY", I have attempted a couple of different workarounds, but cannot seem to get the desired results.
The formula queries a table and returns data based on the value in one or another column (desired value can be in one of two columns) and is as follows.
=QUERY(Sales!$A$3:$R$332,"SELECT A,B,C,D,H,I,J,K,L,M,N,O,P,Q,R WHERE F ='John Melvin' OR G = 'John Melvin'",1)
Attached Workbook
could you explain what you are trying to do with the query AND where it goes (so I don't have to click on every cell in the 6 tabs)?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Sorry, "Sales" will be the data entry and the data will be displayed in "John Melvin".
Many of us here do not work in Google Sheets and I in particular do not understand exactly what you are trying to do. Suggest you explain in plain/simple English exactly what you want to happen. An example to accompany your explanation would be very beneficial.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I'm pulling data from the table in "Sales" for the specific salesperson "John Melvin" and displaying that info in the sheet titled "John Melvin". The salesperson can be listed in either Salesperson #1 or Salesperson #2.
so you are not summing the data (and counting the dates) but rather you want to pull forward a line by line itemization of what John did if he was the sales person either 1 or 2, for each category, is that correct?
Yes, you are correct.
So if you put John Melvin in cell N1 of the John Melvin tab I assume everything is supposed to work off that cell?
AND, what about the month in cell N2, is everything to populate the John Melvin tab supposed to be from a given month based on N2?
Is the John Melvin tab just a sample tab and not really representative of John, in other words should the formula be variable to pull out Jeff Bennett if his name populates that cell? Or are you going to have a tab for every sales rep?
Yes, it should work off that Cell if possible or at least I would prefer it to do so. In Google Sheets, the name had to be in the formula itself using "Query", it wouldn't be a problem moving forawrd either, I would just create a sheet for each salesperson.
Also yes that John Melvin is a sample, I will need to do the same for each salesperson. As mentioned above, I simply created a sheet titled for each salesperson which was not a problem. I had removed the other salesperson tabs so that I could just duplicate the one titled John Melvin and change the formulas to the desired Reps.
The month cell N2 was populated by the date in the first entry, cell A5. So if the data returned was 12-5, the month "December, 2019" would populate.
For clarification, the formula in A4 is how excel converted the original formula from Google Sheets. This was not something I was attempting.
The original read as =QUERY(Sales!$A$3:$R$332,"SELECT A,B,C,D,H,I,J,K,L,M,N,O,P,Q,R WHERE F ='John Melvin' OR G = 'John Melvin'",1)
Which says go to table "Sales A3:R332, selecting columns A,B,C,D.... where Column F or G is "John Melvin".
This would then pull the headers and data associated with John Melvin in either Salesperson #1 or Salesperson #2
Last edited by Jaxdad; 12-12-2019 at 03:00 PM.
as I think about it I think it will be a series of somewhat complex array formulas but I would think that a pivot table would be the answer for your needs.
That seems to be where I see it heading as well, I just didn't know if I was missing something more simple. I've not used Excel for many years and although much is similar, still getting re-acquainted with Excel and changing the thought process is a challenge at times.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks