google sheets
Question is related to this Google Sheet:
https://docs.google.com/spreadsheets/d/1U13dvWPk8-hMybmhE1RKZ66y7RXXGPWt91_BNFWIup4/edit?usp=sharing
How can I limit column A and B to only include Formdata of the last 2 entries by that person (limit by character name). Example, Jesper has made 3 form submits, but I would like to limit this to only include the latest 2. How can I do this?
The data is there, but it's out of sight because of the blank rows that are included due to the use of whole columns (A2:B). Filter the rows where A or B cells are not empty. One way to do that is by using QUERY(), i.e.:
=QUERY({{'source data 1'!A2:B};{'source data 2'!A2:B}},
"Select Col1,Col2 Where (Col1<>'' OR Col2<>'') Order by Col1",0)
paste in D1 cell and drag to the right:
=FILTER($A:$A, MOD(ROW($A:$A)-COLUMN(A1), 5)=0)
Best Answer
Kenneth, I have added two sheets to your spreadsheet: "Rule-In" and "FormdataLimited."
"Rule-In" has four formulas (currently highlighted in blue):
1.) In A1
=ArrayFormula({Formdata!B$1;UNIQUE(FILTER(Formdata!B:B,Formdata!B:B<>"",ROW(Formdata!B:B)<>1))})This forms an array of all of your unique player names.2.) In B1
=ArrayFormula({"Most Recent Row";VLOOKUP(FILTER(A:A,A:A<>"",ROW(A:A)<>1),QUERY({Formdata!B:B,Formdata!A:A,ROW(Formdata!A:A)},"Select * Order By Col2 Desc"),3,FALSE)})This looks up each player from A:A in an upside-down QUERY ordered by most recent date to furthest date. The QUERY also includes the ROW of each entry. The VLOOKUP finds the user in the upside-down form QUERY and returns the row of that entry (i.e., the row of the most recent submission by each user).3.) In C1
=ArrayFormula({"Second-Most Recent Row";IFERROR(VLOOKUP(FILTER(A:A,A:A<>"",ROW(A:A)<>1),QUERY({FILTER(Formdata!B:B,ISERROR(VLOOKUP(ROW(Formdata!B:B),B:B,1,FALSE))),FILTER(Formdata!A:A,ISERROR(VLOOKUP(ROW(Formdata!A:A),B:B,1,FALSE))),FILTER(ROW(Formdata!A:A),ISERROR(VLOOKUP(ROW(Formdata!A:A),B:B,1,FALSE)))},"Select * Order By Col2 Desc"),3,FALSE))})This forms another upside-down QUERY that rules out any rows found in B:B (i.e., the last VLOOKUP-QUERY combo). In short, it finds the user again, not including the most recent entry (which gives the next-most recent entry). The QUERY here is formed by taking the same elements from the last QUERY and FILTERing them with ISERROR(VLOOKUP(ROW(...),B:B,1,FALSE)) each time (which is what rules out already-listed rows).4.) In D1
=ArrayFormula({"Limit Forms To These Rows";1;SORT(FILTER(FLATTEN(B:C),ISNUMBER(FLATTEN(B:C))),1,TRUE)})This combines the found rows from B:C (i.e., the row numbers of all most-recent and next-most-recent entries from your original Formdata! sheet).The "FormdataLimited" sheet contains one FILTER formula (also currently highlighted in blue):
This just filters your original Formdata! sheet to include only the rows found in Rule-In!D:D.
Then you just change
Formdata!toFormdataLimited!in the "List" sheet formulas, where you want to reference only the form submissions that were among the two most recent per player. (I have already done this for you in the first sheet, cell A1. I wasnt sure if you wanted Columns D:E in that first sheet to reference the full list or the limited list, so I left that referencing the full list as you had it.)Keep in mind that you can right-click the tab/name of any sheets you don't want to see (e.g., Rule-In) and Hide them. This is standard practice, to Hide sheets that process information that isn't relevant to your everyday needs.