+ 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... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    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