I have went through some trial and error working on the wrong formulas for what I thought I needed and I've now narrowed it down to the exact formula/concept that I need for my use case.

I need a formula that searches the header row (Row 1 of Sheet 1) for occurrences of a specific string, for instance HouseNumber, and when it finds an occurrence, it grabs the data from the cell below it (from Row 2 of Sheet 1) and copies that over to a different sheet (starting at Row 2 of Sheet 2, leaving Row 1 as a header row), then continues looking for the second occurrence, then once it's found, it grabs the data from the cell below it (from Row 2 of Sheet 1), and copies that to the next row down on the different sheet (Row 3 of Sheet 2), then continues until no more occurrences are found, etc...

So for example, in Sheet 1 below, search Row 1 for all occurrences of HouseNumber and copy the values in the cells below them, from Row 2, over to separate rows in Sheet 2 (so a formula should go in Sheet 2 at A2).

Sheet 1:

Sheet 2:

Then do the same as above, except search for occurrences of HouseStreet (so a separate formula should go in Sheet 2 at B2).

Sheet 2:

I have been trying nested functions with INDEX and MATCH, but it is not fully working and also it is only structured to search for / return one occurrence / value, not all occurrences/values.

CodePudding user response：

If you have Windows Excel 2021 (which have the functions used), you can use the following formulas:
For the address table 1, I used a Table with structured addressing, but you could use regular addressing if you prefer

HN: =FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,FILTER(adrTbl,ISNUMBER(FIND("Number",adrTbl[#Headers])))) & "</s></t>","//s")

HS: =FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,FILTER(adrTbl,ISNUMBER(FIND("Street",adrTbl[#Headers])))) & "</s></t>","//s")

results should spill down as far as needed

adrTbl

Results

Note that you can expand the table rows and/or columns and the formula should still work.

If you have an earlier version of Excel, I'd suggest using Power Query

Custom Function
Pivot with no aggregation

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/
//rename this query fnPivotAll

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Main Query

let
    Source = Excel.CurrentWorkbook(){[Name="adrTbl"]}[Content],

//Unpivot all the columns to => data in just two columns
    unPivot = Table.UnpivotOtherColumns(Source,{},"Attribute","Value"),

//split off the number from the column header to normalize them
//If you have other columns => unwanted attributes, may need to filter after the Split
    #"Split Column by Delimiter" = Table.SplitColumn(unPivot, "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),

//Pivot on the Attribute with no aggregation
    pivot=fnPivotAll(#"Removed Columns","Attribute.1","Value")
    
in
    pivot

CodePudding user response：

Solved it with a formula:

=arrayformula(query(transpose(to_text(Sheet1!1:2)),"Select Col2 where Col1 contains 'HouseNumber'"))