+ Reply to Thread
Results 1 to 10 of 10

Transpose text only data into table (like pivot table but with text only)

  1. 08-20-2015, 02:54 PM #1
    FelicityHall
    FelicityHall is offline
    Registered User
    Join Date
    08-20-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Transpose text only data into table (like pivot table but with text only)

    Hi

    I'm completely new to this forum and would love some help on something I'm sure is really simple but I can't get my head around it and can't find what I need on the forum.

    I have attached some basic sample data to illustrate:


    Thanks v much
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-20-2015, 03:17 PM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Transpose text only data into table (like pivot table but with text only)

    In L6 copied across and down, use this ARRAYED Formula

    =IFERROR(INDEX($A$6:$A$23, SMALL(IF($B$6:$B$23= L$5, ROW($A$6:$A$23)-ROW($A$6)+1), ROWS($A$1:$A1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 08-20-2015, 03:32 PM #3
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Transpose text only data into table (like pivot table but with text only)

    e5=iferror(INDEX($A$6:$C$23,match(E$5&row()-5,$C$6:$C$23,0)+Row()-6,1),"")

    and drag across.


    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  4. 08-20-2015, 03:42 PM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Transpose text only data into table (like pivot table but with text only)

    oeldere
    you threw an extra +ROW()-6 in there.
    Reply With QuoteRegister To Reply
  5. 08-20-2015, 03:51 PM #5
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Transpose text only data into table (like pivot table but with text only)

    @ChemistB

    Very good point. I used them for another approach and forget to remove them.

    e5=iferror(INDEX($A$6:$C$23,match(E$5&row()-5,$C$6:$C$23,0),1),"")


    Here is the updated result (file).
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 08-20-2015, 03:55 PM #6
    FelicityHall
    FelicityHall is offline
    Registered User
    Join Date
    08-20-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Re: Transpose text only data into table (like pivot table but with text only)

    Thanks so much ChemistB and oeldere. You have both solved my problem, and I realise I'm not as stupid as I thought as I would NEVER have figured it out on my own.

    Now to ponder why your solutions work....

    Thanks again.
    Reply With QuoteRegister To Reply
  7. 08-20-2015, 03:59 PM #7
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Transpose text only data into table (like pivot table but with text only)

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

    It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
    Reply With QuoteRegister To Reply
  8. 08-20-2015, 04:09 PM #8
    Czeslaw
    Czeslaw is offline
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,293

    Re: Transpose text only data into table (like pivot table but with text only)

    And if such a method?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 08-20-2015, 04:49 PM #9
    FelicityHall
    FelicityHall is offline
    Registered User
    Join Date
    08-20-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Re: Transpose text only data into table (like pivot table but with text only)

    Thanks again guys. Have added reputation and solved. I'll be back. You were brilliant.
    Reply With QuoteRegister To Reply
  10. 08-20-2015, 04:54 PM #10
    FelicityHall
    FelicityHall is offline
    Registered User
    Join Date
    08-20-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Re: Transpose text only data into table (like pivot table but with text only)

    Thanks also Czeslaw. That looks the most simple solution so far.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. All 0's on Pivot Table Sum, Data NOT text
    By eagarcia08 in forum Excel General
    Replies: 2
    Last Post: 02-24-2011, 08:59 AM
  2. Text in the data area of a pivot table
    By mkvassh in forum Excel General
    Replies: 4
    Last Post: 03-12-2009, 07:26 AM
  3. Text in Data area of Pivot table
    By sreenath205 in forum Excel General
    Replies: 1
    Last Post: 01-10-2007, 08:10 AM
  4. [SOLVED] Pivot table - Text in Data field
    By pum75 in forum Excel General
    Replies: 2
    Last Post: 11-17-2005, 11:50 AM
  5. [SOLVED] Text data appears as 0 in my Pivot table - how do I display as tex
    By Debra Dalgleish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 02:05 PM
  6. Text data appears as 0 in my Pivot table - how do I display as tex
    By Murray Snedden in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  7. Text data appears as 0 in my Pivot table - how do I display as tex
    By Murray Snedden in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  8. [SOLVED] Text data appears as 0 in my Pivot table - how do I display as tex
    By Murray Snedden in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1