Hello
I am working on a spreadsheet, and need to convert a list of suppliers materials on different rows, so they are displayed in columns, if this makes sense
- I have attached a spreadsheet showing the issue.
I need to get the "Overview" sheet to list the materials each supplier supplies in horizontal order, I have done V lookups on the first two sheets, so the material supplied is on the "supplier and codes" tab and the supplier is on the "Codes and Materials" tab.
The reason behind doing this is so that I can send out an email (as a mail merge) and the supplier will recieve one email with a list of materials rather than several emails, one for each material.
Hope someone can help me... it would literally save me DAYS!!!!
Here,try this:
"Relax. What is mind? No matter. What is matter? Never mind!"
Try this:
In the first tab, D2 enter:
copied down.=B2&"_"&COUNTIF($B$2:B2,B2)
Then in the Overview tab insert a new column before column F and in the new Column F at F2 enter:copied down=COUNTIF('Supplier and Materials'!B:B,A2)
Then in G2:
copied down and across.=IF(COUNTIF($G$1:G$1,"*Code")>$F2,"",INDEX('Supplier and Materials'!$A:$C,MATCH($A2&"_"&COUNTIF($G$1:G$1,"*Code"),'Supplier and Materials'!$D:$D,0),IF(RIGHT(G$1,4)="Code",1,3)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks