yogi_From 'Master Data"!A:E Pull Data For Each Month Based On Months In Columns A and B And Rules Specified In 'Master Data"!G:G

Google Spreadsheet   Post  #2416

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-08-2018

question by: RichrdRG
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/x1p7jRD68BI;context-place=mydiscussions

Very specific query to pull from specific workdays.

Hello, 

I am stuck trying to use a query to pull a very specific data set from another sheet. 

Problem: 

I need a query function to pull data from a range of data, inside that range of data are 2 columns of dates. Col1 is a date of a reported error, Col2 is a date when the error was made. The errors are categorized by month but have a 3 workday grace period into the next month, if they are reported in this grace period they are listed in the previous month, if they are reported after the grace period they are counted in the current month. 

E.g: 

Error made on 3/30/2018, reported on 4/4/2018 counts for March and should be listed on March's sheet

Error made on 3/30/2018, reported on 4/5/2018 counts for April and should be listed on April's sheet

Error made on 4/1/2018, reported on 4/4/2018 counts for April and should be listed on April's sheet. 

I have worked around and figured out how to almost do it, but it would require fixed days instead of workdays, and this was reported as unacceptable. 

If anyone can help, I have created a test sheet to share with how the end result of the data should look.

For the final sheet I would like to have the data source be an entirely other sheet if that makes any difference. 

Thank you!

https://docs.google.com/spreadsheets/d/1ufeNPm05kDnr8vG06zvFefjseJa1jJsoXtPMSV3Ejtw/edit?usp=sharing

 

yogi_From Data In Sheet2 Check Whether Facility 1 to 5 On Specified Dates Is Occupied Or Vacant

Google Spreadsheet   Post  #2415

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-05-2018

question by: Mr. Rich
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Ez_Ttz2Q42g;context-place=forum/docs

Array Value

How do I fix this?

=IF(INDIRECT(ADDRESS(MATCH(C4,Sheet2!A1:A12),2,,,"Sheet2")):INDIRECT(ADDRESS(MATCH(D4,Sheet2!A1:A12),2,,,"Sheet2"))="","Vacant","Occupied")

Thanks

yogi_List Unique Players And Their Email Address those are in Column G And Not In Column A

Google Spreadsheet   Post  #2414

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-02-2018

question by: Mike Damus
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/mJfMUEXXyBI;context-place=forum/docs

oh GREAT and POWERFUL SPREADSHEET GURUS, hear my song on UNIQUE function?

I am a spreadsheet novice but I have found this forum so helpful. To those of you out there who take the time to answer these questions you really help a lot of people out there. I basically end up copying the formulas you come up with and modify it a little.

I have spent a lot of time on this and I figured someone out there knows how to do it.

I'm searching for a formula that will return two columns of data BUT use the unique data from one column, not the unique of both columns.

We use a google form to see who is playing on our baseball team each week. And over the years with the help of this forum I think it works pretty well kinda. Players choose their name from a list and their status from IN or OUT.

Question:

Can you query or import data from two columns from one sheet to another and filter results based on unique values in one of the columns?

Like if the columns say:

A            B

ROB              IN

MIKE            IN

MIKE            OUT

Is there a way to end up with just:

A            B

ROB              IN

MIKE            OUT

I tried to explain it all on the spreadsheet with data please take a look!

Here is my spreadsheet:

https://docs.google.com/spreadsheets/d/1HQRqks6c6LPcpe9of6VF4H7cAUfU4ZQzaZ-E-CvJIO4/edit?usp=sharing

Formulas I use:

Formula used on C2: =UNIQUE(QUERY(A2:B,"select * where A<>'' order by B asc"))

Formula used on G2: =UNIQUE({QUERY(A2:B,"select * where B<>'' order by B asc");QUERY(E2:F,"select *")})

Thanks so much for taking a look. I appreciate it!

Mike

-------------------------------------------------------------------------------------------------------------------------------------

in reference to second part of your question

yogi_List Unique Players With Their Latest Status

Google Spreadsheet   Post  #2413

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-02-2018

question by: Mike Damus
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/mJfMUEXXyBI;context-place=forum/docs

oh GREAT and POWERFUL SPREADSHEET GURUS, hear my song on UNIQUE function?

I am a spreadsheet novice but I have found this forum so helpful. To those of you out there who take the time to answer these questions you really help a lot of people out there. I basically end up copying the formulas you come up with and modify it a little.

I have spent a lot of time on this and I figured someone out there knows how to do it.

I'm searching for a formula that will return two columns of data BUT use the unique data from one column, not the unique of both columns.

We use a google form to see who is playing on our baseball team each week. And over the years with the help of this forum I think it works pretty well kinda. Players choose their name from a list and their status from IN or OUT.

Question:

Can you query or import data from two columns from one sheet to another and filter results based on unique values in one of the columns?

Like if the columns say:

A            B

ROB              IN

MIKE            IN

MIKE            OUT

Is there a way to end up with just:

A            B

ROB              IN

MIKE            OUT

I tried to explain it all on the spreadsheet with data please take a look!

Here is my spreadsheet:

https://docs.google.com/spreadsheets/d/1HQRqks6c6LPcpe9of6VF4H7cAUfU4ZQzaZ-E-CvJIO4/edit?usp=sharing

Formulas I use:

Formula used on C2: =UNIQUE(QUERY(A2:B,"select * where A<>'' order by B asc"))

Formula used on G2: =UNIQUE({QUERY(A2:B,"select * where B<>'' order by B asc");QUERY(E2:F,"select *")})

Thanks so much for taking a look. I appreciate it!

Mike

yogi_Present In A Single Column Starting From Cell A2 Entries From Specified Cells Of A Specified Sheet

Google Spreadsheet   Post  #2412

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-01-2018

question by: Paul Rosasco
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/_Yyqm7R7Qn8;context-place=forum/docs

Trying to use fill down to fill down to access data from another sheet but it skips cells.

So I have one sheet with a bunch data in A:Z. I am using =TRANSPOSE('Sheet1'!A52:Z52) to take the data from that sheet and transpose it on another sheet. This would transpose 26 pieces of data. I then want to select the transposed data and fill it down so the next data it would show is from A53:Z53 then A54:Z54 etc... I want to do this a ton of times. Unfortunately when I try to fill it down, it shows data from A80:Z80 then the next shows data from A110:Z110. Not sure why it skips cells. Need to figure out a way around this.

yogi_Pull Members Order Data (Product and Quantity) by Email Address From Orders Data In Another Sheet

Google Spreadsheet   Post  #2411

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-01-2018

question by: Les Rose
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/zx8XpLWLmPk;context-place=forum/docs

Does VLOOKUP work for non-unique records?

I'm having trouble expressing this in a thread title let alone working out how to do it! Let me explain:

I have a 2 sheets. One contains a list of users, the other is a record of their transactions. They can each choose from a menu of possible transactions. In fact this is a data export from an online shop, so we can regard the transactions as products. I need to report on who has bought what, or not yet bought anything. The problem is that each user can order more than one product, so the list of transactions is not unique. I have looked at VLOOKUP but I think it will only work for unique records. Am I right? Is there a better approach? Whatever method I use needs to be capable of development so that I can plug in other queries. Sorry but I am very rusty on spreadsheets in general. I was not bad 30 years ago but things have moved on a bit.

yogi_LookUp Value In Cell A1 Of Sequential Sheet Number Listed In Cells A3:A

Google Spreadsheet   Post  #2410

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-01-2018

question by: Ed100-Jeff
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Bv9K27FjHxg;context-place=forum/docs

Using INDIRECT() to refer to a newly added sheet in a workbook ($10 bounty for solution!)
Issue: I'm struggling to get the INDIRECT() function to work across sheets when new sheets are added to a workbook.

Simplified example of the challenge: How can I make the first sheet of this workbook calculate when adding new sheets???:

https://docs.google.com/spreadsheets/d/1rLvD_hyZ--as75Ap1b3Ip2AT6RLnKwLZLG8HGsYvvgc/edit#gid=0 

[  Full case: The sheet I'm building is for a nonprofit blog (Ed100.org) -- here is the work in progress: 

Public workbook link: https://docs.google.com/spreadsheets/d/1IFZ4QyEW7Bwy3_QIs_D0dExpid__yGLp3HMIVD3Y-Yg/edit?usp=sharing

To replicate the problem: 

• Duplicate the sheet named "Raven"
• Rename the duplicate sheet "Test"
• Activate the sheet named "School List"
• In Cell B17 enter the value "Test"
• Activate the sheet "Decision Matrix"

The expected result is for data indirectly referenced from the new "Test" sheet to be reflected in the "Decision Matrix" sheet in cell F17. But it doesn't work reliably. I find that I have to mess with refreshing different cells until I get lucky and trigger a recalc that causes the "Decision Matrix" sheet to "see" the new sheet. 

What am I missing?