Tighter formula than this?

L

Lostguy

Hello!

Is there a shorter way to write this formula?

=UPPER(IF(G4="","",G4&", "&G5&" "&G6&IF(G6="","","."))&CHAR(10)&IF
(H4="","",H4&", "&H5&" "&H6&IF(H6="","","."))&CHAR(10)&IF
(I4="","",I4&", "&I5&" "&I6&IF(I6="","","."))&CHAR(10)&IF
(J4="","",J4&", "&J5&" "&J6&IF(J6="","","."))&CHAR(10)&IF
(K4="","",K4&", "&K5&" "&K6&IF(K6="","","."))&CHAR(10)&IF
(L4="","",L4&", "&L5&" "&L6&IF(L6="","","."))&CHAR(10)&IF
(M4="","",M4&", "&M5&" "&M6&IF(M6="","","."))&CHAR(10)&IF
(N4="","",N4&", "&N5&" "&N6&IF(N6="","","."))&CHAR(10)&IF
(O4="","",O4&", "&O5&" "&O6&IF(O6="","","."))&CHAR(10)&IF
(P4="","",P4&", "&P5&" "&P6&IF(P6="","",".")))


It repeats IF(G4="","",G4&", "&G5&" "&G6&IF(G6="","","."))&CHAR(10),
but it loops G to P. (G4 contains a last name, G5 is first name, G6 is
middle initial (if there is an inital, put a period after it)). It
makes a list:

SMITH, WILLIAM A. JR.
FDF, FDF
SMITH, WILL
JONES, BILL D.
SMITH, WILL E.
JONES, BILL F.
SMITH, WILL H.
JONES, BILL I.
SMITH, WILL J.
JONES, BILL K.

Just seems clumsy to me. I appreciate the help!

VR/
Lost
 
L

Lostguy

Hello!

If that formula was split out so that each part was in its own cell,
adding the extra rows adds an additional 1KB to the file size. From
what I have found, the less rows and columns in the sheet, the smaller
the file size.

If you can use less cells and whittle the formulas down to the fewest
characters, the file size drops more.

VR/

Lost
 
E

Engin Tarhan

Is the size of the file *that* important, or are you competing against
yourself to create the smallest size file to do a given task?

Good luck,
Engin
 
L

Lostguy

Engin,

My restraints are: smallest file size, most functionality, no VBA.

So, yes, every bit counts.

I saw that formula and was thinking maybe there was a formula that
looped through a range, like a FOR-NEXT loop that would make what I
have less bulkly, but I don't know of one.

Thanks!

VR/ Lost
 
P

Peter Beach

Hi,

If you want to put the list all into one cell I think you won't be able to.
If you are happy with a vertical range with each cell containing one of the
names you could try the following.

Select a range (say a1:a10) and enter the following formula:

=TRANSPOSE(UPPER(G4:J4&", "&G5:J5&IF(G6:J6="",""," "&G6:J6&".")))

It's an array formula so needs to be entered (and edited) with Shift / Ctrl
/ Enter.

Adjust the ranges as appropriate. For clarity I've taken out tests for the
name existing.

HTH

Peter Beach
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top