Capture lines with entries?

[Wind54Surfer]

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

 

[Jacob Skaria]

Why dont you try the filter option on the same date. (menu
Data>Filter>Autofilter)

If this post helps click Yes

 

[Gord Dibben]

Autofilter on column A for non-blanks

Then F5>Special>Visible cells only>OK

Copy then paste to another part of the sheet.


Gord Dibben MS Excel MVP

 

[Wind54Surfer]

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.

 

[Wind54Surfer]

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

 

[Jacob Skaria]

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,ROW($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

 

[Wind54Surfer]

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]

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]

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(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW(A1))))

If this post helps click Yes

 

[Wind54Surfer]

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]

Is not working, as you can see below I need to start counting from A5 (after
the Version line)

====================
A----B
32 62716
54073
2 ProductName
Version 07/21/2009
CM/AW to 4
1 CM/AW to 4 Colour
CM/AW to 5
CM/AW to 5 Colour
123 CM/AW to 6
CM/AW to 6 Colour
CM/AW to 7
====================

Somehow with your first formula it worked but it will only show me the row
with the "1" (row 6) that was intentional.

now I get:

====================
32 61634.826
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
====================

I tried different combinations of A5 and $A$5 no luck

What am I doing wrong?

Mac Office 2008
Document to go for iPhone
(by the way I am doing all the testing in my notebook, haven't been able to
sync (no wi-fi) where I am, and I noticed that all the parts of your formula
exist in the iPhone (111 formulas) except "SMALL", I am not sure what will
happen)

Thanks again!

 

[Jacob Skaria]

Try the below array formula..

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

If this post helps click Yes

 

[Wind54Surfer]

Bingo!, except finally I was able to sync with my iPhone, and when I open the
worksheet it warns me that the function is not supported (locks the entire
file-read only)
I believe "SMALL" is the problem since is not on the formulas included.

Is there another way without SMALL?

Thanks a lot for your patience!

 

[Wind54Surfer]

I am searching, now that I know roughly what I am searching for and found:

MATCH

is there any way to use this somehow on that formula?

Thanks again for your patience!

 

[Jacob Skaria]

In Sheet2; we can use the below formulas and a helper column to get the
results. All the below formulas are normal formulas and are not array entered.

In Sheet2 A1
=MATCH(TRUE,INDEX(Sheet1!$A$5:$A$1000<>0,),)+4

In Sheet2 A2
=IF(OR(A1="",ISNA(MATCH(TRUE,INDEX(INDIRECT("Sheet1!A" & (A1+1) &
":A1000")<>0,),)+A1)),"",MATCH(TRUE,INDEX(INDIRECT("Sheet1!A" & (A1+1) &
":A1000")<>0,),)+A1)
and copy down the formula as required for Column A

In Sheet2 B1 enter the below formula. Copy/drag cell B1 to C1 and copy down
as reqired...
=INDEX(Sheet1!$A:$B,$A1,COLUMN(A1))

In case you have more columns in Sheet1 whch are to be displayed in Sheet2
modify the data range Sheet1!$A:$B in cell B1 formula and copy across/down as
required. Col A is a helper column which can be hidden..

If this post helps click Yes

 

[Wind54Surfer]

You have no idea how much I appreciate your help, you go way out of your way!

It works great on my notebook, but on Documents to Go I found out COLUMN
should be COLUMNS (I changed it), and it does not support INDIRECT, bummer!

I wrote what it supports under "Lookup and Reference"
================================
CHOOSE (index_num, value1, value2,….)
COLUMNS (array)
HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)
INDEX (reference, row_num, column_num, area_num)
MATCH (lookup_value, lookup_array, match_type)
ROWS (array)
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
================================

Thanks a lot for your patience, again!

 

[Jacob Skaria]

Try

Sheet2 A1 formula remains the same
=MATCH(TRUE,INDEX(Sheet1!$A$5:$A$1000<>0,),)+4

Sheet2 A2..Copy down as required....Handling the errors make this lenghty.
=IF(OR(A1="",ISNA(MATCH(TRUE,INDEX(INDEX(Sheet1!A:A,A1+1):Sheet1!A1000<>0,),))),"",MATCH(TRUE,INDEX(INDEX(Sheet1!A:A,A1+1):Sheet1!A1000<>0,),)+A1)

Sheet2 B1 and down.....If more columns change the array size and copy
across...
=IF($A1<>"",INDEX(Sheet1!$A:$B,$A1,COLUMNS($A1:A1)),"")

If this post helps click Yes

 

[Wind54Surfer]

Same problem with A1, A2, A3,... it locks the file-read because of
UNSUPPORTED FUNCTIONS

The rest is OK

Sorry to drive you crazy!

Thanks again.

 

[Jacob Skaria]

Can you identify which function is not supported? When using Mac do you
convert the commas to ;

If this post helps click Yes

 

[Wind54Surfer]

I would have never guess, I tested the file in Windows Excel, and because is
2003 saved as XLS and the Functions were OK, except everything in Sheet2
shows #VALUE!

Then I saved in Mac like XLS and same thing Functions OK, but shows #VALUE!

Does this changes anything?

Thanks again!