Extracting specific rows

[sbdk82]

I have a big excel file with 5000 rows and 20 columns. I want to extract specific rows and save it in another worksheet. Following is a simple example of what output should be

Suppose the excel contains following data

1	abc
1	cde
1	efg
2	hij
2	lkp
2	jkl
3	opl
4	lkp
4	pol
5	lkm
6	plm
6	dfb

I want to extract the following rows

1	abc
2	hij
3	opl
4	lkp
5	lkm
6	plm

Can you please help me?

[gebobs]

Are you trying to extract the first row for each value in column A?

[sbdk82]

Yes !! Basically I want to have the unique ids in one column and any row corresponding to one id in the next column. So first row of each value in Column A is also one answer.

[gebobs]

Hoo boy...you lost me there. But anyway, let's see if this works...

Insert another column after B. In the first row, enter 1. In the second row:

=IF(A2=A1,C1+1,1)

Now you have a unique key for each line consisting of Columns A and C. All you have to do now is filter Column C to only show those records with a value of 1.

[jolivanes]

Or with code.

Sub Maybe()
    Dim vMatch As Variant, lngRow As Long, lr As Long
    Application.ScreenUpdating = False
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For lngRow = lr To 2 Step -1
        vMatch = Application.Match(Cells(lngRow, "A"), Cells(1, "A").Resize(lngRow - 1), 0)
        If IsNumeric(vMatch) Then Cells(lngRow, "A").EntireRow.Hidden = True
    Next lngRow
    Range("A2:A" & lr).Resize(, 2).SpecialCells(12).Copy Sheets("Sheet3").Range("A1") '<---Copies from Col A & B only
    Range("A2:A" & lr).EntireRow.Hidden = False
    Application.ScreenUpdating = True
End Sub