+ Reply to Thread
Results 1 to 9 of 9

Transpose or Pivot??

  1. #1
    Registered User
    Join Date
    02-23-2021
    Location
    Tennessee
    MS-Off Ver
    O365
    Posts
    4

    Transpose or Pivot??

    I have a table of data of customers. The column data includes the standard name, address, etc.. It also includes 1 column of the software platform they own. Because customers have multiple software platforms, there are multiple rows of the same customer data with the software column including the 2nd, 3rd, 4th, etc. software they own. How can I move all the separate rows of the software onto the first row of the customer creating multiple columns of the different software they own? It would be best to take the separate rows of software and add them onto the first row of the customer. Then I can remove all the additional rows of the customer. The final goal is to have one row per customer that includes the standard data like name, address, etc. and then multiple columns of software. So a variation of a transpose or is this a pivot need? Is this possible? A sample of data is attached.
    Attached Files Attached Files
    Last edited by gman830; 02-23-2021 at 09:52 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,409

    Re: Transpose or Pivot??

    Using the technique shown here https://sfmagazine.com/post-entry/ju...a-pivot-table/

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    3
    Church Name Address City State Zipcode Phone Primary Contact Platforms
    4
    Fifth Avenue Presbyterian Church 7 W 55th St New York NY 10019-4902 (212) 247-0490 xxxxxx ShelbyNext Giving, ShelbyNext Financials (Installed), Arena Select
    5
    First Baptist Church 1205 Spring St Mountain Home AR 72653 (870) 425-6961 xxxxxx ShelbyNext Financials (Hosted)
    6
    First Pentecostal Church 5000 I 55 S Jackson MS 39272-5505 (601) 373-9000 xxxxxx V5
    7
    First United Methodist Church 190 E Franklin Blvd Gastonia NC 28052-4105 (704) 864-4371 xxxxxx Platform Bundle, ShelbyNext Financials (Hosted), ShelbyNext Membership, ShelbyNext Giving, ShelbyNext Website - Clover, V5
    8
    Lifegate Church 15555 W Dodge Rd Omaha NE 68154-2070 (402) 330-5724 xxxxxx V5
    9
    Second Presbyterian Church 404 N Prairie St Bloomington IL 61701 (309) 828-6297 xxxxxx ShelbyNext Financials (Hosted), ShelbyNext Giving, Platform Bundle, ShelbyNext Membership, ShelbyNext Website - Clover
    Sheet: Sheet2

  3. #3
    Registered User
    Join Date
    02-23-2021
    Location
    Tennessee
    MS-Off Ver
    O365
    Posts
    4

    Re: Transpose or Pivot??

    Your example looks like the result I am looking to achieve. So how was that accomplished?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,409

    Re: Transpose or Pivot??

    Look at the link I provided

  5. #5
    Registered User
    Join Date
    02-23-2021
    Location
    Tennessee
    MS-Off Ver
    O365
    Posts
    4

    Re: Transpose or Pivot??

    Sorry. Missed that first sentence. I did try this but get an error on the Measure process. My table name is "Table3". My formula is =CONCATENATEX(Table3,[Status],”, “). My error is: This formula is invalid or incomplete: 'The expression is not valid or appears to be incomplete. Please review and correct the expression.
    The following syntax error occurred during parsing: Invalid token, Line 1, Offset 30, ”.
    '.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,398

    Re: Transpose or Pivot??

    Hi gman and welcome to the forum,

    I have no idea what Dax is either, so I've done your problem with a helper column. First I did a Unique() on column 2 below your table. Then a helper to count how many of each in the last column.
    See the attached with the final answer below your table. Neither Transpose or Pivot but Unique and.xlsx Click on the link to the left.
    Hope this does what you need, and you can follow the logic. BTW - Unique() is only in the newer versions of Excel.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,409

    Re: Transpose or Pivot??

    here is my file
    Attached Files Attached Files
    Last edited by alansidman; 02-23-2021 at 11:55 PM.

  8. #8
    Registered User
    Join Date
    02-23-2021
    Location
    Tennessee
    MS-Off Ver
    O365
    Posts
    4

    Re: Transpose or Pivot??

    Boom. That got it! Much appreciated.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,409

    Re: Transpose or Pivot??

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. Macro needed to transpose data
    By Mr_Red in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2018, 06:43 AM
  2. Data Transpose Needed!
    By ImranEF in forum Excel General
    Replies: 1
    Last Post: 06-13-2017, 09:47 PM
  3. [SOLVED] Variation of IF formula help needed
    By moe1986 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2015, 12:02 PM
  4. [SOLVED] Date variation validation problem - a challenge needed help!
    By AlanY in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 11-10-2014, 09:47 AM
  5. Correction needed for transpose macro
    By genetist in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2014, 04:03 AM
  6. [SOLVED] Help needed in excel transpose
    By aswin_aditi@hotmail.com in forum Excel General
    Replies: 0
    Last Post: 05-02-2006, 02:30 AM
  7. variation to code needed
    By workingclassdog in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2005, 09:41 PM

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