Filling in folder numbers in an MS Excel container list.

Here at the Kheel Center we have quite a few legacy container lists in Excel spreadsheets that list the folder titles for each box, without giving the folder numbers.

A	B
Box	Folder Title
1	Folder 1
1	Folder 2
1	Folder 3
2	Folder 4
2	Folder 5
3	Folder 6
3	Folder 7

Before converting the container list to EAD I wanted to insert the folder numbers. I was able to do so using a fairly simple If-Then-Else statement. First insert a new column between the Box and Folder Title columns.

A	B	C
Box	Folder	Folder Title
1		Folder 1
1		Folder 2
1		Folder 3
2		Folder 4
2		Folder 5
3		Folder 6
3		Folder 7

In the first cell of the Folder column, cell B2, enter the following formula:

=IF(A2-A1=1,1,B1+1)

What this does is subtract A1 from B2, which will give you either a 1 or a 0. If the value is 1 then the current row is the first folder of the box. If the value is 0 then the current folder is the next folder in the box and should have a folder number that is one greater than the one above it. When you have the formula done double-click on the little handle at the lower right corner of the cell. This is the fill-down function and will run the formula down the entire column.

Note: this formula will give an error for all the folders in the first box: you are trying to subtract text (“Box”) from a number in the first cell.

A	B	C
Box	Folder	Folder Title
1	#VALUE!	Folder 1
1	#VALUE!	Folder 2
1	#VALUE!	Folder 3
2	1	Folder 4
2	2	Folder 5
3	1	Folder 6
3	2	Folder 7

To correct the error simply temporarily replace the “Box” in A1 with a zero.

A	B	C
0	Folder	Folder Title
1	1	Folder 1
1	2	Folder 2
1	3	Folder 3
2	1	Folder 4
2	2	Folder 5
3	1	Folder 6
3	2	Folder 7

Select column B and copy, then Paste Special=>Values right over it. This will convert the formulas in column B to the values shown. After that you can change cell A1 back to “Box”.

You now have a container list with both box and folder numbers.