Google-sheets – How to find last non-blank entry given a case-insensitive unique identifier

google sheets

I have a google sheet that is autofilled from a google form. A person is allowed to do the questionnaire as often as they'd like, but I would like to not make them answer every question every time. Every time they fill out the questionnaire they must provide their identity.

I've created an example of a similar sheet here.

In this case I have 3 users apple, orange, and banana. Each has both a price and an amount. What I want to do is find each unique case insensitive name, and their most recent input in each column. How would I go about doing this?

Best Answer

Filter out empty rows,Sort in reverse and vlookup:

E3:

=ARRAYFORMULA(UNIQUE(PROPER(A3:A11)))

F3:

=ARRAYFORMULA(VLOOKUP(E3:E5,SORT(FILTER({A3:B11,ROW(A3:A11)},B3:B11<>""),3,0),2,0))

G3:

=ARRAYFORMULA(VLOOKUP(E3:E5,SORT(FILTER({A3:C11,ROW(A3:A11)},C3:C11<>""),4,0),3,0))

Related Solutions

Google-sheets – How to prepare data to be summarized with a pivot table

Basically you have A, B and C columns as headers and the pair of columns with the data to be summarized. Use the array notation to build an array with the required data structure. In order to be able to include new form submissions, use references of the following form A2:C.

Add the following formula to the cell A1 of a new sheet.

=QUERY(
   {
     A1:C1,"Case Number","Hours";
     A2:C,D2:E;
     A2:C,F2:G;
     A2:C,H2:I;
     A2:C,J2:K
   },
   "Select * where Col1>0"
 )

A1:C1,"Case Number","Hours" is a row with the array headers. A2:C,D2:E and the other similar are a set of five columns, the first have first Case Numbers and hours column pair, the second the second column pair and so on.

Reference

Using arrays in Google Sheets - Google Docs editors Help

Google-sheets – Case insensitive character string filter for multiple individual columns (dynamic input for each)

It appears that you are using regexmatch just to test string equality. This isn't necessary, equalities can be entered simply as Data!A:A = A1. You don't even need lower, because string comparison in filter is case-insensitive:

=filter(Data!A:R, Data!A:A = A1, Data!B:B = B, Data!C:C = C1)

(Also, there is no need for single quotes around sheet name when it does not contain spaces.)

Best Answer

Related Solutions

Google-sheets – How to prepare data to be summarized with a pivot table

Reference

Google-sheets – Case insensitive character string filter for multiple individual columns (dynamic input for each)

Related Topic