Capture lines with entries?
 

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

Why dont you try the filter option on the same date. (menu
DataFilterAutofilter)

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

Autofilter on column A for non-blanks

Then F5SpecialVisible cells onlyOK

Copy then paste to another part of the sheet.


Gord Dibben MS Excel MVP

On Fri, 2 Oct 2009 19:35:01 -0700, Wind54Surfer
wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

Unfortunately I am using it in a handheld with no data filters, it pretty
much have to rely on formulas, I guess, I am not well versed on Excel, only
the basics.

Thanks for your help anyway.

"Jacob Skaria" wrote:

Why dont you try the filter option on the same date. (menu
DataFilterAutofilter)

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

This works great on any full Excel program, the handheld device I use works
with Documents to Go, very skimpy Excel-like program, on an iPhone

Thanks for your help

"Gord Dibben" wrote:

Autofilter on column A for non-blanks

Then F5SpecialVisible cells onlyOK

Copy then paste to another part of the sheet.


Gord Dibben MS Excel MVP

On Fri, 2 Oct 2009 19:35:01 -0700, Wind54Surfer
wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

OK.
With your data in Sheet1 A/B/C starting in Row1; try the below formula in
Sheet2 cell A1 and copy to B1,C1. Once done copy the formula down as
required. this should display the entries in Sheet1 having a 1 in Column A

=IF(COUNTIF(Sheet1!$A$1:$A$1000,1)<ROW(),"",INDEX( Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000=1,R OW($A$1:$A$1000)),ROW())))

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Unfortunately I am using it in a handheld with no data filters, it pretty
much have to rely on formulas, I guess, I am not well versed on Excel, only
the basics.

Thanks for your help anyway.

"Jacob Skaria" wrote:

Why dont you try the filter option on the same date. (menu
DataFilterAutofilter)

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

I am trying really hard to make this work on a sample simple worksheet, but I
have 2 problems:

Pressing CTRL+SHIFT+ENTER only moves the cursor up, is it OK to enter the
{} by hand?

Also what do you mean by "and copy to B1,C", the formula has to be copied?
Because then you said copy the formula down??

I am sure is me not understanding.

Thanks for your patience

"Jacob Skaria" wrote:

OK.
With your data in Sheet1 A/B/C starting in Row1; try the below formula in
Sheet2 cell A1 and copy to B1,C1. Once done copy the formula down as
required. this should display the entries in Sheet1 having a 1 in Column A

=IF(COUNTIF(Sheet1!$A$1:$A$1000,1)<ROW(),"",INDEX( Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000=1,R OW($A$1:$A$1000)),ROW())))

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Unfortunately I am using it in a handheld with no data filters, it pretty
much have to rely on formulas, I guess, I am not well versed on Excel, only
the basics.

Thanks for your help anyway.

"Jacob Skaria" wrote:

Why dont you try the filter option on the same date. (menu
DataFilterAutofilter)

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

Try the below

=IF(COUNTIF(Sheet1!$A$1:$A$1000,1)<ROW(),"",INDEX( Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<"" ,ROW($A$1:$A$1000)),ROW())))

Would really like to know which product/version you are using..

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Never mind, it works like a charm, I was a little rusty.

Now I will throw a curb, I made the first sample too simple, because the
number "1" could be any number, like "1023" or "96", or "151"

Any ideas?

I feel like I am so close, thanks to you of course.

Thanks again!


"Wind54Surfer" wrote:

I am trying really hard to make this work on a sample simple worksheet, but I
have 2 problems:

Pressing CTRL+SHIFT+ENTER only moves the cursor up, is it OK to enter the
{} by hand?

Also what do you mean by "and copy to B1,C", the formula has to be copied?
Because then you said copy the formula down??

I am sure is me not understanding.

Thanks for your patience

"Jacob Skaria" wrote:

OK.
With your data in Sheet1 A/B/C starting in Row1; try the below formula in
Sheet2 cell A1 and copy to B1,C1. Once done copy the formula down as
required. this should display the entries in Sheet1 having a 1 in Column A

=IF(COUNTIF(Sheet1!$A$1:$A$1000,1)<ROW(),"",INDEX( Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000=1,R OW($A$1:$A$1000)),ROW())))

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Unfortunately I am using it in a handheld with no data filters, it pretty
much have to rely on formulas, I guess, I am not well versed on Excel, only
the basics.

Thanks for your help anyway.

"Jacob Skaria" wrote:

Why dont you try the filter option on the same date. (menu
DataFilterAutofilter)

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

Correction:

With the below you can start the formula in any row in Sheet2; (again array
entered)

=IF(COUNTA(Sheet1!$A$1:$A$1000)<ROW(A1),"",INDEX(S heet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<"", ROW($A$1:$A$1000)),ROW(A1))))

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Never mind, it works like a charm, I was a little rusty.

Now I will throw a curb, I made the first sample too simple, because the
number "1" could be any number, like "1023" or "96", or "151"

Any ideas?

I feel like I am so close, thanks to you of course.

Thanks again!


"Wind54Surfer" wrote:

I am trying really hard to make this work on a sample simple worksheet, but I
have 2 problems:

Pressing CTRL+SHIFT+ENTER only moves the cursor up, is it OK to enter the
{} by hand?

Also what do you mean by "and copy to B1,C", the formula has to be copied?
Because then you said copy the formula down??

I am sure is me not understanding.

Thanks for your patience

"Jacob Skaria" wrote:

OK.
With your data in Sheet1 A/B/C starting in Row1; try the below formula in
Sheet2 cell A1 and copy to B1,C1. Once done copy the formula down as
required. this should display the entries in Sheet1 having a 1 in Column A

=IF(COUNTIF(Sheet1!$A$1:$A$1000,1)<ROW(),"",INDEX( Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000=1,R OW($A$1:$A$1000)),ROW())))

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Unfortunately I am using it in a handheld with no data filters, it pretty
much have to rely on formulas, I guess, I am not well versed on Excel, only
the basics.

Thanks for your help anyway.

"Jacob Skaria" wrote:

Why dont you try the filter option on the same date. (menu
DataFilterAutofilter)

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio

Capture lines with entries?
 

Never mind, it works like a charm, I was a little rusty.

Now I will throw a curb, I made the first sample too simple, because the
number "1" could be any number, like "1023" or "96", or "151"

Any ideas?

I feel like I am so close, thanks to you of course.

Thanks again!


"Wind54Surfer" wrote:

I am trying really hard to make this work on a sample simple worksheet, but I
have 2 problems:

Pressing CTRL+SHIFT+ENTER only moves the cursor up, is it OK to enter the
{} by hand?

Also what do you mean by "and copy to B1,C", the formula has to be copied?
Because then you said copy the formula down??

I am sure is me not understanding.

Thanks for your patience

"Jacob Skaria" wrote:

OK.
With your data in Sheet1 A/B/C starting in Row1; try the below formula in
Sheet2 cell A1 and copy to B1,C1. Once done copy the formula down as
required. this should display the entries in Sheet1 having a 1 in Column A

=IF(COUNTIF(Sheet1!$A$1:$A$1000,1)<ROW(),"",INDEX( Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000=1,R OW($A$1:$A$1000)),ROW())))

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Unfortunately I am using it in a handheld with no data filters, it pretty
much have to rely on formulas, I guess, I am not well versed on Excel, only
the basics.

Thanks for your help anyway.

"Jacob Skaria" wrote:

Why dont you try the filter option on the same date. (menu
DataFilterAutofilter)

If this post helps click Yes
---------------
Jacob Skaria


"Wind54Surfer" wrote:

Hi all,

I don't even know how to search for this, I have a long (almost 500 items)
price list like:

A-----------B----------------------------------C
1 CM 4 $400
2 1 CM 5 $500
3 CM 6 $600
4 1 CM 7 $700
5 CM 8 $800

I need a way to capture on another part of the sheet the
lines that have entries in the A column, like:

1 CM 5 $500
1 CM 7 $700

so I can tell if all the items have been entered properly, since the list is
so long
and I am using a handheld device, making it less clear and prone to
omissions and/or errors.

I appreciate any help.

Thanks in advance,
Emilio