Help collating a lot of files

[step into my office]

Hello, all

I'm attempting to file about 1,000 employee forms in a new filing system. Each employee has one form. I want to divide the 1,000 employee forms according to the first two letters of their last name. I want to put "at most" twenty forms in each manila folder. I also want to keep each manila folder neatly segregated ("Aa-Ab"; "Ac-Ah"; "Ai-Ar"; etc.).

Finally, I want to do this via Excel!

I've already taken a list of employees:

Frankenstein,Doctor
Franklin,Benjamin
Franklin,Josephine
Franklin,Marianne
Franklin,Nancy
Jefferson,Thomas
Jefferson,Xavier
Jeffrey,Bill
Roosevelt,Franklin
Roosevelt,Theodore
Washington,George
Washington,Harry
Washington,Stanley
Washington,Yvette
Watts,Stacey​

and shortened their names to the first two letters of their last name, and created a count of how many employees fall into each "two-letter" category:

Fr 5
Je 3
Ro 2
Wa 5​

This is the data I'm thinking of working with.

I want a magic formula that I can insert down the column, add up the numbers, and flag each time it gets to "at most twenty" without going over and without combing different letters.

in short, I'd like a formula to get something like this:

"Name" | "Count" | "Sum, At most 20"
Aa 1
Ac 10 11
Ad 16
Af 2 18
Ag 10
Ar 4
As 2 16
Ba 1
Be 2
Bi 17 20​

in other words,

Folder Aa-Ac containing 11 files
Folder Ad-Af containing 18 files
Folder Ar-As containing 16 files
Folder Ba-Bi containing 20 files​

Can this be done? Would I have to somehow convert the names into numbers?

 

[step into my office]

I'm working with Excel 2007, btw.

 

[shawnhet]

As a quick way of doing this, assuming name and count are in columns A & B, post the following three expressions in columns C,D, E & F respectively in the *second* row with data and copy down to the bottom of your range - my version assumes the second row with data is row 2 (the first row of data is row 1).

=IF(C1+B2<21,C1+B2,B2)
=IF(C2=B2,A2,D1)
=IF(C3=B3,A2,D1)
=IF(C3=B3,CONCATENATE(D2,"-",E2),"")

This should give you your total less than 20 is column C and the label title in column F. you can then use those columns in a mail merge to print your labels(after copying and pasting as values, then sorting by column F).

Here is my output for comparison


Aa 1 1 Aa *** Aa typed in by me to column D in first line.
Ac 10 11 Aa Ac Aa-Ac
Ad 16 16 Ad Aa
Af 2 18 Ad Af Ad-Af
Ag 10 10 Ag Ad
Ar 4 14 Ag Ag
As 2 16 Ag Ag
Ba 1 17 Ag Ag
Be 2 19 Ag Be Ag-Be
Bi 17 17 Bi Bi Bi-Bi
Bj 1 1 Bj Bj Bj-Bj

Cheers,

 

[step into my office]

Fantastic! Thank you so much.

Is there any way to prevent it from creating categories like "Ag-Be"? I'd like to keep the letters of the alphabet separate. Of course, I can do this manually, but...just to dare to dream it can be achieved through a formula. :D

 

[shawnhet]

You're welcome!

I think this should work, put the following in C2 per my framework (instead of =IF(C1+B2<21,C1+B2,B2) )


=IF(AND(C1+B2<21,LEFT(A1,1)=LEFT(A2,1)),C1+B2,B2)

and copy down.

Cheers,