Create new row subject to data in a field

josco

New Member
Joined
Sep 18, 2017
Messages
2
Hi everyone, I am new here as a member and hope you will be able to help.

I am trying to upload some data in a CSV to an online system which requires that each record is on a different line, but the data I have been given is all on one line, let me try to explain.

This is the data I have:
Prod IDProd DescImgSrc1ImgSrc2ImgSrc3
1234Useful Product12.jpg13.jpg14.jpg
5678Less Useful Item15.jpg16.jpg

<tbody>
</tbody>

And this is how I wish it to look:
Prod IDProd DescImgSrcImgNumber
123412.jpg1
123413.jpg2
123414.jpg3
567815.jpg1
567816.jpg2

<tbody>
</tbody>

Couple of points, I don't need all the other fields, just the Prod ID, ImgSrc and an incrementing number for each image associated with a Prod ID. Also some of the images may be used more than once, they are not unique.

I hope someone can help, I have been wrestling with this for a little while and as there are over 500 records, more later, I don't want to do this manually.

Best regards

Jonathan
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Put this in a new module and run it when positioned on your data sheet:

Code:
Public Sub josco001()

Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim thisRow As Long
Dim lastRow As Long
Dim thisCol As Long
Dim lastCol As Long
Dim nextRow As Long

' Set up the sheets
Set sourceSheet = ActiveSheet
Set targetSheet = Worksheets.Add(After:=sourceSheet)

' Set up the header row
targetSheet.Cells(1, 1).Value = "Prod ID"
targetSheet.Cells(1, 2).Value = "ImgSrc"
targetSheet.Cells(1, 3).Value = "ImgNumber"

' Find the last row of data
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row

' Next row to populate on the target sheet
nextRow = 2

' Process all rows
For thisRow = 2 To lastRow
    ' Make sure there's at least one image file
    If sourceSheet.Cells(thisRow, 3).Value <> "" Then
        ' Find the last column that contains an image file
        lastCol = sourceSheet.Cells(thisRow, sourceSheet.Columns.Count).End(xlToLeft).Column
        
        ' Process all image file columns
        For thisCol = 3 To lastCol
            ' Set the Prod ID, Image Source and Image Number
            targetSheet.Cells(nextRow, 1).Value = sourceSheet.Cells(thisRow, 1).Value
            targetSheet.Cells(nextRow, 2).Value = sourceSheet.Cells(thisRow, thisCol).Value
            targetSheet.Cells(nextRow, 3).Value = thisCol - 2
            
            ' Move to the next row
            nextRow = nextRow + 1
        Next thisCol
    End If
Next thisRow

End Sub

WBD
 
Upvote 0
WBD

Wow, I copied the code and placed it in a new module with the data I have been given expecting to have a bit of head scratching and probably coming back here to ask further help, but no, it just worked!

Thank you very much.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top