Fining Vendor from list of products, VBA or Formula

[Mcaesar]

I have a spreadsheet of data that lists different vendors and the products they offer but i need to move the data to another one that is organized by Product so essentially i need to search through the data and find the product and then return the vendor name. My sheet looks like this

Vendor1 Vendor2 Vendor3
Product1 Product4 Product7
Product2 Product5 Product8
Product3 Product6 Product9

And i have to rearrange it by product so
Item Vendor Name
Product1 Vendor1
Product2 Vendor1
Product3 Vendor1
Product4 Vendor2

The issue is that i have a lot of 100s of products and multiple vendors. Either in VBA or A formula works fine for me.
Thanks

 

[JoeMo]

This will rearrange data starting in A1 in columns E & F:

Excel Workbook
	A	B	C	D	E	F
1	Vendor1	Vendor2	Vendor3		Product	Vendor
2	Product1	Product4	Product7		Product1	Vendor1
3	Product2	Product5	Product8		Product2	Vendor1
4	Product3	Product6	Product9		Product3	Vendor1
5					Product4	Vendor2
6					Product5	Vendor2
7					Product6	Vendor2
8					Product7	Vendor3
9					Product8	Vendor3
10					Product9	Vendor3
Sheet9

Sub RearrangeProductVendor()
Dim R As Range, nR As Long, i As Long, ct As Long
Range("E1:F1").Value = Array("Product", "Vendor")
Set R = Range("A1").CurrentRegion.Offset(1, 0).Resize(Range("A1").CurrentRegion.Rows.Count - 1)
Application.ScreenUpdating = False
For i = 1 To R.Columns.Count
    ct = R.Columns(i).Rows.Count
    nR = Range("E" & Rows.Count).End(xlUp).Row + 1
    With Range(Cells(nR, "E"), Cells(nR + ct - 1, "E"))
        .Value = R.Columns(i).Value
        .Offset(0, 1).Value = Columns(i).Cells(1, 1).Value
    End With
Next i
Application.ScreenUpdating = True
End Sub