Multiple fields reduced Excel

New Contributor

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?

10 Replies


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

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


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


= LET(




best response confirmed by mcunliffe (New Contributor)

Hi @Peter Bartholomew 


Thanks for your reply. I keep getting #NAME? 


Where am I going wrong with this?




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


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.

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

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

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