Forum Discussion

Sean Booth's avatar
Sean Booth
Copper Contributor
Jun 15, 2018

Finding particular data in a single column

HI

 

Looking for away of extracting the registration number and the mileage for each vehicle and creating a new work sheet containing the values in two columns.

 

Thanks in advance

excel
Excel on Mac
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 15, 2018

    Hi Sean,

     

    As variant, you may create new sheet with Registration in first cell of column A and Odometer for column B, in A2 use formula

    =IFERROR(OFFSET(Sheet1!$B$1,AGGREGATE(15,6,1/(Sheet1!$B$1:$B$4000=$A$1)*ROW($A$1:$A$4000),ROW()-1),0),"")

    similar for B2 and after that drag down these two cells since empty one appears.

    Please see attached.

    • Sean Booth's avatar
      Sean Booth
      Copper Contributor
      Jun 15, 2018

      That's fantastic.

       

      Is there any way to remove the word miles for the Odometer cell?

       

      Thanks

      Sean

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jun 15, 2018

        You could wrap with SUBSTITUTE

        =IFERROR(SUBSTITUTE(OFFSET(Sheet1!$B$1,AGGREGATE(15,6,1/(Sheet1!$B$1:$B$4000=$B$1)*ROW($A$1:$A$4000),ROW()-1),0)," miles",""),"")

        and attached

         

Resources