Jun 15 2018 05:14 AM
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
Jun 15 2018 06:17 AM
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.
Jun 15 2018 06:56 AM
That's fantastic.
Is there any way to remove the word miles for the Odometer cell?
Thanks
Sean
Jun 15 2018 07:06 AM
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