+ Reply to Thread
Results 1 to 7 of 7

Table to column problem to create dataset for pivot table [Bloomberg data to pivot table]

  1. #1
    Registered User
    Join Date
    01-19-2017
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    6

    Table to column problem to create dataset for pivot table [Bloomberg data to pivot table]

    Hello,
    I have a dataset that consists of multiple (around 400) 3-column sets that are all next to each other. Additionally those 3 column sets are of different "heights" (as shown in attached file), so it's hard to use formula here. I would like to put all those 3-columns sets under each other in 3 columns. I am aware that you can do it manually, but I have multiple files like that and it would be time consuming. I attached the excel file with my problem (not the actual data as its way bigger). I would appreciate your help.

    Kind regards
    Attached Files Attached Files
    Last edited by fajfus; 01-24-2017 at 05:41 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Table to column problem to create dataset for pivot table [Bloomberg data to pivot tab

    Your version of Excel to 2016 so use Power Query and merge tables

  3. #3
    Registered User
    Join Date
    01-19-2017
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    6

    Re: Table to column problem to create dataset for pivot table [Bloomberg data to pivot tab

    Thanks for your reply, but I tried it and couldn't do that. My excel skills are not advanced and I would need step by step kind of help.

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Table to column problem to create dataset for pivot table [Bloomberg data to pivot tab

    Eg. YouTube, keywords are Power Query, merge data

  5. #5
    Registered User
    Join Date
    01-19-2017
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    6

    Re: Table to column problem to create dataset for pivot table [Bloomberg data to pivot tab

    I've checked that, from what I understand I need to make a table out of each 3-column sets (400 in total) and then I can merge that with Power Query (it's faster to do the whole thing manually). If I do it at once it recognizes different columns for each Date, Total assets and Ticker (making it in the table as Date, Date1, Date2..., Total assets, Total assets1... and so on). With different columns names I am not able to solve it.
    Last edited by fajfus; 01-24-2017 at 03:51 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Table to column problem to create dataset for pivot table [Bloomberg data to pivot tab

    I do not know if that's it?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-19-2017
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    6

    Re: Table to column problem to create dataset for pivot table [Bloomberg data to pivot tab

    I appreciate your help, but mine actual dataset consist of columns from A:ATD, so its massive. The thing is it's over 400 firms and you have to create table for each of them and it takes time. I think it's actually faster to cut+paste to a different file and put all the data under each other in 3 columns. I was looking for some faster method. Maybe there's a faster way to change the data I have (in these 3 column tables that you did in the previous attached file) into the tables. If not it still takes a lot of time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automating Pivot table and pivot chart creation if data table names unknown
    By Vegiepie2016 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-04-2016, 11:52 AM
  2. Pivot table problem - how to relate 3 sheets' data into 1 pivot table?
    By GRG Stevan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-14-2015, 06:12 AM
  3. Replies: 1
    Last Post: 04-20-2013, 03:55 AM
  4. [SOLVED] pivot table from raw data with 2 rows per dataset
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2012, 05:45 PM
  5. [SOLVED] Create Pivot Table: Cannot Open Pivot Table Source File
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-05-2010, 05:11 AM
  6. Return pivot table range...not the data table, the PIVOT TABLE!
    By Air_Cooled_Nut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2008, 01:07 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1