+ Reply to Thread
Results 1 to 2 of 2

Taking a horizontal list with multiple values and converting it into a vertical list

  1. #1
    Registered User
    Join Date
    03-26-2019
    Location
    NYC
    MS-Off Ver
    2
    Posts
    3

    Taking a horizontal list with multiple values and converting it into a vertical list

    I have a data set where there are multiple Client Numbers associated with the same Client Name. An example is:

    Client Name Client Number 1 Client Number 2 Client Number 3
    10 Years 81946
    21 Savage (Shayaa Bin Abraham-Joseph) 116290 116288
    Aaron Bruch 120062
    Aaron Evans pka TA'EAST 85475 108124
    Alex Izquierdo (Hia Entertainment) 58161 117956 117873


    I want to take this data and convert it into a vertical list that is only two columns wide and the Client Name would be duplicated where there are multiple client numbers. The result would look like this:

    Client Name Client Number
    10 Years 81946
    21 Savage (Shayaa Bin Abraham-Joseph) 116290
    21 Savage (Shayaa Bin Abraham-Joseph) 116288
    Aaron Bruch 120062
    Aaron Evans pka TA'EAST 85475
    Aaron Evans pka TA'EAST 108124
    Alex Izquierdo (Hia Entertainment) 58161
    Alex Izquierdo (Hia Entertainment) 117956
    Alex Izquierdo (Hia Entertainment) 117873
    Last edited by mcfurlong1; 03-19-2020 at 02:35 PM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,541

    Re: Taking a horizontal list with multiple values and converting it into a vertical list

    Hello mcfurlong1 and Welcome to Excel Forum.
    This proposal employs two helper columns (E:F) which may be moved and/or hidden for aesthetic purposes. It also assumes that you have Excel version 2010 or later, please update your profile to indicate the year version of excel or 365 for the subscription version as different versions support different functions (makes it easier to help).
    Column E is populated using: =COUNT(B2:D2)
    Column F is populated using: =SUM(E2,F1)
    The output columns are populated using
    Client Name: =IFERROR(INDEX(A$2:A$6,AGGREGATE(15,6,(ROW(A$2:A$6)-ROW(A$1))/(F$2:F$6>=ROWS(A$1:A1)),1)),"")
    Client Numbers: =IF(H2="","",INDEX(B$2:D$6,MATCH(H2,A$2:A$6,0),COUNTIFS(H$2:H2,H2)))
    For future reference, you will usually get faster results if you utilize the instructions in the banner at the top of the page.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Unique Horizontal list from vertical list
    By juriemagic in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-27-2019, 08:18 AM
  2. Create vertical list from non uniform horizontal list
    By simonwait in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-31-2019, 08:36 PM
  3. [SOLVED] turn vertical list to horizontal list start new row when change in column A, B and E
    By drosew in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-13-2016, 01:13 PM
  4. Switching a Horizontal List to a Vertical List
    By mrvp in forum Excel General
    Replies: 2
    Last Post: 02-28-2012, 07:22 PM
  5. Converting vertical list into horizontal list
    By Frenchtom in forum Excel General
    Replies: 2
    Last Post: 09-12-2011, 03:55 PM
  6. [SOLVED] converting vertical data list to horizontal data list
    By tjb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-14-2006, 09:25 PM
  7. [SOLVED] Converting a large vertical mailing list into a horizontal format
    By Kevin VanHalen in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-30-2005, 08:07 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