Reading Data from a Multi-Row Calendar

May 18, 2013 at 10:37:14
Specs: Windows 7
I have an Excel spreadsheet that represents multiple ships doing multiple patrols over the course of year at five-day intervals. I would like to develop a procedure which can read each row and output a list of the patrols that includes ship name, start and end date and patrol type [CD, AM, etc.]. Can anyone help me out?

See More: Reading Data from a Multi-Row Calendar

Report •


#1
May 18, 2013 at 18:53:02
Before we can offer any suggestions, we would need to know something about the layout of the spreadsheet.

Please click on the blue line at the end of this post, read the instructions on how to post example data in this forum and then post an example of your data. Also include an explanation of the output you are looking for, based on the example data provided.

Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 19, 2013 at 04:30:27
		D	0	120	240	360	480	600	720	840	960	1080	1200	1320	1440	1560	1680	1800	1920	2040	2160	2280	2400	2520	2640	2760	2880	3000	3120	3240	3360	3480	3600	3720	3840	3960	4080	4200	4320	4440	4560	4680	4800	4920	5040	5160	5280	5400	5520	5640	5760	5880	6000	6120	6240	6360	6480	6600	6720	6840	6960	7080	7200	7320	7440	7560	7680	7800	7920	8040	8160	8280	8400	8520	8640
C-04	CHARLESTONSC		GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD
C-05	CHARLESTONSC	DS	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	C	C	C	C	C	C	C	C	C	C	C	C	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T	T	C	C	C	C	C	C	C
D-24-5	NORFOLK	DD	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	T	T	T	T	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	TS	TS	TS	TS	TS	TS	C	C	C	C	C	C	C	C	C	C
D-25-5	NORFOLK		LMR	LMR	LMR	LMR	C	C	C	C	C	C	C	C	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C
D-20-5	NORFOLK	DS	DP	DP	DP	DP	DP	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T			DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	C	C	C	C	C	C	C	C	C	C	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR
D-21-5	NORFOLK	DS	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	C	C	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T		LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	C	C	C	C	C	C
D-22-5	NORFOLK						LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	LMR	C	C	C	C	C	C	C	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP
D-23-5	NORFOLK	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	T	T	T	T		AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	C	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK
D-01-5	CHARLESTONSC		DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	C	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	AW	AW	AW	AW
D-02-5	CHARLESTONSC	DS	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	AK	AK	AK	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	DP	DP	DP
D-03-5	CHARLESTONSC	DS			AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T		AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C
D-14-5	CHARLESTONSC		DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T		DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP
D-12-5	MAYPORTFL	DD	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	T	T	T	T					DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	C	C
D-13-5	MAYPORTFL		DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP
D-16-5	MAYPORTFL	DS	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T			DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP
D-17-5	MAYPORTFL	DS												DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T		DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP
D-18-5	MAYPORTFL								AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	TS	TS	TS	TS	TS	TS
D-19-5	MAYPORTFL	DD						DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	T	T	T	T		AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	C	C	C	C	C
																																																																											
																																																																											
C-04	CHARLESTONSC	0	3000	GD			<- Example output																																																																				
C-04	CHARLESTONSC	3000	6240	C																																																																							
C-04	CHARLESTONSC	6240	8760	CD																																																																							
C-05	CHARLESTONSC	0	2520	CD																																																																							
C-05	CHARLESTONSC	2520	3960	CD																																																																							
C-05	CHARLESTONSC	3960	6360	CD																																																																							
C-05	CHARLESTONSC	6360	7320	DS																																																																							
C-05	CHARLESTONSC	7320	7920	T																																																																							
C-05	CHARLESTONSC	7920	8760	C																																																																							
D-24-5	NORFOLK	0	1680	AW																																																																							
D-24-5	NORFOLK	1680	3000	DD																																																																							
D-24-5	NORFOLK	3000	3960	T																																																																							
D-24-5	NORFOLK	3960	5640	DP																																																																							
D-24-5	NORFOLK	5640	6840	C																																																																							
D-24-5	NORFOLK	6840	7560	TS																																																																							
D-24-5	NORFOLK	7560	8760	C																																																																							


Report •

#3
May 19, 2013 at 09:41:48
Thanks for posting the data in a format that we can read. Next time, please include Column letters and Row numbers so we can reference specific cells or ranges if need be.

That said, there is still some information missing. Keep in mind that we don't know what your data represents or how you are using it. It would behoove you to explain how you went from your input to your output.

For example, let's look at this part of your output:

				
C-05	CHARLESTONSC	0	2520	CD
C-05	CHARLESTONSC	2520	3960	CD
C-05	CHARLESTONSC	3960	6360	CD

All three lines start with C-05 CHARLESTONSC and they all end in CD. However, I can't tell from your input data where the numbers in the middle came from or what they represent.

Please explain how your output is derived from your input.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 19, 2013 at 10:56:42
The array is basically a calendar showing the underway periods of ships and the missions they are assigned. The calendar starts at column A with the name of the ship, followed by the home port, followed by a code indicating maintenance activity (if any) performed in the year. The calendar itself starts at column D, with hour zero and increments at five-day (120 hour) intervals across the columns for one year and the last column is the period 8640 - 8760. Basically, I was looking for some code that would run through each row of they array and identify the start time, completion time and activity type entered into the array.

Report •

#5
May 19, 2013 at 13:57:46
Let me try to explain my confusion in a little more detail. I really wish you had included Column letters and Row numbers, so I could refer to specific cells, but since you didn't, we'll do our best.

Let's look at the first line of your output example:

C-04	CHARLESTONSC	0	3000	GD 

I assume this output come from the fact that there is a GD under the column with a heading of 120 all the way through to the column with a heading of 3000. I don't quite understand why the output starts at 0, so I'll assume that the output always starts at the column prior to the appearance of the letters, e.g. GD.

OK so if those assumptions are right, I can understand where these 2 line came from:

C-04	CHARLESTONSC	3000	6240	C
C-04	CHARLESTONSC	6240	8760	CD

The C starts under 3120, so back up one column and output 3000.
The CD starts under 6360, so back up one column and output 6240.

If all that is correct, then I don't see where these lines came from, specifically the second line:

C-05	CHARLESTONSC	0	2520	CD
C-05	CHARLESTONSC	2520	3960	CD
C-05	CHARLESTONSC	3960	6360	CD

1: CD starts at 120, so use 0 and ends at 2520. Makes sense.
2: Here's where I'm confused. There's a C under 2640, which means start at 2520, but I have no idea why the output is still CD and not C.

Why doesn't the output look like this?

C-05	CHARLESTONSC	0	2520	CD
C-05	CHARLESTONSC	2520	3960	C
C-05	CHARLESTONSC	3960	6360	CD

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
May 20, 2013 at 06:01:12
OK, let's try this again. I have manually entered the row and column labels. I cannot
account for why the first "GD" isn't showing up in cell D2 in this paste - it is in the
spreadsheet. The first first patrol period for that ship goes from hour zero (cell D1)
to hour 3000 (cell AD1) - since the hour values (row 1) show the beginning
of the period, you have to reference the next column for the end end time (patrol ends
in column AC, reference AD for the end time.

	A	B	C	D	E	F	GD	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	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX
1			D	0	120	240	360	480	600	720	840	960	1080	1200	1320	1440	1560	1680	1800	1920	2040	2160	2280	2400	2520	2640	2760	2880	3000	3120	3240	3360	3480	3600	3720	3840	3960	4080	4200	4320	4440	4560	4680	4800	4920	5040	5160	5280	5400	5520	5640	5760	5880	6000	6120	6240	6360	6480	6600	6720	6840	6960	7080	7200	7320	7440	7560	7680	7800	7920	8040	8160	8280	8400	8520	8640
2	C-04	CHARLESTONSC	 	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	GD	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	C	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD
3	C-05	CHARLESTONSC	DS	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	C	C	C	C	C	C	C	C	C	C	C	C	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T	T	C	C	C	C	C	C	C
4	D-24-5	NORFOLK	DD	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	T	T	T	T	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	TS	TS	TS	TS	TS	TS	C	C	C	C	C	C	C	C	C	C
5	D-25-5	NORFOLK	 	LM	LM	LM	LM	C	C	C	C	C	C	C	C	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C
6	D-20-5	NORFOLK	DS	DP	DP	DP	DP	DP	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T			DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	C	C	C	C	C	C	C	C	C	C	LM	LM	LM	LM	LM	LM	LM	LM	LM
7	D-21-5	NORFOLK	DS	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	C	C	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T		LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	LM	C	C	C	C	C	C
8	D-22-5	NORFOLK	 					LM	LM	LM	LM	LM	LM	LM	LM	LM	C	C	C	C	C	C	C	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP
9	D-23-5	NORFOLK	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	T	T	T	T		AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	C	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK
10	D-01-5	CHARLESTONSC	 	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	CD	C	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	AW	AW	AW	AW
11	D-02-5	CHARLESTONSC	DS	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	AK	AK	AK	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	DP	DP	DP
12	D-03-5	CHARLESTONSC	DS			AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T		AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C
13	D-14-5	CHARLESTONSC	 	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T		DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP
14	D-12-5	MAYPORTFL	DD	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	T	T	T	T					DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	C	C
15	D-13-5	MAYPORTFL	 	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP
16	D-16-5	MAYPORTFL	DS	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T			DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	C	C	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	C	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP
17	D-17-5	MAYPORTFL	DS												DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DS	DS	DS	DS	DS	DS	DS	DS	T	T	T	T		DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP
18	D-18-5	MAYPORTFL	 							AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	C	TS	TS	TS	TS	TS	TS
19	D-19-5	MAYPORTFL	DD						DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	DP	C	C	C	C	C	C	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	AW	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	DD	T	T	T	T		AK	AK	AK	AK	AK	AK	C	C	C	C	C	C	C	C	C	C	C	C
20																																																																												
21																																																																												
22	C-04	CHARLESTONSC	0	3000	GD			<- Example output																																																																				
23	C-04	CHARLESTONSC	3000	6240	C																																																																							
24	C-04	CHARLESTONSC	6240	8760	CD																																																																							
25	C-05	CHARLESTONSC	0	2520	CD																																																																							
26	C-05	CHARLESTONSC	2520	3960	CD																																																																							
27	C-05	CHARLESTONSC	3960	6360	CD																																																																							
28	C-05	CHARLESTONSC	6360	7320	DS																																																																							
29	C-05	CHARLESTONSC	7320	7920	T																																																																							
30	C-05	CHARLESTONSC	7920	8760	C																																																																							
31	D-24-5	NORFOLK	0	1680	AW																																																																							
32	D-24-5	NORFOLK	1680	3000	DD																																																																							
33	D-24-5	NORFOLK	3000	3960	T																																																																							
34	D-24-5	NORFOLK	3960	5640	DP																																																																							
35	D-24-5	NORFOLK	5640	6840	C																																																																							
36	D-24-5	NORFOLK	6840	7560	TS																																																																							
37	D-24-5	NORFOLK	7560	8760	C																																																																							


Report •

#7
May 20, 2013 at 12:57:05
Thanks for the Row and Column headings. They really help.

OK, I think we have a number of issues and it may because of the paste you did. Remember, I don't want to offer any code or suggestions until I am sure that the example data that I am looking at is correct. I think you need to look very closely at the example data you've posted and make sure it is correct. I'm seeing many indications that it is not.

First:
I am ssuming that the Column bewteen F and H is G, not GD as shown in your post.

Second:

Are the ship names in Column B or C?

Third:

You said: "I cannot account for why the first "GD" isn't showing up in cell D2 in this paste - it is in the spreadsheet."

My guess is that the paste inserted a "tab" after CHARLESTONSC and everything is shifted one column to the right.

Fourth:

You said: "The first first patrol period for that ship goes from hour zero (cell D1) to hour 3000 (cell AD1)"

If you scroll your post out to cells AC1/AD1 you'll see that the 3000 is showing in AC1, not AD1. Please clarify.

Fifth

I've asked this before, but haven't seen a response, so I'll ask it again, this time using the cell references provided:

In Row 3, for "C-05 CHARLESTONSC" there is a C in Z3 (under 2640), but your output in Row 26 shows:

       A      B    C      D      E     F        
26   C-05   CHARLESTONSC 2520   3960   CD

Why isn't there a C in F26, instead of a CD?

Sixth:

What is the DS in D3 for? You don't show it in your output, yet it appears under the 0 start time.

Seventh

Similar question for the DD in Row 4. Is the DD part of the name "NORFOLK DD" or is it supposed to be in some other column?

Conclusion:

I suspect that your columns ae not line up correctly because of the way the data was pasted into this forum's editor. I suspect that there are tabs between some columns and it is messing with the alignment.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question