+ Reply to Thread
Results 1 to 10 of 10

Various Vendors in columns per row

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    Netherlands Rotterdam
    MS-Off Ver
    Excel 2016
    Posts
    13

    Various Vendors in columns per row

    In the enclosed file I would like to see 1 item per row and the potential vendors showing in columns ( preferred)
    instead of the situation NOW showing an item and vendor per row.

    Thanks in advance for your help.

    Peter
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,188

    Re: Various Vendors in columns per row

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    09-14-2012
    Location
    Netherlands Rotterdam
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Various Vendors in columns per row

    Hi Ali,

    Thank you for your message.
    I am using excel 2016 on a windows computer.

    Br Peter

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,188

    Re: Various Vendors in columns per row

    Then please update your profile, as requested. Thanks.

  5. #5
    Registered User
    Join Date
    09-14-2012
    Location
    Netherlands Rotterdam
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Various Vendors in columns per row

    I did update my profile as per your request. Thanks.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,298

    Re: Various Vendors in columns per row

    The pivot table on the PT sheet is produced using Power Pivot which is included with the 2016 version of Excel.
    1. Another column (E) is added to the source data, populated using: ="Vendor "&COUNTIFS(A$2:A2,A2)
    2. After the table is added to the data model the following measure is produced: List of Vendor:=CONCATENATEX(Table1,Table1[Vendor],",")
    3. In the pivot table column E is placed in the Columns area and the measure is placed in the Values area
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    09-14-2012
    Location
    Netherlands Rotterdam
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Various Vendors in columns per row

    Thank you very much for your solution, it is exactly what I needed.

    I tried all kind of formules but never thought of the pivot table. This is most probably due to my inexperience with pivot tables.
    Can you please explain the measure you created in point 2.

    How did you create this and what exactly does it do?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,976

    Re: Various Vendors in columns per row

    Another option (formula):

    Unique ID (copy down):
    =IFERROR(INDEX(Now!$A$2:$A$40,MATCH(0,INDEX(COUNTIF($A$1:$A1,Now!$A$2:$A$40),0),0)),"")

    Name & display name (copy across & down)
    =IFERROR(VLOOKUP($A2,Now!$A:$C,COLUMNS($A:B),FALSE),"")

    Vendors (copy across and down):
    =IFERROR(INDEX(Now!$D:$D,AGGREGATE(15,6,ROW(Now!$A$2:$A$40)/(Now!$A$2:$A$40=$A2),COLUMNS($A2:A2))),"")

    It can be made fully dyanmic by adding a Named Range to auto-adjust the range selected in column A of the raw data.
    Attached Files Attached Files
    Glenn



  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,976

    Re: Various Vendors in columns per row

    Named Range called ID (CTRL-F3 to view/edit):
    =$A$2:INDEX($A:$A,MATCH(10^100,$A:$A))

    ID formula then becomes:
    =IFERROR(INDEX(ID,MATCH(0,INDEX(COUNTIF($A$1:$A1,ID),0),0)),"")

    and vendors formula becomes:
    =IFERROR(INDEX(Now!$D:$D,AGGREGATE(15,6,ROW(ID)/(ID=$A2),COLUMNS($A2:A2))),"")
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,298

    Re: Various Vendors in columns per row

    As per producing a measure:
    1. Select a cell in the data on the Now sheet
    2. Press the Ctrl and t keys to convert the data to a table
    3. Add the Vendor# column as described in post #6
    4. From the Power Pivot tab on the ribbon select Add to Data Model
    5. On the Power Pivot sheet select a cell below the grey line and then paste the List of Vendor:=CONCATENATEX(Table1,Table1[Vendor],",") formula into the formula bar and press the Enter key.
    Usually when a text field like Vendor (column D) is dragged into the Values area of a pivot table the default will be to display a count. On the other hand when the List of Vendor function is dragged into the Values area, vendor names are displayed.
    I hope that makes sense. Let us know if you have any questions.

+ 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] Help with Charting different dates for Vendors
    By vvillanueva in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-02-2020, 02:05 PM
  2. xls comparison of management vendors
    By rjburnes in forum Excel General
    Replies: 1
    Last Post: 02-21-2020, 03:32 AM
  3. [SOLVED] Lookup and Match UPC for 2 Different Vendors
    By kbmhc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-25-2019, 10:49 AM
  4. Summarizing A List of Vendors
    By saq7792 in forum Excel General
    Replies: 1
    Last Post: 10-30-2017, 05:14 PM
  5. Replies: 6
    Last Post: 11-27-2013, 10:28 PM
  6. Analysisi the Top 26 vendors from among all on a new sheet.
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-22-2013, 11:21 PM
  7. Minimization of numbers of vendors
    By Jowi7991 in forum Excel General
    Replies: 5
    Last Post: 06-18-2012, 09:50 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1