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?

Before we can offer any suggestions, we would need to know something about the layout of the spreadsheet. Please click on the

blue lineat 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.

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

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 CDAll 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.

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.

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 GDI 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 CDThe 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 CD1: 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.

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

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.

FifthI'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 CDWhy 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.

SeventhSimilar 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.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History