0

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?

Improve this question

1 Answer 1

Reset to default
1

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):

=FILTER(Formdata!A:D,VLOOKUP(ROW(Formdata!A:A),'Rule-In'!D:D,1,FALSE)=ROW(Formdata!A:A))

This just filters your original Formdata! sheet to include only the rows found in Rule-In!D:D.

Then you just change Formdata! to FormdataLimited! 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.

Improve this answer
3
  • Kenneth, I should note that this is beyond the scope of what sites like this typically offer, as it is more of extended project work rather than one formula or general guidance. Just keep this in mind as you continue your project. If you have lots of such complex elements you're going to want to be using, you will likely need to hire someone. (Most of us have clients who pay for extensive solutions like this, and so we have to limit the extent of the volunteer solutions we can offer on sites like this one.) But happy to help as far as I have here.
    – Erik Tyler
    Commented Jul 14, 2020 at 10:05
  • Wow... I had no idea the solution was this complex. I really appreciate your help! Works perfectly. Very complex though. You are using a lot of functions that I had no idea existed - going to look into them. I have changed D and E, so they match the new sheet. Have not thought about extracting some of the form entries into a new sheet, and work from that data instead. Going to have some fun with this stuff :) Thanks again.
    – Kenneth Poulsen
    Commented Jul 14, 2020 at 10:27
  • Keep in mind: we all started somewhere, learning little by little. If you stick with it, you'll be looking at formulas that once boggled your mind and reading them like comic books. Please do take a moment to mark the answer as the Accepted answer, as this helps future users find what they're looking for while also signaling other techs here not to continue to work on this particular problem.
    – Erik Tyler
    Commented Jul 14, 2020 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.