Return record from a datasheet and start again at top of the list - data transposition

Dear All,

I enclose an Excel workbook. The first sheet "data" has the source data. In the second sheet "output", I need to show the same data, but in a certain format. The "output" format has to be a Period number in ascending order for each transaction, with the corresponding sales person and region against each unique period number.

In the "output" sheet, I have written a formula that lists the number of periods for each transaction, and is limited by the number of rows/records from the "data" sheet (here it is 6 rows/records). So, period 1 has 6 records, period 2 has 6 records and so on until period 12.

What I am unable to write as a formula is that each time a period number changes, the formula would start referencing the first record in the list for sales person and region. In the "output" sheet i have manually typed what records i'd expect to see the formula return for sales person and region.

Additionally, I cannot use a Pivot table to summarise the data.

Please can any one help solve this problem?

Regards,

David

If the structure does not change...

You can just use the 1st formula with adjusted range.
In C2.
=INDEX(data!$B$2:$B$8,COUNTIF(A$2:A2,A2))

Copy down.

Many thanks CK76. You have been very helpful! Kind regards.