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
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
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" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali,
Thank you for your message.
I am using excel 2016 on a windows computer.
Br Peter
Then please update your profile, as requested. Thanks.
I did update my profile as per your request. Thanks.
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.
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?
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.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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))),"")
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks