Compiling a list using INDEX but need to skip certain rows-CodePudding

I'm compiling a list based on the first answers recieved between row N and AF.

I'm using these two formulas: =INDEX(N2:O2,MATCH(FALSE,ISBLANK(N2:O2),0)) and =INDEX(R2:AF2,MATCH(FALSE,ISBLANK(R2:AF2),0))

Is there a way to combine them whilst not searching in rows P & Q?

These are generated from a Form response so can't just be switched around.

CodePudding user response：

try:

=INDEX({N2:O2, R2:AF2}, MATCH(FALSE, ISBLANK({N2:O2, R2:AF2}), 0))

CodePudding user response：

If Sheet1 is an intake sheet of form results, you should not add any data, formulas or even formatting to that sheet. It virtually always causes issues. A form intake sheet should be left exactly as it is. A new sheet can then be used to bring over the results of the form intake sheet as you want to see them.

However, since you didn't specify any of that, I will supply a formula written to work in the same sheet as your posted example and in-sheet examples.

Clear an entire column and place the following in the top cell of that column:

=ArrayFormula({"Attendee Name"; IF(E2:E="",,IFERROR(REGEXEXTRACT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(IF(N2:AK="",,N2:AK&"~"),N1:AK1=N1)),,COLUMNS(N1:AK1)))),"\s*([^~] )"),"(none listed)"))})

This one formula will produce a header (the text of which you can change within the formula itself as you lie) and all valid results for all rows.

The inner IF will append a tilde (~) to any non-null entries in the range N2:AK.

FILTER will keep only those columns in this range where the header is the same as the header in N1 (i.e., "Attendee Name").

TRANSPOSE(QUERY(TRANSPOSE( ),,COLUMNS( ))) is colloquially called a "Query smash." It will form one cell from all horizontal results per row.

TRIM will cut any preliminary spaces and form a true string.

REGEXEXTRACT will pull the from the first non-space character up to but not including the first tilde (from those appended in the first step)—in other words, the first full valid entry from any column.

IFERROR will return a message if there is an error, with the likely error being that there were no valid entries for "Attendee name" in any column.

The outer IF will leave the cell blank if the no training event exists in E2:E.

{ } forms a virtual array that places the header over all other results.

ArrayFormula( ) signifies that multiple results will be processed at once.

Because this is an array formula that is being "asked" to process every row, you cannot manually type into any cell of this results column. If you do, you will "break the array"; everything except what you just typed will disappear, leaving only an error in the formula cell. If you need to add or change a name, you need to do that in the raw results range (e.g., manually type a name or a new name in Col N), which will then turn up in the formula output range.