Count Occurrence with Index

[johnlhaase]

Hello

I am working with a limited data set. I need to create a grouped index and my thought was to count each occurnace and fill down thereby giving me set or groups to work with.  In my table below I would like the top Duty to count as 1 and the next Duty would be 2. I could then fill or group each set of Duty, Block and Rest with 1, 1, 1 and 2, 2, 2 etc. I cannot figure this one out. The data is limiting and I do not want to spend time manully

FlightAssignmentHoursPICSICOver-20 PIC

3000	Duty	15	1	2	0
3000	Block	13	1	2	0
3000	Rest	46	1	2	0
3000	Duty	18	1	2	0
3000	Block	12	1	2	0
3000	Rest	27	1	2	0
3000	Duty	14	1	2	0
3000	Block	12	1	2	0
3000	Rest	22	1	2	0

[HotChilli]

You've switched the requirements on me.  What's the desired output from that last sample data?

[johnlhaase]

Hello the two original tables' data  lacks a useful key to join the two tables  tables. By using an idex based on the order of Duty, Block and Show Time I can then join the tables with an output I have below. Now I have some useful data. But without being able to increase the count for each Show Time it will not work, there could be two to three flights between the Show Times that need to be skiped.

Thanks

John Haase

PICSICOver-20 PICFlightDaysDayFlight2Hours3PIC4SIC5Fourth Pilot

3000	Duty	15	1	2	0	1	1	Show Time	1	2
3000	Block	13	1	2	0	1	1	Flight	13	1	2	1
3000	Duty	18	1	2	0	2	2	Show Time	1	2
3000	Block	12	1	2	0	2	2	K4 807	8	1	2	0
3000	Duty	14	1	2	0	3	3	Show Time	1	2
3000	Block	12	1	2	0	3	3	Flight	12	1	2	1
3000	Duty	6	1	2	0	4	4	Show Time	1	2
3000	Block	4	1	2	0	4	4	Flight	4	1	2	0
3000	Duty	16	1	2	0	5	5	Show Time	1	2
3000	Block	14	1	2	0	5	5	Flight	14	1	2	1
3000	Duty	22	1	2	1	6	6	Show Time	1	2
3000	Block	15	1	2	0	6	6	Flight	8	1	2	0
3000	Duty	17	1	2	0	7	7	Show Time	1	2
3000	Block	13	1	2	0	7	7	Flight	12	1	2	1

[johnlhaase]

FlightDayFlightDaysAssignmentFlight2PICSICOver-20 PICHoursHours3

3000	1	1	Duty	Show Time	1	2	0	15
3000	1	1	Block	Flight	1	2	0	13	13
3000	2	2	Duty	Show Time	1	2	0	18
3000	2	2	Block	Flight	1	2	0	12	8
3000	3	3	Duty	Show Time	1	2	0	14
3000	3	3	Block	Flight	1	2	0	12	12
3000	4	4	Duty	Show Time	1	2	0	6
3000	4	4	Block	Flight	1	2	0	4	4
3000	5	5	Duty	Show Time	1	2	0	16
3000	5	5	Block	Flight	1	2	0	14	14
3000	6	6	Duty	Show Time	1	2	1	22
3000	6	6	Block	Flight	1	2	0	15	8
3000	7	7	Duty	Show Time	1	2	0	17
3000	7	7	Block	Flight	1	2	0	13	12

[v-kelly-msft]

Hi  @johnlhaase,

Could you pls also provide your expected output based on your sample data?Best with your logic.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[johnlhaase]

Hello Works great when we have a set number between each row. Now in this eaxmple beow there is no set number. The row sequence is the same "Show" then flights and "Debrief". There could be multiple flights. So, the set number is not always the same. The end Goal is to merge (join) the one set of days with the other. But your solution does with with a set number

DayFlightDeparture-Arrival

	Show Time
15Jun Tu	K4 807	CVG-BAH
	Debriefing
	Show Time
17Jun Th	K4 807	BAH-HKG
18Jun Fr	K4 860	HKG-NGO
	Debriefing
	Show Time
19Jun Sa	K4 258	NGO-LAX
	Debriefing
	Show Time
20Jun Su	K4 232	LAX-SYD
	Debriefing
	Show Time
24Jun Th	K4 259	SYD-SIN
	Debriefing
	Show Time
25Jun Fr	K4 259	SIN-HKG
26Jun Sa	K4 258	HKG-NGO
	Debriefing

[HotChilli]

If you add an Index column (from 0) you can add another custom column like:

Number.IntegerDivide([Index], 3) + 1

and that should do it.  

[johnlhaase]

I will try it now. Thanks