Forum Discussion

AngelaMR's avatar
AngelaMR
Copper Contributor
Mar 10, 2020

Covert two lines to two columns

I have some data as follows: 

Header1: Data A

Header2: Data B

Header1: Data A

Header2: Data B

Header1: Data A

Header2: Data B

 

I'm sure this is very simple to do, but I can't work out how. How do I change it, so that it appears as

Header1 | Header 2

Data A    | Data B

 

Thanks

excel

2 Replies

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Mar 11, 2020

    Hi AngelaMR 

     

    Please find below query can be handle with new dynamic arrays & classic array method

     

     

    New dynamic arrays Formula

     

     

    =FILTER($B$4:$B$9,C$3=$A$4:$A$9,"No Data")

     

     

     

    Classic Array Method (Ctrl+Shift+Enter)

     

     

    =IFERROR(INDEX($B$4:$B$9,SMALL(IF((F$3=$A$4:$A$9),ROW($A$4:$A$9)-ROW($A$3),""),ROW()-ROW($A$3))),"")

     

     

     

    Attached is the sample file for your ready reference.

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer.

  • adlih's avatar
    adlih
    Copper Contributor
    Mar 11, 2020

    AngelaMR 

     

    IF "Header1: Data A" IN BOX A1, "Header2: Data B" IN BOX A2,

    THEN FOR BOX B2 PLS ENTER:

    =LEFT(A1,(FIND(":",A1)-1))&" | "&LEFT(A2,(FIND(":",A1)-1))

     

    THEN FOR BOX B3 PLS ENTER:
     =RIGHT(A1,(FIND(":",A1)-1))&" | "&RIGHT(A2,(FIND(":",A1)-1))

Resources