simple?

M

Me

It's amazed me that what I am trying to do is not much simpler:

A B
Jim Jones Jim Jones
Hank Aaron Hank Aaron
Tweety McFly Tweety McFly
Paddy O'Hara
Paddy O'Hara Constance McNair
Constance McNair

I simply want to what is in row A to be in row B minus the blanks. Hiding
rows is no good for me so filtering seems to not work. Unless someone knows
another way to use the advanced filter to accomplish this.

Thanks,
-Me
 
M

Me

deleting rows does not work because formulas rely on the filtered list. once
cells are deleted all the formulas go bad.
 
F

Frank Kabel

Hi
then enter the following array formula (entered with CTRL+SHIFT+ENTER)
in B1:
=INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW())
)
and copy this formula down
 
M

Me

I tried that.

{=INDEX(EMPLOYEES!$V$2:$V$3001,SMALL(IF(EMPLOYEES!$V$2:$V$3001<>"",ROW(EMPLO
YEES!$V$2:$V$3001)),ROW())
)}

That leaves the blanks there. Also, for some strange reason, it ignores the
2 top rows. I get the contents of EMPLOYEES!V4 in B2 (the first cell of
where I am putting the filtered list, B2:B101).

Any ideas???

Thanks for looking.
 
M

Me

I changed all the V2 to V1 and it works like a charm!!! Bless your soul,
bless your children's souls. If you're ever in Ft Lauderdale I will buy you
a tall cool one!!
 
F

Frank Kabel

Hi
first: are you sure the rows are really blank (e.g. they do not contain
some Spaces). Try checking a blank cell with
=V3=""
this should return TRUE if V3 is a blank cell

For the other question change the formula to
{=INDEX(EMPLOYEES!$V$2:$V$3001,SMALL(IF(EMPLOYEES!$V$2:$V$3001<>"",ROW(
EMPLOYEES!$V$2:$V$3001)),ROW()-1)-1)}
 
R

Ron Rosenfeld

It's amazed me that what I am trying to do is not much simpler:

A B
Jim Jones Jim Jones
Hank Aaron Hank Aaron
Tweety McFly Tweety McFly
Paddy O'Hara
Paddy O'Hara Constance McNair
Constance McNair

I simply want to what is in row A to be in row B minus the blanks. Hiding
rows is no good for me so filtering seems to not work. Unless someone knows
another way to use the advanced filter to accomplish this.

Thanks,
-Me
Click to expand...

Well you can use the Advanced Filter.

A
1 Name
2 ="<>"
3
6 Name
7 Jim Jones
8 Hank Aaron
9 Tweety McFly
10
11 Paddy O'Hara
12 Constance McNair
13
14 Jill Street

Advanced Filter
* Copy to another location

List Range A6:A14
Criteria Range A1:A2
Copy to: B6


--ron
 
M

Me

naw, she's working great! Only small problem is that the formula will leave
errors at the bottom of the range. (my range is 100 cells, a2:a101), I've
never had more than 100 active employees so I know it will not go beyond
that. Is there a way to get rid of the #NUM! errors that I am getting at the
bottom of the range??

Thanks again!!

-Rob
 
F

Frank Kabel

Hi Rob
one way:
=IF(ISERROR(the_formula),"",the_formula)
also entered as array formula
 
Top