Fields repeating after every Nth Field number

Question

Given below is the dataset. Kindly note that the dataset has been shared horizontally i.e. same field repeats after every 3rd filed (although the field names are different - but as we can see below, based on the data that each field represents, one can infer the similarity in the fields (eg - field name -> 1, 4 & 7 imply same meaning)

This is the outcome that I am expecting (but subject to an IMPORTANT NOTE specified below this snip):

IMPORTANT NOTE

I want to make sure that workflow is dynamic enough to capture the data from new fields that would get added with similar pattern going forward i.e. in essence, same filed repeating after every 3rd field (Eg: when new fields are added  as 10, 11, 12; then field 10 will imply same meaning as field 1,4 &7....& so on)

I have attached the excel sheet for reference.

Thanks in advance!

Test X.xlsx

DataNath · Accepted Answer

How does this look @Chirag_KRCPL? It should be fully dynamic as the data is transposed and a repeating 1-3 count is applied to all values that come through. Workbook attached - let me know if you have any issues!

Emmanuel_G · Accepted Answer

Hi @Chirag_KRCPL;,

You can find a test with desired output.

Really simple to use.

It works even if you have more 3 fields for each line.

Let us know if it works as you want.

DavidP · Accepted Answer

Hi @Chirag_KRCPL

I used a similar idea to @DataNath, but tried to do it without multi-row formulas.

You have to define a value for N and I included a RecordID tool so that you're not relying on the field names to be numbers.

You can then use the Mod() and Ceil() functions to define the column and row numbers before using the CrossTab tool.

Chirag_KRCPL · Accepted Answer

Thaknyou @DavidP @Emmanuel_G @DataNath for your response!!