Excel formula help

[mogwai]

Hi,

I am trying to find the necessary formula to use that will allow me to connect an empty excel sheet to the products from the Prodcut list Excel file used to hold all the details about the products sold in our online shop. Basically I need to find the populated rows in the productid column and then repeat them with each currency cost of sale and sale price.

As the number of products change I cannot just select the column by clicking at the top as the range of products will need to repeat for each currency.

In the source spreadsheet the currencies are arranged horizontally and they need to be changed round to vertical

Source:
________| ______Sale Price_____|____ Cost of Sale____|
Product ID | USD | EUR | INR | CHF | USD | EUR | INR | CHF


Target:

Product ID | Currency Code | Cost of Sale | Sale Price

 

[mogwai]

I have given up with doing it in excel and will now be doing it in TSQL. As VBA / Macros just seem to be big pain in the backside.

 

[mogwai]

Good choice, if you need any help with the TSQL email is in trust

Thanks for the offer. I am ok with TSQL as my main role is a SQL DBA