Forum Discussion
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?
9 Replies
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/QkvCkU5QB4cbetter if you share Excel file with exactly same information as on screenshot, it will be easier to illustrate the proposed solution.
Thanks SergeiBaklan I've attached the Excel file.
This is very easy for an Excel 365 user but a little messier using traditional techniques.
= LET( k,SEQUENCE(4,5), INDEX(source,k))
