+ Reply to Thread
Results 1 to 3 of 3

Multiple same values in A with diffrent values in B to a single horizontal row

  1. #1
    Registered User
    Join Date
    03-28-2018
    Location
    London
    MS-Off Ver
    2013
    Posts
    3

    Lightbulb Multiple same values in A with diffrent values in B to a single horizontal row

    Hi guys,

    I have a problem with data transformation from rows to columns. Since I am a bit familiar with excel the going to option would be PIVOT. But my one seems to be a special case and I can't find any solution by searching google and the search function here in this forum. Mostly because I do not know how to describe the problem in a proper way to look it up.

    NOTE to MODERATOR: If the PIVOT function can't handle this, please feel free to move this post.


    Let's start with an example:
    I got a file which I have to work on and process the data.
    The problem is the data is ordered like this:

    Please Login or Register  to view this content.
    I need it to look like this:
    Please Login or Register  to view this content.
    I have tried PIVOT but that only gives me the rows of the diffrent values in column B as single columns with the values as column names back.
    Like this:
    Please Login or Register  to view this content.
    Maybe there is another option to switch it in PIVOT but I can't find it. I have never had to deal with such a file before and do not know how to modify it to my needs.
    I tried the transpose option (when pasting it) too but the same result...

    It looks like there is no one that has to deal with this sort of data. I can find a bunch of information about the "classical" data for PIVOT like shown below but no one like the one I have got.

    Classic case of PIVOT to "turn data around":
    Please Login or Register  to view this content.

    I really appresciate your help.
    Thank you,
    matt
    Last edited by matt1911; 04-21-2018 at 03:59 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Multiple same values in A with diffrent values in B to a single horizontal row

    Assuming your actual data has numeric values then you could add a countif column and use that to get instances across the top
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Multiple same values in A with diffrent values in B to a single horizontal row

    Are you open to a formula solution rather than pivot?

    In the attached copy column A to column D. Remove duplicates. (If you prefer a formula can do that instead.)
    Then in E2 across column I or further (until you get all blanks.)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

+ 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. Replies: 3
    Last Post: 03-10-2017, 07:56 PM
  2. Replies: 0
    Last Post: 06-24-2014, 05:51 PM
  3. Replies: 4
    Last Post: 05-12-2014, 07:10 PM
  4. Replies: 2
    Last Post: 03-22-2013, 07:42 AM
  5. Sum values from multiple cells - multiple lookup values in single cell
    By taxdept in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 04:12 PM
  6. Replies: 2
    Last Post: 02-20-2012, 06:06 PM
  7. Replies: 3
    Last Post: 05-10-2011, 01:46 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