Oct 29 2021 08:30 AM
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?
Oct 29 2021 12:36 PM
better if you share Excel file with exactly same information as on screenshot, it will be easier to illustrate the proposed solution.
Nov 01 2021 01:29 AM
Thanks @Sergei Baklan I've attached the Excel file.
Nov 01 2021 02:07 AM
This is very easy for an Excel 365 user but a little messier using traditional techniques.
= LET(
k,SEQUENCE(4,5),
INDEX(source,k))
Nov 01 2021 03:33 AM
Solution
Thanks for your reply. I keep getting #NAME?
Where am I going wrong with this?
Nov 01 2021 03:54 AM - edited Nov 01 2021 04:06 AM
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
Nov 01 2021 04:40 AM
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.
Nov 01 2021 04:55 AM
Nov 01 2021 08:07 AM
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.
Nov 01 2021 09:46 AM
@Peter Bartholomew and @Wyn Hopkins Thank you both very much! Great team work!
Nov 01 2021 03:33 AM
Solution
Thanks for your reply. I keep getting #NAME?
Where am I going wrong with this?