SOLVED

New Contributor

# Multiple fields reduced Excel

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

# Re: Multiple fields reduced Excel

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

# Re: Multiple fields reduced Excel

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

# Re: Multiple fields reduced Excel

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

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

best response confirmed by mcunliffe (New Contributor)
Solution

# Re: Multiple fields reduced Excel

Where am I going wrong with this?

# Re: Multiple fields reduced Excel

@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

# Re: Multiple fields reduced Excel

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.

# Re: Multiple fields reduced Excel

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

# Re: Multiple fields reduced Excel

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.

# Re: Multiple fields reduced Excel

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