Pivoting and organizing Auto-Populating data with formulas

[alacey425]

Hello,

I am working with a large amount of data so I created a small example of what I hope to do attached below. My problem is with formatting in Excel and I have been unable to find the right combination of formulas to complete what I am trying to do, hopefully you all can help!

Quick Background - I am using the Bloomberg add-in with Excel so everything I have highlighted in orange will be imported automatically.

In the first table I have the class numbers and subject. I need to be able to take the class number and set up a function where I could drag or pivot Class 1 and GPA across the top and it will automatically populate Class 2 GPA, Class 3 GPA, etc. In reality I am working with 900 "classes" so having to type every one is painstaking. Again, these tables will automatically populate with data pulled from Bloomberg, so I am mostly concerned with the Column headings changing.

Last and most important I need a formula to list the names of each student and GPA with their class number to the left of the name, with a space between each class section. I will have over 900 classes, all with different numbers of students, that I need to populate automatically in this way. This is one of my biggest problems dealing with the different numbers of students for each class and then having it populate below (See image).

If this is possible, and it is possible for any of you to help, I greatly appreciate it!

[/IMG]

 

[StaticVoid]

I have used the Bloomberg add-in before, never knew it has GPAs there .
I don't see how this can be done without VBA. Would you consider it? If yes, would you post a link to a minimal example? Also, is the number of classes static, or you can have more than 900?

 

[saeedkk]

Hello,

in order to solve the first part you need to use this formula
=IF(COUNTIF(OFFSET(D1,0,-3),"class*")>0,CONCATENATE("class ",RIGHT(OFFSET(D1,0,-3),LEN(OFFSET(D1,0,-3))-SEARCH(" ",OFFSET(D1,0,-3)))+1),IF(COUNTIF(OFFSET(D1,0,-3),"GPA*")>0,"GPA",""))

but be careful, in order to use this formula you need to just setup the first three columns as this ( i.e A1 , B1 , C1 )


class 1 GPA ""
then use the function afterwords in the fourth column( ie D)

as for the other part i m sorry have no time now i have to run , when i m back if it is not solved, will give it a try

Regards
Saeed

 

[alacey425]

I have never used VBA but I am open to anything. The number of classes can change also.

 

[saeedkk]

hello again ,

sorry for being late

well you can do it in VBA, but it has several for loops... anyways here is your solution without VBA which is very much like a for loop , it was a nice challenge.please follow the guides and i expect that you are using the previous formula i mentioned.

first lets set the keywords i ll be using,
"Data Tables" is your Bloomberg Tables
"End List " is the final table that you need to display
you have to use the top row for the titles.

second , in order to iterate through the cells in the data tables you need to put a column which is an iterator , this column will be a dummy column which the functions use to display the information you need in the end list, at the top of this column is the starting cell of your data tables ( for example : just write A1 as text ), the cells below this cell will use this function ( D8 in my case is call that i filled with the text A1)
=IF(INDIRECT(D8)="",SUBSTITUTE(ADDRESS(ROW(OFFSET(INDIRECT(D8),-ROW(OFFSET(INDIRECT(D8),0,3))+1,3)),COLUMN(OFFSET(INDIRECT(D8),0,3))),"$",""),SUBSTITUTE(ADDRESS(ROW(OFFSET(INDIRECT(D8),1,0)),COLUMN(OFFSET(INDIRECT(D8),0,0))),"$",""))

you can drag this column as much as you want and will iterating all your data tables one by one , it will jump three columns as per your data you showed above ( please stick to this requirement)

now next column in the list will use the function
=OFFSET(INDIRECT(D8),-ROW(OFFSET(INDIRECT(D8),0,0))+1,0) which will also get the title ( class number) you have in your data tables , so you can drag them as much as you want

next column will use the function
=IF(COUNTIF(INDIRECT(D8),"*class*")>=1,0,INDIRECT(D8))
this will extract the name , the iteration will produce the titles cells which dont have names , so when it is iterating the class title , then it will display 0 ( this is for out to sort in the end )

GPA column
=IF(COUNTIF(INDIRECT(D8),"*class*")>=1,0,OFFSET(INDIRECT(D8),0,1))
this will extract GPA from your data tables

check my print screen and also i have put the excel sheet that i used so you can use it in my drive in the below link, it is in a zip

https://drive.google.com/file/d/0BzZL_L3ZnGTdS3duVWNxMkZNeVk/view?usp=sharing

Hope this helps you
Regards
Saeed

 

[alacey425]

Saeed,

Thank you very much for you time and effort and it nearly solved the painstaking problem I have. I tried applying your formulas to my spreadsheet but a problem arises because all the "classes" (i.e. class 1, class 2, etc.) are not the same (this is why I had put to pull from the vertical list as step one). I should have asked for the "subjects" since they are different, my mistake Saeed for the confusion.

I have uploaded a shortened version of my actual excel file with explanations on my drive to provide clarity and show exactly what I am hoping to accomplish in the link below:

https://drive.google.com/file/d/0B0EXrjDqZFT8c0xpVE93LU5uS3c/view?usp=sharing

Thank you for the help!

<colgroup><col width="485"></colgroup><tbody>
</tbody>

 

[saeedkk]

Hello,

please find the workbook with what you need , i have solved it in the way you presented the examples, and find my answers and comments in red inside the file

https://drive.google.com/file/d/0BzZL_L3ZnGTdYnAyMlh1bldPd0k/view?usp=sharing

hope this is a good help ,

you are a financial analyst i presume?

Regards
saeed

 

[alacey425]

This is truly amazing you saved me an incredible amount of time. I am a graduate student working on a research project and now that I am able to have all the data it will be a massive time saver! Incredible.

I don't want to be greedy but have two more questions:
Is there an easer way when copying the 8 cells across, or will I simply have to keep dragging it until I reach A900?
Also do you think it is at all possible to eliminate Step 2?

Thanks again Saeed!!

 

[saeedkk]

Hello..
Well that is indeed greedy
It only takes less than 1 min to drag about 10000 cells in excel
If you need to make it automatic without interaction ... it would be using vba
I have a question for you
The excel add in for bloomberg can be bought on itself or you would need a bloomberg terminal ?? What is needed to get it...
It eas a nice challange
Your welcome