Help with Excel filtering please..

  Whaty 16:45 21 Feb 05
Locked

I have a list of 8 digit numbers in column 'A', the numbers are in the format 123-456-78. Each number appears more than once. I've arranged the column using 'Sort' so that all the duplicate numbers are next to each other.

I now need to be able to filter this column in such a way as to only display the 1st two of each set of numbers, eg:

123-456-78
123-456-78
234-567-89
234-567-89
987-654-32
987-654-32

etc.

Is there a way to do this.? My list contains 18,000 numbers so your help, any help, would be gratefully received.

Thanks in advance, Terry

  Whaty 16:46 21 Feb 05

Sorry about the formatting, the numbers in my example should be underneath each other, not on the same line...

  Graham ® 16:59 21 Feb 05

Dunno the answer, but you may need ASAP:

click here

PS, you need to double space to make a list on here :-)

  Graham ® 17:13 21 Feb 05

In ASAP, Columns/Rows, 'Colour each nth row or column'. Then filter the colours.

  Graham ® 17:18 21 Feb 05

No, that's not quite it, but something similar?

  Whaty 18:35 21 Feb 05

Thanks for trying Graham, I'm sure there must be a way....

  VoG II 18:49 21 Feb 05

This is not straightforward! One can "easily" eliminate duplicate records using an Advanced Filter but I don't know how to retain two of each number. It should be possible to do this using Visual Basic for Applications programming.

An alternative would be something like click here but you would need to set this up to look for each of your numbers in turn.

I suggest that you ask this question at click here - I have searched that site but could not find this particular problem.

  The BB 19:08 21 Feb 05

If your data is in column A then insert a column and try a formual like

=IF(AND(A2=A1,A2=A3),"",A2)

Though you'll need to sort first and last row out!

It will have a value for the first and last (or only) occurance.

You may then need to copy and paste values in another sheet and then sort and delete all the blanks (or whatever text you put in the "")

  Whaty 20:38 21 Feb 05

Thanks VoG & BB..

BB, your suggestion is close but I need just the first two entries for each number. On average there are five of each number and I need to seperate just the first two.

Thanks again for trying..

Regards,
Terry

  skeletal 20:41 21 Feb 05

This code will do what I think you want. There are many ways of doing it, and the code could be improved, but this works so hey!!

MAKE A COPY OF YOUR DATA!!! DON’T try this on your only copy, you may make a mistake in copying the code (formatting on this site is not very good for code!) and could lose everything.

Open the VBA editor by pressing Alt F11.

Copy the code into the editor (you may also notice the words “Option Explicit” at the top. In this case it does not matter if its there or not).

Make sure your list of numbers is in column A and starts at A2. In A1 type in any text you like as a dummy heading. The paired outputs will start in C2.

When you have put the code in, go to the toolbar at the top of the VBA editor and find and select Debug/Compile. If you get an error message, the editor will help you find the mistake.

There are several ways to make the code run, but the easiest is to put you cursor anywhere in the code and press F5. On my computer (AMD 2700XP) it took about 2 seconds to sort 20,000 cells in the way you describe. If it is too hard to read on the site, drop me an email and I’ll send it to you.

Sub Macro1()

Dim tempval
Dim countint As Integer
Dim countstr As String
Dim countcopies As Integer
Dim newcolcount As Integer
countint = 1
countcopies = 1
newcolcount = 2

Do
countstr = Trim(Str$(countint))
tempval = Range("A" & countstr).Value

countstr = Trim(Str$(countint + 1))

If Range("A" & countstr).Value = tempval Then
countcopies = countcopies + 1

If countcopies <= 2 Then
countstr = Trim(Str$(newcolcount))
Range("C" & countstr).Value = tempval
newcolcount = newcolcount + 1
End If

Else
countstr = Trim(Str$(newcolcount))
Range("C" & countstr).Value = tempval
newcolcount = newcolcount + 1
countcopies = 1
End If
countint = countint + 1
Loop Until tempval = ""
End Sub

Skeletal

  skeletal 20:46 21 Feb 05

OMG!!! I've just seen the result...the ultimate in wrecked formatting!!!

And that was putting two spaces after each line as well.

If you want to follow it up, you'll have to drop me a line. You wont stand a prayer of entering the code as it appears.

Skeletal

This thread is now locked and can not be replied to.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

Apple Watch 2 review | Apple Watch Series 2 review: New Apple Watch is faster, brighter, water-resit…