Formula / Macro Help for Populating data and pulling in data

Hi

Im new to Excel Forums so please be gentle with me

I need some help

I have some research that i have been doing for work and have just been informed by the developers that they need the data in a certain format. For example i have researched the price of a beer like Budweiser from 6 supermarkets. I have put the SKU ''Budweiser'' in Cell A2 and have have put the web links to this product from the supermarkets in cells B2, C2, D2, E2, F2 and E2.

The developers need the research to go in all the same column. I.e. Cells A1 to A6 would be say ''Budweiser'' but the web links need to go in B1 to B6. I would then have to put the same for ''Heineken'' in cells A7 to A12 and have its corresponding web links in cells B7 to B12. This order would be the same for all the products. I.e. another 6 cells in Column A & B for another product

I need to do this for about 1,300 different products.

I only know the basics of Excel, so please any help would be really appreciated!!

If required i can attach an example, incase im not explaining it very well

Hi there,

Do all products have six links (columns) associated with them, or do some have more/fewer?

Regards,

Greg M

Hi there,

Take a look at the attached workbook (1,300 products) and see if it does what you need - it works on the basis that six links are provided for each product.

It uses the following code:

Option Explicit


Private Sub ColumnsToRows()

    Const sFIRST_DATA_CELL  As String = "A2"
    Const iNO_OF_LINKS      As Integer = 6

    Dim sProductName        As String
    Dim rFirstColumn        As Range
    Dim rDataRange          As Range
    Dim rDataRow            As Range
    Dim iNewRowNo           As Integer
    Dim iRowNo              As Integer
    Dim wks                 As Worksheet

    Set wks = ActiveSheet

    With wks
        Set rDataRange = Range(.Range(sFIRST_DATA_CELL), _
                               .UsedRange.Cells(.UsedRange.Cells.Count))
    End With

    Set rFirstColumn = rDataRange.Columns(1)

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

        For iRowNo = (rFirstColumn.Cells.Count) To 1 Step -1

            Set rDataRow = rFirstColumn.Cells(iRowNo, 1).EntireRow
            Set rDataRow = Intersect(rDataRow, _
                                     rDataRange)

'           Determine the name of the current product, and then display it in a
'           progress message on the StatusBar
            sProductName = rDataRow.Cells(1, 1).Value
            Application.StatusBar = "Processing product " & sProductName

'           The first link is not moved - it is already in its correct location
            For iNewRowNo = iNO_OF_LINKS To 2 Step -1

'               Insert a new row immediately below the currently-selected DataRow
                rDataRow.Offset(1, 0).EntireRow.Insert

'               Copy the product name to the first cell in the new data row
                rDataRow.Cells(2, 1).Value = sProductName

'               Copy the appropriate link to the second cell in the new data row
                rDataRow.Cells(2, 2).Value = rDataRow.Cells(1, iNewRowNo + 1).Value

'               Clear the cell which contains the value of the original link
                rDataRow.Cells(1, iNewRowNo + 1).Value = vbNullString

            Next iNewRowNo

        Next iRowNo

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.StatusBar = False

End Sub

The highlighted values can be altered to suit any future layout changes.

Hope this helps - please let me know how you get on.

Regards,

Greg M