Forum Discussion
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
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.
That's fantastic.
Is there any way to remove the word miles for the Odometer cell?
Thanks
Sean
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
Thanks