Tighter formula than this?

  • Thread starter Thread starter Lostguy
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
Back
Top