Extract data from a Table

rasika99

New member
Joined
Jan 14, 2014
Messages
3
Reaction score
0
Points
0
Guys,

I have a table containing the data of audits of companies by Rating Agencies (in last 03 years). My boss asks summaries of these audits. I attached the table and the summery report format.

Can u guys help me to do this?

View attachment Book1.xlsx
 

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
I suggest adapting the table to have the following columns
No, Company,Year, Rating agency, Type of audit, Month Company Rating
Then extracting it with a pivot table
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try this formula in D9

=INDEX(Table!$C$5:$AC$14,MATCH($D$5,Table!$B$5:$B$14,0),(COLUMN(A1)-1)*3+ROW(A1))

then copy down and across
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
BTW, best to put a data validation list in D5 with the company list.
 
Top