d2 | BSD Licensed Open Source project whose purpose

I have two google sheets in the same workbook and I am trying to import certain columns from one into the other sheet based on what hour of class was chosen. For example, I have the Date in column A, Name in B, Email in C, and Number in D and other stuff, then the Class in I.

Column A - Date Column B - Name Column C - Email Column D - Number ... Column I - Class

My goal is to import column B through D if column I has a certain class. I tried a using this if statemnt: =if('Confirmação'!I2 = A1,(=importrange("sheet_url","Confirmação!B2:D2")), "NOPE") where A1 has the name of the class to look for, but it resulted in a #Error.

Then I tried a variety of query such as the following: =QUERY({'Confirmação'!B2:D2,'Confirmação'!I2},"Where I = 'Terça 19h English 1'") =QUERY({'Confirmação'!B2:D2, 'Confirmação'!I2},"Where 'Confirmação'!I = 'Terça 19h English 1'")

and also added the IfError: =iferror(QUERY('Confirmação'!B4:I4,"Where I = 'Terça 19h English 1'"),"Vaga")

Could someone correct my functions or help with a google script? Thank you! Very much appreciated! (Sorry about the Portugues-it's a project I am working on in Brazil)

I have this situation on google sheets

image

I wanted to make a monthly report with sumifs with 2 conditions like the image above. But all I got was errors.

Code I am using

=sumifs(D2:D13;D1:G1;C16;B2:B13;or(B2:B13="apple","cherry","watermelon"))

Dataset looks like this : This is a sample dataset for number of employee login activity named - activity

I need to calculate few metrics, was able to do in python data frames, but new in mySQL.

1. what is the average number of employee active per day for month of jan 2018 by dept ( was able to do somewhat half of it, but results coming are not correct.

2. number of unique active employee (login >0) per month for jan 2018 for each dept_id (was able to do it)

3. month over month growth for all dept_id from dec-2017 to jan 2018 where at least one employee was active (login >0) - no idea how to do this in sql

4. fraction of users who were active in each dept_id for dec 2017 and were also active in the same dept_id for jan 2018

5. how many employee login in on 3 or more consecutive days in jan 2018

Any help would be appreciated.

Query written for case 1:

Situation: I have two dataframes df1 and df2, where df1 has a datetime index based on days, and df2 has two date columns 'wk start' and 'wk end' that are weekly ranges as well as one data column 'statistic' that stores data corresponding to the week range.

What I would like to do: Add to df1 a column for 'statistic' whereby I lookup each date (on a daily basis, i.e. each row) and try to find the corresponding 'statistic' depending on the week that this date falls into.

I believe the answer would require merging df2 into df1 but I'm lost as to how to proceed after that.

Appreciate any help you might provide! Thanks!

df1: (note: I skipped the rows between 2019-06-12 and 2019-06-16 to keep the example short.)

df2:

Desired output:

code for the dataframes d1 and d2

im currently having Issues with removing duplicates of a Card in a given CardArray. My current Code is attached. My Issue isnt removing duplicates themself, but how to put the Cards from the Set back into the CardArray, as that is required from the Task.

I know there are some other questions (with answers) to this topic. But no of these was helpful for me.

I have a postfix server (postfix 3.4.14 on debian 10) with following configuration (only the interesting section):

I wanted a ArrayFormula at C1 which gives the required result as shown.

Entry sheet:
(Column C is my required column)

Date Entered is the date when the Name is Assigned a group i.e. a, b, c, d, e, f

Criteria:

1. The value of count is purely on basis of Date Entered (if john is assigned a on lowest date(10-Jun) then count value is 1, if rose is assigned a on 2nd lowest date(17-Jun) then count value is 2).
2. The value of count does not change even when the data is sorted in any manner because Date Entered column values is always permanent & does not change.
3. New entry date could be any date not necessarily highest date (If a new entry with name Rydu is assigned a on 9-Jun then the it's count value will become 1, then john's (10-Jun) will become 2 and so on)

Example:

After I sort the data in any random order say like this:

Random ordered sheet:
(Count value remains permanent)

And when I do New entries in between (Row 4th & 14th) and after last row (Row 17th):

Random Ordered sheet:
(Doesn't matter where I do)

1. ={"AF Formula1"; ArrayFormula(IF(B2:B="", "", COUNTIFS(B$2:B, "="&B2:B, D$2:D, <"&D2:D)+1))}
2. ={"AF Formula2";INDEX(IFERROR(1/(1/COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B)))))}

I tried to figure my own ArrayFormula but it's not working:

I got Formula for each cell:
=RANK($D2,FILTER($D$2:$D, $B$2:$B=$B2),1)
I figured out Filter doesn't work with ArrayFormula so I had to take a different approach.

I took help from my previous question answer (Arrayformula at H3) which was similar since in both cases each cell FILTER formula returns more than 1 value. (It was actually answered by player0)

Using the same technique I came up with this Formula which works absolutely fine :

=RANK($D2, ARRAYFORMULA(TRANSPOSE(SPLIT(VLOOKUP($B2, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ","))), 1)

Now when I tried converting it to ArrayFormula: ($D2 to $D2:$D & $B2 to $B2:$B)

=ARRAYFORMULA(RANK($D2:$D,TRANSPOSE(SPLIT(VLOOKUP($B2:$B, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ",")), 1))

It gives me an error "Did not find value '' in VLOOKUP evaluation", I figured out that the problem is only in VLOOKUP when I change $B2 to $B2:$B.

I'm sure VLOOKUP works with ArrayFormula, I fail to understand where my formula is going wrong! Please help me correct my ArrayFormula.

Here is the editable sheet link

I am having issues with my eBAY Scraper and can not work out why. Although it is pulling the data off fine, it misses SOME of the data OFF for the first row and then for each first row of every Loop and therefore the data is not in the correct row.

Q) Why is it missing the data at the start and then for each loop?

I think It may have something to do with the title extracting slower that the rest of the items, however I can not work it out as I am very limited with vba. I have attached a demo, for your viewing.

I am not looking for a full rewite of the code, just pointing in the right direction or a SLIGHT change to MY code. As I stated I and very limited in vba, I can understand my code, anything more advanced will be out of my depth.

Demo Download - Download Excel File

WebSite - Ebay.co.uk

Ebay Product Page - Prodcts Shown may vary browser to browser

I have colour coded it so you can see better

This is what it is doing

When It Should be This

For some reason it misses out Price, Condition, Former Price & Discount for the first item on start and EVERY Loop. For every loop that it misses the items out the Price, Condition, Former Price & Discount become MORE out of line

1st Loop - Items are NOW 2 rows out of line

2nd Loop - Items are NOW 3 rows out of line

As I searched 3 pages (2 pages + 1 extra) and it looped 3 time it has missed the first row on each loop. I am 3 rows out. I think this may have too do with the Title of the item as it extracts a bit slower then the rest of the items

End Of Extraction

This is my code

I have these data in my booked_tickets table:

1st Row: A1, A3, B2

2nd Row: C3, D2, A2

3rd Row: C1, A2, D1

When I tried to concatenate these data into one string, I get this: A1, A3, B2C3, D2, A2C1, A2, D1

What I wanted is: A1, A3, B2, C3, D2, A2, C1, A2, D1

How do I concatenate like that?