Formula to copy paste text a certain cell?

[Jyggalag]

Hi all,

I currently have a data set that looks like this:

I would like to transpose my information down under columns H-L so that for ALL cells in A that contains "George", their respective cells in columns B-F will be transposed under column H.

Likewise the same for all cells that contain any of the titles in column I-L

The trick is that all the cells in column A contain the title in columns H-L, however I can't figure out how to get this done

I hope my question makes sense? Otherwise please let me know and I'll be happy to elaborate

@Fluff I think you may have done something similar to this before?

Kind regards,
Jygglaag

 

[Jyggalag]

I can do something along these lines:

However, the issue for me is that:

1) I can only do if it cell itself is named "George", but I want it to be if the cell just contains the string "George" anywhere in its value at all

and

2) If I try to "pull" the formula down, it creates a spill:

 

[jaeiow]

Two links I think may help with this:
Filter with Contains:

FILTER with partial match

To use select records from a set of data based on a partial match, you can use the FILTER function with the SEARCH function. In the example shown, the formula in G7 is: =FILTER(Table1,ISNUMBER(SEARCH(H4,Table1[Last]))*(H4""),"No results") where Table1 is an Excel Table that contains 100 rows of...

exceljet.net

Flatten and stack output:

https://www.mrexcel.com/board/threads/aflat.1175181

 

[Fluff]

Do you have the new functions such as TOCOL?

 

[Fluff]

If you do have TOCOL try

=TOCOL(FILTER($B$2:$F$22,ISNUMBER(SEARCH(H1,$A$2:$A$22))))

 

[Jyggalag]

Do you have the new functions such as TOCOL?

What is TOCOL and is it something I have to download externally, or is it automatically included in newer versions of Excel?

Also, apologies for the very late response!

 

[Fluff]

It's one of the new functions that has recently been released to the monthly channel & will probably be released to the semi-annual channel in January.

 

[Fluff]

Assuming you don't have TOCOL yet, another option is

=LET(f,FILTER($B$2:$F$22,ISNUMBER(SEARCH(H1,$A$2:$A$22))),c,COLUMNS(f),s,SEQUENCE(c*ROWS(f),,0),INDEX(f,INT(s/c)+1,MOD(s,c)+1))