Transforming yearly column entries into row variables

Galaxorian

New Member
Joined
Sep 30, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have another question concerning the formatting of data (see the picture). The file in question contains information on about 260 unique companies. However, I want to structure the data in such a way that for each company there is a single row and multiple columns on for example Assets2000, Assets 2001 etc. I want to do this for assets, employees, revenue and RandDexpense. The issue is that the data range is from 2000 to 2006 so most companies have 7 rows, but some are missing a few years and have 3 rows. How do I go about getting the data in the wanted format?
Help.PNG



Thanks a ton!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
but your assets, employees, revenue and RandDexpense are not available on the picture
what about Data Year - Fiscal? All in one cell?
use XL2BB to post representative example with generic data and expected result
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
something like this?
Company NameTicker SymbolCUSIPCIK NumberGlobal Company KeyISO Currency CodeFiscal Year-end MonthData DaData Year _ Fiscal
ABBOTT LABORATORIESABT282410018001078USD1220001231, 20011231, 20021231, 20031231, 20041231, 20051231, 200612312000, 2001, 2002, 2003, 2004, 2005, 2006
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,574
Members
410,852
Latest member
WernerS
Top