How to get a cell and the immediate cell below it in a table-CodePudding

Is there a way that I can get all the cells that contain "Student" along with the cell that's below it and put it in another table next to each other?

Maybe this picture will illustrate my problem better:

The main problem is to simply look in a column all the cells that contain "Student" and get the cell right below it too (discard everything else).

Will the best approach be to use VLOOKUP() or the SEARCH(), I'm struggling to create a formula for this small task. Any help would be greatly appreciated.

CodePudding user response：

Just HSTACK two filters, the second with and offset range:

=HSTACK(
    FILTER(A3:A17,ISNUMBER(SEARCH("Student",A3:A17))),
    FILTER(A4:A18,ISNUMBER(SEARCH("Student",A3:A17))))
           ^^^^^^Note the offset

With irregular spacing:

CodePudding user response：

This answer is based on your previous thread, I can update the formula's if needed but I am guessing this example above is just an example?

This will require a helper column to my understanding, in order to trim out the fat.

=IFERROR(FILTER(FILTER(A1:A1000,NOT(ISNUMBER(FIND("titanium",A1:A1000)))*NOT(ISNUMBER(FIND("beagle",A1:A1000)))*NOT(ISNUMBER(FIND("legend",A1:A1000)))*NOT(ISNUMBER(VALUE(LEFT(A1:A1000,FIND(".",A1:A1000)-1))))),FILTER(A1:A1000,NOT(ISNUMBER(FIND("titanium",A1:A1000)))*NOT(ISNUMBER(FIND("beagle",A1:A1000)))*NOT(ISNUMBER(FIND("legend",A1:A1000)))*NOT(ISNUMBER(VALUE(LEFT(A1:A1000,FIND(".",A1:A1000)-1)))))<>0),"")

C1 Required to be dragged down for X number of cells

=IFERROR(INDEX(FILTER(IFERROR(LEFT(RIGHT($B$1:$B$1000,LEN($B$1:$B$1000)-FIND("/",$B$1:$B$1000)),LEN(RIGHT($B$1:$B$1000,LEN($B$1:$B$1000)-FIND("/",$B$1:$B$1000)))-1),$B$1:$B$1000),IFERROR(LEFT(RIGHT($B$1:$B$1000,LEN($B$1:$B$1000)-FIND("/",$B$1:$B$1000)),LEN(RIGHT($B$1:$B$1000,LEN($B$1:$B$1000)-FIND("/",$B$1:$B$1000)))-1),$B$1:$B$1000)<>0),ROWS(C$1:C1)*2-1),"")

D1 Required to be dragged down for X number of cells

=IFERROR(INDEX(FILTER(IFERROR(LEFT(RIGHT($B$1:$B$1000,LEN($B$1:$B$1000)-FIND("/",$B$1:$B$1000)),LEN(RIGHT($B$1:$B$1000,LEN($B$1:$B$1000)-FIND("/",$B$1:$B$1000)))-1),$B$1:$B$1000),IFERROR(LEFT(RIGHT($B$1:$B$1000,LEN($B$1:$B$1000)-FIND("/",$B$1:$B$1000)),LEN(RIGHT($B$1:$B$1000,LEN($B$1:$B$1000)-FIND("/",$B$1:$B$1000)))-1),$B$1:$B$1000)<>0),ROWS(D$1:D1)*2),"")

B1 will filter the data, and cells C1 and D1 will strip out the text you are looking for.

CodePudding user response：

I know am late to the party, but you can try this something along the lines using TAKE(), TRANSPOSE() & WRAPCOLS()

• Formula used in cell C3

=TAKE(TRANSPOSE(WRAPCOLS(A3:A17,5)),,2)

Note: This works if the spacing is consistent.

For Irregular Spacing using XLOOKUP(), FILTER() & HSTACK()

• Formula used in cell H3

=LET(_Student,FILTER(F3:F17,ISNUMBER(SEARCH("Student",F3:F17))),
HSTACK(_Student,XLOOKUP(_Student,F3:F17,F4:F18)))