Finding particular data in a single column

[Sean Booth]

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

[#2]

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.

[#3]

That's fantastic.

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

Thanks

Sean

[#4]

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

[#5]

Thanks