+ Reply to Thread
Results 1 to 8 of 8

Create new Columns based on Description with corresponding Value

  1. #1
    Registered User
    Join Date
    02-05-2021
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    6

    Create new Columns based on Description with corresponding Value

    Hello,

    I am using World Bank data and want to create a database I can use for further analysis.
    I have a Column called Target which lists the different SDG targets in the format of 1.1, 1.2, 1.2.1 etc.
    Each Row has Target number and the Value of said target.
    I would like to create new columns which are are named by the target and have the value of said target. I have been trying to get this to work using power query but I am stuck.
    The Idea is that then I have a Column for 1.1 another for 1.2 etc thereby reducing the number of rows I have in total.

    I would greatly appreciate help and attached example data.

    Best Regards
    Raul
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Create new Columns based on Description with corresponding Value

    What are you trying to do? Transpose the whole table.... or just create a few columns?

    Creating a few columns can be done with:

    =TRANSPOSE(SORT(UNIQUE(data!$B$2:$B$427,FALSE)))

    or

    =TRANSPOSE(UNIQUE(data!$B$2:$B$427,FALSE))

    as SORT doesn't hugely like sorting alphanumerics. However, I suspect what you REALLY want is more complex than this...
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Create new Columns based on Description with corresponding Value

    Mock up a sheet with 10 rows and 5 columns and SHOW the expected results.... to prevent guessing games.

  4. #4
    Registered User
    Join Date
    02-05-2021
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Create new Columns based on Description with corresponding Value

    Hey, thank you for your quick help.
    I created an example which shows what I am trying to do.
    Ideally I would like to find a way to do this in powerquery as I have a large amount of data but in excel would be fine as well.

    Best
    Raul
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,471

    Re: Create new Columns based on Description with corresponding Value

    Load into PQ - select the TARGET column - pivot column using the Values column and Don't Aggregate.
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Create new Columns based on Description with corresponding Value

    J1:
    =TRANSPOSE(UNIQUE(A2:A17))

    H2:
    =UNIQUE(B2:C17,FALSE)

    and a regular formula in H3, copied across and down:

    =INDEX($D$2:$D$17,MATCH(1,($B$2:$B$17=$H2)*($C$2:$C$17=$I2)*($A$2:$A$17=J$1),0))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-05-2021
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Create new Columns based on Description with corresponding Value

    Thank you both very much! It worked perfectly

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,471

    Re: Create new Columns based on Description with corresponding Value

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Thanks for the rep.

+ 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. [SOLVED] Drop List with a description but once chosen description disappear
    By dezspert22 in forum Excel General
    Replies: 22
    Last Post: 12-19-2016, 05:24 PM
  2. how to make description changes itself when changing the columns
    By abdrahman.umair in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2015, 09:51 AM
  3. Comparing Columns and Populating With Description
    By VyT in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2013, 06:41 PM
  4. Trim description into separate columns
    By mskelton in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-17-2013, 12:46 PM
  5. Replies: 0
    Last Post: 08-16-2012, 02:01 PM
  6. Split Description into 3 Columns
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-10-2009, 05:01 PM
  7. Insert columns based on tab description
    By lockye in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2009, 10:48 AM

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