Need help with Big Project Looping Nightmare

[DuncanC66]

So I have a project I am working on that is quite extensive.

Basically I have an attendance sheet that is a giant flat file. This is fixed and cannot be changed. It has room for 350 employees (350 rows) and 44 columns worth of data for each employee, which represents Monday, this is then repeated 6 more times to fill out the week (308 Columns plus some totals). This is again multiplied by 13 weeks (1 quarter), each week on its own sheet. So BIG workbook.

I need to create database entries so that I can setup a mail merge data file that can then be run to create reports to hand out to each employee with all “occurrences” of Absent – Late – Leave Early – and Tardy Absent (coming in so late or leaving so early they were out more than half day).

If I can figure out how to do Monday I can set it up in a loop to do the rest of the days of the week and then set that up in a loop to do the weekly sheets.
Mondays Columns have an “x” that marks if there is an absent in column AG, Tardy Absent in AH, Tardy In in AI and Tardy Out in AJ. An employee could have multiple “occurrences” so I have to iterate each column separately.

	AG	AH	AI	AJ
09	Absent	Tardy Absent	Tardy In	Tardy Out
10		x	x
11	x
12			X	x

<tbody>
</tbody>

I am self-taught, but have been doing this a while. So I have some skills I just have trouble deciding the best way to do it.

This is basically how I would set it up based on my current skill level:
I would use VBA to set up a table out to the right of the flat file area which I would populate with formulas similar to:

• 
  [*=1]=If(AG10=”x”,”Absent”,””) / Get Type of Occurrence
  [*=1]=If(AG10=”x”,Q10,””) / Get Employee Name
  [*=1]=If(AG10=”x”,$BE$3,””) / Get Date
  [*=1]=If(AG10=”x”,AS10,””) / Get Counseling Points
  [*=1]=If(AG10=”x”,BF10,””) / Get Cumulative Counseling Points
  [*=1]=If(AG10=”x”,BG10,””) / Get Warning Level
  [*=1]Etc… etc…

Once I had that set up for Absent Occurrences I would

• Fill the formulas down 350 rows
• Copy the table
• Paste the table as values to remove the formulas
• Sort it to remove any blanks
• Copy the remaining data again
• Paste it to a new Workbook below any existing data

Then I would repeat the process for Tardy Absent, Tardy In and then Tardy Out.

Then I would repeat those processes for the rest of the 6 days.

Then I would repeat that whole deal for each of the 13 sheets in the workbook.

As you can see this is a big job and would take a long time to process the way I am planning.
I am looking for a better way to "programatically" process this, possibly skipping some/many of the steps above.

I need a VBA super formula that will grab the data based on looping through each of the Occurrence columns and paste a series of cells based on the row I’m in and set it up somewhere else. I really just need the part in Bold below to get me going:

Open New workbook
Setup labels on A1 - J1

Switch back to original workbook

Loop 13 times, once for each Sheet in Workbook[INDENT]Loop 7 times within each sheet for weekdays[/INDENT]
[INDENT][I]offset 44 columns each loop through[/I][/INDENT]
[INDENT=2]Loop 4 times for occurrences[/INDENT]
[INDENT=3][B]Loop 350 times through data from list, based on the “x” in absent section[/B][/INDENT]
[INDENT=4][B][I]wb2.ws2.cells to put data in = wb1.ws1.cells to get data from[/I][/B][/INDENT]
[INDENT=3][B]Next Row[/B][/INDENT]
[INDENT=2]Next Occurrence Loop[/INDENT]
[INDENT]Next Weekday Loop[/INDENT]
Next Week Loop

Thanks in advance for any direction you can give me.

 

[Dryver14]

Sorry, I really don't get this.... in this layout anything in AG would be absent wouldn't it?

 

[oldbrewer]

		Mon	Tue	Wed	Thu	Fri	Sat	Sun
		Oct 05	Oct 06	Oct 07	Oct 08	Oct 09	Oct 10	Oct 11
	bill			AB						sheet1
	fred		LA		LA			LE
	dave			TA
	tom
		Mon	Tue	Wed	Thu	Fri	Sat	Sun
		Oct 12	Oct 13	Oct 14	Oct 15	Oct 16	Oct 17	Oct 18
	bill					AB	LA			sheet2
	fred		LE	LA
	dave				TA	TA
	tom
		LA	LE	TA	AB
	bill	1	0	0	2
	fred	3	2	0	0
	dave	0	0	3	0
	tom	0	0	0	0
		formula giving LA=1 for Bill
		=COUNTIF($C3:$I3,C$27)+COUNTIF($C19:$I19,C$27)
	if the data were really on 2 different sheets it would be of course
		=COUNTIF(sheet1!$C3:$I3,C$27)+COUNTIF(sheet2!$C19:$I19,C$27)

<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[DuncanC66]

So the concept here is I am trying to get a database type entry for a mail merge.
So the Table above shows how the data is laid out currently in the flat file.
I don't have room in this post to show the whole table.
Here is a slightly better example: (Note the columns are not contiguous as I have removed impertinent data.

	Q	AG	AH	AI	AJ	AS	BG
09	Name	Absent	Tardy Absent	Tardy In	Tardy Out	Counseling Points	Warning Level
10	Joe		x	x		1.5	None
11	Jon	x				1.0	None
12	Abe			X	x	1.0	Verbal

<tbody>
</tbody>

I need to identify if the person has an "x" in absent. If they do then I need to grab many other cells with info in them.
Then couple all of that together in a new workbook that I can use for the mail merge.

So the final product would be something like:

Joe|Tardy Absent|1.5|None
Joe|Tardy Absent|1.5|None
Jon|Absent |1.0|None
Abe|Tardy In |1.0|Verbal
Abe|Tardy Out |1.0|Verbal

I need VBA Code not Excel formulas.