Only keep rows that have a range of specified values in a particular column

S

spill-the-beans

Board Regular
Joined
Feb 7, 2013
Messages
52
Hi all :)

I have a dataset with 34 columns and ~5000 rows. In column G there is an index number. The index number goes down the column, increasing by 1 each time, eg in G2 the index is 1, in G3 the index is 2, in G4 the index is 3. . .

Data worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns A, B, C, D, E, F[/TD]
[TD]G - index number[/TD]
[TD]Columns H onwards[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]1[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]2[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]3[/TD]
[TD]data[/TD]
[/TR]
</tbody>[/TABLE]


In another worksheet, I have a list of index numbers that are needed. There is a start index number in column F and an end index number in column G; all the index numbers including the start/end and between them are needed.

Index worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns A, B, C, D, E[/TD]
[TD]F -start index[/TD]
[TD]G -end index[/TD]
[/TR]
[TR]
[TD]information[/TD]
[TD]25[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]information[/TD]
[TD]47[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]information[/TD]
[TD]90[/TD]
[TD]94[/TD]
[/TR]
</tbody>[/TABLE]


For example, if the start index was 25 and the end index was 30 in the index worksheet, I would need the all the data in every column in the data worksheet for the rows that have 25, 26, 27, 28, 29 or 30 in column G.

Information needed in the data worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns A, B, C, D, E, F[/TD]
[TD]G - index number[/TD]
[TD]Columns H onwards[/TD]
[/TR]
[TR]
[TD]unneeded data[/TD]
[TD]24[/TD]
[TD]unneeded data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]25[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]26[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]27[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]28[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]29[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]30[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]unneeded data[/TD]
[TD]31[/TD]
[TD]unneeded data[/TD]
[/TR]
</tbody>[/TABLE]


Does anyone know how to either cut out the unneeded data or print a new worksheet with only the needed data? At the moment I've been going through manually deleting the rows outside the needed index numbers, but I was wondering if there is a way less prone to human error & stress?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Hi,

try use offset, like this:

=SUM(OFFSET(Data!A1;F1-1;0;G1-F1+1;1))

{ offset will return a range, you have to use a function with that range, like sum (in example) or count }

Regards!


spill-the-beans said:
Hi all :)

I have a dataset with 34 columns and ~5000 rows. In column G there is an index number. The index number goes down the column, increasing by 1 each time, eg in G2 the index is 1, in G3 the index is 2, in G4 the index is 3. . .

Data worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns A, B, C, D, E, F[/TD]
[TD]G - index number[/TD]
[TD]Columns H onwards[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]1[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]2[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]3[/TD]
[TD]data[/TD]
[/TR]
</tbody>[/TABLE]


In another worksheet, I have a list of index numbers that are needed. There is a start index number in column F and an end index number in column G; all the index numbers including the start/end and between them are needed.

Index worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns A, B, C, D, E[/TD]
[TD]F -start index[/TD]
[TD]G -end index[/TD]
[/TR]
[TR]
[TD]information[/TD]
[TD]25[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]information[/TD]
[TD]47[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]information[/TD]
[TD]90[/TD]
[TD]94[/TD]
[/TR]
</tbody>[/TABLE]


For example, if the start index was 25 and the end index was 30 in the index worksheet, I would need the all the data in every column in the data worksheet for the rows that have 25, 26, 27, 28, 29 or 30 in column G.

Information needed in the data worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns A, B, C, D, E, F[/TD]
[TD]G - index number[/TD]
[TD]Columns H onwards[/TD]
[/TR]
[TR]
[TD]unneeded data[/TD]
[TD]24[/TD]
[TD]unneeded data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]25[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]26[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]27[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]28[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]29[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]needed data[/TD]
[TD]30[/TD]
[TD]needed data[/TD]
[/TR]
[TR]
[TD]unneeded data[/TD]
[TD]31[/TD]
[TD]unneeded data[/TD]
[/TR]
</tbody>[/TABLE]


Does anyone know how to either cut out the unneeded data or print a new worksheet with only the needed data? At the moment I've been going through manually deleting the rows outside the needed index numbers, but I was wondering if there is a way less prone to human error & stress?
Click to expand...
 
Upvote 0
Try this:-
The code will delete the rows you don't want in sheet "Data"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG08Feb47
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic     [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] nRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Num     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Index")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("F2"), .Range("F" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Num = Dn To Dn.Offset(, 1)
        Dic(Num) = Empty
    [COLOR="Navy"]Next[/COLOR] Num
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Data")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("G2"), .Range("G" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Index and match function spanning multiple columns and rows
Replies
2
Views
289
L8NightKebab
L
O
  • Solved
What Is Best Practice To Facilitate Column Layout Change In A Project?
Replies
2
Views
203
ozex
O
Guybrush Threepwood
  • Question Question
Using Filter for Criteria in Multiple Columns and a Specific Row Range?
Replies
0
Views
409
Guybrush Threepwood
Guybrush Threepwood
M
  • Solved
run formula based on cell value to the last row
Replies
5
Views
340
MMM_84
M
S
  • Question Question
Multiple columns lookup
Replies
0
Views
241
Sipirili2504
S

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top