simple?

  • Thread starter Thread starter Me
  • Start date Start date
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
 
deleting rows does not work because formulas rely on the filtered list. once
cells are deleted all the formulas go bad.
 
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
 
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.
 
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!!
 
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)}
 
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
 
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
 
Hi Rob
one way:
=IF(ISERROR(the_formula),"",the_formula)
also entered as array formula
 
Back
Top