ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Help; data filter (https://www.excelbanter.com/excel-discussion-misc-queries/117553-help%3B-data-filter.html)

Ahmad
Help; data filter
 
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

VBA Noob
Help; data filter
 
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Ahmad
Help; data filter
 
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

VBA Noob
Help; data filter
 
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Ahmad
Help; data filter
 
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

VBA Noob
Help; data filter
 
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Ahmad
Help; data filter
 
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

VBA Noob
Help; data filter
 
Hi,

This should work also and isn't limited with the choose function

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Ahmad
Help; data filter
 
Dear VBA Noob
the last function works clearly, can you explain the numbers in the
function please gave me idea about this function because i am not
expert in these functions, and how i can learn this function, becuase
my data need these function, also i send you link have question like
this,
http://groups.google.com.tr/group/mi...312e157e0a20ac

VBA Noob yazdi:
Hi,

This should work also and isn't limited with the choose function

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

VBA Noob
Help; data filter
 
Hi,

Try using excel help to learn more about Offset and Indirect functions

Now if you enter =(ROW(A1)-1)*12+2 in any cell and drag down it will
return 2,14,26 etc. To break it out Row(A1) = 1 and when you drag down
Row(A2) = 2 etc.

So Row 1 would read (1-1)*12+2 which equals 2 e.g 0*12=0+2. The 12 is
needed for your 12 x 3 matrix as the indirect function looks up
A2,A14,A26 etc.

The column(A:A)-1 always returns 0

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

Hope that helps

VBA Noob


Ahmad wrote:
Dear VBA Noob
the last function works clearly, can you explain the numbers in the
function please gave me idea about this function because i am not
expert in these functions, and how i can learn this function, becuase
my data need these function, also i send you link have question like
this,
http://groups.google.com.tr/group/mi...312e157e0a20ac

VBA Noob yazdi:
Hi,

This should work also and isn't limited with the choose function

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Ahmad
Help; data filter
 
Hi
Again thank you for help, now i have understand this function

Best regards

Ahmad
VBA Noob yazdi:
Hi,

Try using excel help to learn more about Offset and Indirect functions

Now if you enter =(ROW(A1)-1)*12+2 in any cell and drag down it will
return 2,14,26 etc. To break it out Row(A1) = 1 and when you drag down
Row(A2) = 2 etc.

So Row 1 would read (1-1)*12+2 which equals 2 e.g 0*12=0+2. The 12 is
needed for your 12 x 3 matrix as the indirect function looks up
A2,A14,A26 etc.

The column(A:A)-1 always returns 0

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

Hope that helps

VBA Noob


Ahmad wrote:
Dear VBA Noob
the last function works clearly, can you explain the numbers in the
function please gave me idea about this function because i am not
expert in these functions, and how i can learn this function, becuase
my data need these function, also i send you link have question like
this,
http://groups.google.com.tr/group/mi...312e157e0a20ac

VBA Noob yazdi:
Hi,

This should work also and isn't limited with the choose function

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5


All times are GMT +1. The time now is 03:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com