Excel question.....

[dan1981]

All,

I've got al ist of data - car regs. Some of them occur more than once. I need to get the spreadsheet to only show the regs that are on there more than once.

Any ideas?

Cheers

Dan

ETA: Sorry this was meant to go in the computer section - can it be moved please?

Edited by dan1981 on Friday 1st August 12:36

[WTD]

select the car reg's

then Data menu, Filter, Advanced Filter

Tick unique records only and press OK

[Minty UFB]

Sort Them in order then use an If statement to see if the first is the same as the second etc.


If (1=1,A1=A2)

[dan1981]

WTD said:

select the car reg's

then Data menu, Filter, Advanced Filter

Tick unique records only and press OK

i need to see the regs that do occur more than once. I need to exclude the ones that are only on there once.

[dan1981]

Minty UFB said:

Sort Them in order then use an If statement to see if the first is the same as the second etc.


If (1=1,A1=A2)

tried that also - unfortunatly it doesn't quite work as even though it says a1 might equal b1 but it won't say that b1 = a1 as b1 will be looking at c1!

(Hope that makes sense!)

[dougc]

dan1981 said:

WTD said:

select the car reg's

then Data menu, Filter, Advanced Filter

Tick unique records only and press OK

i need to see the regs that do occur more than once. I need to exclude the ones that are only on there once.

Do a VLOOKUP using the result of this filter.

[VladD]

If your registrations are in column a, cells 1 to 8, put this in B1:

=COUNTIF($A$1:$A$8,A1)

Copy that into cells B2:B8

The use auto filter on column B to exclude the ones.

[dan1981]

VladD said:

If your registrations are in column a, cells 1 to 8, put this in B1:

=COUNTIF($A$1:$A$8,A1)

Copy that into cells B2:B8

The use auto filter on column B to exclude the ones.

thanks vlad that works perfectly - i can now filter the results to show anythgin larger than one.