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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,099
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,099
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,260
Messages
5,546,827
Members
410,758
Latest member
Bufnercash
Top