Is this possible??....

[Dan B]

I'm pulling in names and addresses from a seperate workbook. I have 2
address lines. If the second address line is blank in my source data, is
there a way to move up the city state and zip row to remove the blank line
in the address. For example:
John Doe
1234 Anywhere St

New York, NY 90210

Can I make New York move up only when row above is blank. I think I might
be asking for too much here. Not sure if this is possible.
Thanks,
Dan

 

[Anders S]

Dan,

If, by "pulling", you mean linking with worksheets formulas, you can try

=[Book1.xls]Sheet1!B1
=[Book1.xls]Sheet1!B2
=IF([Book1.xls]Sheet1!B3<>"",[Book1.xls]Sheet1!B3,[Book1.xls]Sheet1!B4)
=IF([Book1.xls]Sheet1!B4<>"",[Book1.xls]Sheet1!B4,"")

Each record in the source and destination tables will still occupy four rows,
but there will be no space between the third and fourth row if #3 is empty.

HTH
Anders Silvén

With source data like:

Row
1 John Doe
2 Apt. 2A
3 1234 Anywhere St
4 New York, NY 90210
5 John Doe
6 1234 Anywhere St
7 New York, NY 90210
8
9 John Doe
10 Apt. 2B
11 1234 Anywhere St
12 New York, NY 90210
13 John Doe
14 1234 Anywhere St
15 New York, NY 90210
16

You will get:

Row
1 John Doe
2 Apt. 2A
3 1234 Anywhere St
4 New York, NY 90210
5 John Doe
6 1234 Anywhere St
7 New York, NY 90210
8
9 John Doe
10 Apt. 2B
11 1234 Anywhere St
12 New York, NY 90210
13 John Doe
14 1234 Anywhere St
15 New York, NY 90210
16

 

[Dave Peterson]

Before you remove the blank line (in fact, before you reformat your data), I
think you'll be better off later if you put each name/address/city/state on one
row. (each field in a different column, though).

It'll make life easier if you ever decide to do mailmerge.

http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.

Or even just sort/filter your data.

If you think you might like this, post back with a few more details. Is every
name/address 4 rows?

Depending on how you answer, you'll get different solutions.

But if all you want to do is get rid of the blank rows (do it against a
copy--just in case), you can

select that column
Edit|goto|special
click Blanks
Edit|delete
(entire row or shift cells up depending on what you want to do with B:IV)

But if you do this, then "flattening" your file may get more difficult.

 

[dizzykid]

Starting with the formula previously posted by Mr Grove, and assuming these
columns: 1 num, 2 name, 3 add1, 4 add2, 5 city, 6 state, 7 zip:

These formulas placed in four consecutive rows in the same column will
produce a "mailing label" of sorts.

=IF(ISNUMBER(MATCH($I$16,Sheet1!$A$2:$A$710,0)),VLOOKUP($I$16,Sheet1!$A$2:$G
$710,2,0),"")

=IF(ISNUMBER(MATCH($I$16,Sheet1!$A$2:$A$710,0)),VLOOKUP($I$16,Sheet1!$A$2:$G
$710,3,0),"")

=IF(ISNUMBER(MATCH($I$16,Sheet1!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,Sheet1
!$A$2:$G$710,4,0))="",VLOOKUP($I$16,Sheet1!$A$2:$G$710,5,0)&",
"&VLOOKUP($I$16,Sheet1!$A$2:$G$710,6,0)&"
"&VLOOKUP($I$16,Sheet1!$A$2:$G$710,7,0),VLOOKUP($I$16,Sheet1!$A$2:$G$710,4,0
)),"")

=IF(ISNUMBER(MATCH($I$16,Sheet1!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,Sheet1
!$A$2:$G$710,4,0))="","",VLOOKUP($I$16,Sheet1!$A$2:$G$710,5,0)&",
"&VLOOKUP($I$16,Sheet1!$A$2:$G$710,6,0)&"
"&VLOOKUP($I$16,Sheet1!$A$2:$G$710,7,0)),"")

Hopefully that's what you're looking for.
Chris

 

[Dan B]

DizzyKid,
Thanks for your reply.
I tried your suggestions and it duplicated the city state and zip on lines
three and four. It also put a square box, instead of a space between the
city, state and zip.
Here is my original formula for lines 3 and 4:
=IF(ISNA(VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,4,FALSE)),0,VLOOKUP($I$16,'C:\Work\[customer
s from caselle.xls]Sheet1'!$A$2:$G$710,4,FALSE))


=IF(ISNUMBER(MATCH($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,5,0)&", "&
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,6,0)&" "&
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,7,0),"")

Here are your formulas for lines 3 and 4 as I have them entered into the
spreadsheet:
=IF(ISNUMBER(MATCH($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0))="",VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,5,0)&",
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,6,0)&"
"&VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,7,0),VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0
)),"")


=IF(ISNUMBER(MATCH($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0))="",VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,5,0)&",
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,6,0)&"
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,7,0)),"")

Did I do something wrong?

Thanks Again,
Dan

 

[dizzykid]

Hi Dan,

There is a tiny problem with the fourth formula. Check out the formula
below, and note the apparent duplication of the null string argument. Both
of those nulls are required, and your version has just one.

=IF(ISNUMBER(MATCH($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),
IF(TRIM(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0))=
"","",
VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,5,0)&", "
&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,6,0)&" "
&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,7,0)),"")

In case it might help, here's a pseudocode illustration of the logic used:
('result' represents the bottom line of the 'mailing label')
IF I16 exists in database THEN
IF addr2 is blank THEN
result= blank (because
city/prov/postal are moving up a line)
ELSE (addr2 is not blank)
result= city+[comma]+[space]+province+[space]+postal
END IF
ELSE
result= blank
END IF

The square box could be a result of copying and pasting. Try deleting and
retyping any strings that are producing a box. There should only be a space
or a comma and a space between those quotes. I'm thinking some kind of
carriage return character got in there somehow and is being displayed as a
box.

Note- the post offices in both Canada and the US tend to prefer this format
(they don't insist on it though):
city+[space]+prov+[space][space]postal
Winnipeg MB R2Y 1P3
Knoxville TN 54321

Good luck,
Chris