+ Reply to Thread
Results 1 to 3 of 3

Problem transposing with VBA

  1. #1
    Registered User
    Join Date
    05-09-2020
    Location
    Buenos Aires, Argentina
    MS-Off Ver
    2010
    Posts
    3

    Unhappy Problem transposing with VBA

    Hi guys!

    Thanks in advance as usual!

    I need to modify a lot of data using VBA and my main problem is this.

    Im having a doubt, I need to transpose data like this to be seen vertically. The problem is that some names belong to the same country and this is my main problem to transpose the data.

    The input seems like this:

    Country Name
    Brazil Alfonso
    Brazil Jorge
    Colombia Maria
    Japan Lia
    Spain Luis
    Spain Luisa
    UK Robert

    And is needed to be like this:

    Country Brazil Colombia Japan Spain UK
    Alfonso Maria Lia Luis Robert
    Jorge Luisa


    I can create the list, remove duplicates and transpose the countries but how do I assign the people that belongs to the country, any idea that help me to think in how solve this?

    Attached the reference book if it helps.

    Thanks as usual!
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    MEXICO
    MS-Off Ver
    Excel 2013
    Posts
    212

    Re: Problem transposing with VBA

    Your data in columns A and B. Result in E1 onwards

    Try this:

    Please Login or Register  to view this content.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Problem transposing with VBA

    An alternative is to pivot the data using Power Query. Here is the Mcode

    Please Login or Register  to view this content.
    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. Transposing problem
    By Hoekstraat in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-12-2018, 08:20 AM
  2. Problem Transposing between workbooks
    By coreytroy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2014, 05:00 PM
  3. Transposing Problem
    By ramssmar in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-27-2009, 04:44 PM
  4. Problem in Transposing
    By batrasunil81 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-29-2008, 04:07 AM
  5. transposing problem
    By karelappel in forum Excel General
    Replies: 2
    Last Post: 01-21-2008, 02:34 PM
  6. [SOLVED] Please help with a column/row transposing problem
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-26-2006, 05:40 AM
  7. Replies: 2
    Last Post: 07-25-2006, 07:30 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