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

[spill-the-beans]

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

Columns A, B, C, D, E, F	G - index number	Columns H onwards
data	1	data
data	2	data
data	3	data

<tbody>
</tbody>

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

Columns A, B, C, D, E	F -start index	G -end index
information	25	30
information	47	78
information	90	94

<tbody>
</tbody>

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

Columns A, B, C, D, E, F	G - index number	Columns H onwards
unneeded data	24	unneeded data
needed data	25	needed data
needed data	26	needed data
needed data	27	needed data
needed data	28	needed data
needed data	29	needed data
needed data	30	needed data
unneeded data	31	unneeded data

<tbody>
</tbody>

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?

 

[juliano]

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!

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

Columns A, B, C, D, E, F	G - index number	Columns H onwards
data	1	data
data	2	data
data	3	data

<tbody>
</tbody>

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

Columns A, B, C, D, E	F -start index	G -end index
information	25	30
information	47	78
information	90	94

<tbody>
</tbody>

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

Columns A, B, C, D, E, F	G - index number	Columns H onwards
unneeded data	24	unneeded data
needed data	25	needed data
needed data	26	needed data
needed data	27	needed data
needed data	28	needed data
needed data	29	needed data
needed data	30	needed data
unneeded data	31	unneeded data

<tbody>
</tbody>

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?

 

[MickG]

Try this:-
The code will delete the rows you don't want in sheet "Data"

[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

 

[spill-the-beans]

Thank you Mick, that worked perfectly!

 

[MickG]

Your welcome
Regrds Mick