Multiple fields reduced Excel

[mcunliffe]

I am pretty new to Excel so probably finding the easy stuff had, but I'm wondering if anyone can help me with this. My data set has come from a .txt file, and I'm trying to turn it into usable data in Excel pivot table. Attached is an example of the stage I'm at, where all the data is in two rows and I want to use the duplicated top row as headers and the varying data to drop into columns below each header. How can I do this?

[#2]

@mcunliffe 

better if you share Excel file with exactly same information as on screenshot, it will be easier to illustrate the proposed solution.

[#3]

Thanks @Sergei Baklan I've attached the Excel file.

[#4]

@mcunliffe 

This is very easy for an Excel 365 user but a little messier using traditional techniques.

= LET(
  k,SEQUENCE(4,5),
  INDEX(source,k))

[#5 · Accepted solution]

Hi @Peter Bartholomew 

Thanks for your reply. I keep getting #NAME? 

Where am I going wrong with this?

[#6]

@mcunliffe

Here's my very similar PQ approach to @Herbert_Seidenberg   I named the range of cells first rather than converting it to a table

If this layout is an extract from a system then use Power Query to reference that text file without even opening it

If you're not familiar with Power Query then check out my intro video here...
https://youtu.be/QkvCkU5QB4c

[#7]

@mcunliffe 

If with legacy formulae, here

in A16

=INDEX($A$2:$T$6, INT( (ROW() - ROW($A$16) -1)/4)+1, 5*MOD( ROW() - ROW($A$16) -1, 4)+COLUMN() - COLUMN($A$16)+1 )

drag it to the right and when entire line down till zero values appear.

[#8]

You'll need highlight the data in row 2 and use the Name box to name it as SOURCE (at least I'm assuming that's what Peter did

[#9]

@Wyn Hopkins 

Thanks, I probably should put more effort into describing the defined Names; just because I haven't used a direct cell reference since 2015 certainly doesn't mean others are familiar with the approach. In this instance, I was unsure whether it is 'Source' that is the problem or my use of the 365 functions LET and SEQUENCE.
I am an infrequent user of the Power tools so I still find it somewhat difficult to visualise the result of pivoting and unpivot steps. I presume it becomes easier with practice.

[#10]

@Peter Bartholomew and @Wyn Hopkins Thank you both very much! Great team work!