Convert Calendar to List of Occurrences

[Shawnaby]

I received an old attendance tracker spreadsheet, in which occurrences were entered in as calendar entries. On the left side is employee names, across the top are days 1-31, and in the intersections codes like T, for tardy, U, for unexcused absence etc. are entered. I have a years worth of these spreadsheets for 40 employees that I need to turn into a list of names and occurrences for processing. I am stumped on the easiest way to do this. Any suggestions?

 

[iliace]

So you have 12 spreadsheets, one for each month?

If that's the case, I would suggest you copy and paste them into a single workbook. You may want to create a separate column to differentiate the months. Then just use SUMIF. Here is an example:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	Name	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total T	Total U
2	John Smith	Jan	*	T	*	*	*	U	*	*	*	*	T	*	*	*	U	U	*	*	*	*	*	*	*	*	*	U	*	T	*	*	*	3	4
3	Jane Doe	Jan	*	*	*	U	*	*	T	*	T	*	*	*	*	*	*	*	*	*	*	U	*	*	*	*	*	T	*	*	*	*	*	3	2
4	Robert Swift	Jan	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	U	*	*	*	*	U	*	*	*	*	*	U	*	*	*	*	0	3
5	John Smith	Feb	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	T	*	*	*	*	*	*	*	*	*	*	T	*	U	*	U	2	2
6	Jane Doe	Feb	U	*	*	*	*	*	*	*	U	U	*	T	*	*	*	*	U	*	*	*	U	*	T	U	*	*	*	*	*	*	*	2	6
7	Robert Swift	Feb	*	*	*	*	T	*	T	*	*	*	*	U	*	*	*	*	*	T	*	*	*	*	*	U	*	*	*	*	*	*	*	3	2
8	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
9	Totals	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
10	John Smith	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	5	6
11	Jane Doe	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	5	8
12	Robert Swift	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	3	5
Sheet1

 

[Shawnaby]

That is good, but not exactly what I am looking for. I would prefer to have a list of occurrences such as:

John Smith 01/02 T
John Smith 01/06 U
.
.
Robert Swift 02/24 U

 

[ww4612]

you can merge multiply value into a string with &.
for example =rc1&" "&rc2&" "&COUNTIF(rc3:$rc33, "T")

 

[Shawnaby]

Looking for something which will return a date. I posted a new question which better describes what I am looking for.