Extracting specific rows

sbdk82 · #1 09-03-2014, 08:21 AM

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

HTML Code:

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

HTML Code:

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

Can you please help me?

gebobs · #2 09-03-2014, 08:32 AM

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

sbdk82 · #3 09-03-2014, 08:40 AM

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 · #4 09-03-2014, 09:46 AM

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 · #5 09-07-2014, 10:24 PM

Or with code.

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge to create specific number of table rows	flackend	Mail Merge	4	12-01-2023 02:49 PM
Find specific rows then copy and paste to new doc	konopca	Word VBA	5	02-20-2014 02:34 PM
Deleting rows with specific criteria	joflow21	Excel	9	11-22-2013 12:10 PM
Adding columns in specific rows only	mhays	Excel	5	01-17-2012 09:13 AM
Need help extracting specific text from one doument to another with macro/VBA.	zsmithku	Word	1	04-15-2011 03:46 PM