Macro to reformat raw data in a different worksheet

I have two columns of raw data which are currently being manually reformatted (copy/paste) and are very time intensive. I am looking to create a simple macro which will make this process autonomous.

Macro Example.xlsx

Above is a link to a small sample of data and how I would like to process it.

Firstly, I am only interested in names beginning with "TH47#" (I do not care about any name where the 5th character is not numeric). I also do not care about any items called "Alll".

I want to populate sheet 2 with a reformatting of column A in which each unique name starting with "TH47#" will become a heading extending across Row 2 in sheet 2. In the example file, only three names will populate row 2 of sheet 2.

Under the headings in sheet 2, I want to list all of the items associated with the names selected.

I'm happy to answer any questions!!

This is brilliant. This may be too much to ask, but is there any way to offer the user a prompt where they can type in the first five letters they are looking for (instead of changing the macro from "TH47#")... This way when the name changes, the macro would not need to be updated. Thank you so much for your help, David!