Hi all, I have vehicle names in A1:AE1, under each header are actual model years (i.e., 1999, 2001, etc). Some headers only have one year under them, some have eleven. I need a formula (in a separate worksheet) that will match the car name and list each year next to it.
I have the worksheet attached, I'm hoping there is a good array formula that will do this. I think once you open the file, you will understand what I am looking for.
Thank you for your help!!!
Hi alexx,
Power Query to the rescue. If you install the Power Query Add-In this is simple work. No vba. No formulas.
Steps...
1. Click anywhere in your first table. Power Query from Table.
2. Select all the columns of data in the PQ Editor
3. Unpivot Only Selected Columns (they are all selected)
4. I sorted by year and car and renamed the column heads but you don't need to.
5. Close and Load (I did this into Sheet1).
See the attached. Easy Peasy?
Car Data (1).xlsx
This is a close link:
https://trumpexcel.com/unpivot-data/
except in your problem select all the columns and unpivot them.
Last edited by MarvinP; 05-08-2018 at 04:40 PM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
HI Marvin, I should have asked no PowerQuery eitherthe reason being, is this is going to be part of a larger spreadsheet. I will use the new columnar data to do some data variance analysis. Once I am finished, I am envisioning a completely automated solution for my end user.
The way the data is currently laid out on my real report, it is completely dynamic.
Could you have compassion on me this time and toss me a formula if you know of one??
So no Power Query
How about some VBA? Will all the data start in A1? Will "completely dynamic" mean the car list is in row 1 and the years are down under each?
If you want a formula only and no VBA then I'm not that smart.
On a normal day, that was an awesome answer! This report is just a pain though.....thanks for trying MarvinP!~
This proposed solution employs a helper row (1) on the Current State of Data sheet, which may be moved and/or hidden for aesthetic purposes.
The formula that populates A1 is: =COUNT(A3:A25)
The formula that populates B1:AE1 is: =SUM(COUNT(B2:B25),A1)
On the Desired State sheet the array entered formula* that populates the Model column is:Formula:
Please Login or Register to view this content.
The formula that populates the Year column is:Formula:Please Login or Register to view this content.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks