Joe Reichsfeld
asked on
issues transposing data to multiple cells
I deal with massive amounts of data on a regular basis. With assistance here, I've been able to systematically simplify dealing with what could be an overwhelming nightmare, thanks.
I have a column of cells filled with multiple URLs per cell. I need to transpose them from one cell to multiple successive cells in the same row. Once it is transposed, I need to eliminate all blank cells shifting everything left and finally, any cell that begins with the word large and follows a cell that begins with the word hires should be deleted and everything shifted left. Two successive cells beginning with large but following a hires cell would only eliminate the first large cell. A large must follow a hires to be eligible for deletion
Seems easy but has been nothing of the sort. I can't get past the transpose. I've tried to add line breaks, find and replace, multiple different things but everything I do results in losing most of the data past the first entry
I've included an example with a macro I recorded. It copies the cell contents, goes to a blank worksheet, pastes them vertically, copies the results and transposes them back onto the original worksheet. Unfortunately, it doesn't work as it is too specific as to the contents of the cell
Cell Contents might look like this :
hiRes":"https://images-na.ssl-images-amazon.com/images/I/619YB71mfL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/41jexIwZeL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61EodGNNjL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/41ib6yY1wL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61bkOe2JYL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/51NDH6hruL.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/51tsIdIZbL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61mCsRqgoL._UL1000_.jpg
macro:
The first 3 or four lines of the sample file appear as they should after the actual transpose occurs
Any assistance is always appreciated.
imgBreakout.xlsm
I have a column of cells filled with multiple URLs per cell. I need to transpose them from one cell to multiple successive cells in the same row. Once it is transposed, I need to eliminate all blank cells shifting everything left and finally, any cell that begins with the word large and follows a cell that begins with the word hires should be deleted and everything shifted left. Two successive cells beginning with large but following a hires cell would only eliminate the first large cell. A large must follow a hires to be eligible for deletion
Seems easy but has been nothing of the sort. I can't get past the transpose. I've tried to add line breaks, find and replace, multiple different things but everything I do results in losing most of the data past the first entry
I've included an example with a macro I recorded. It copies the cell contents, goes to a blank worksheet, pastes them vertically, copies the results and transposes them back onto the original worksheet. Unfortunately, it doesn't work as it is too specific as to the contents of the cell
Cell Contents might look like this :
hiRes":"https://images-na.ssl-images-amazon.com/images/I/619YB71mfL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/41jexIwZeL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61EodGNNjL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/41ib6yY1wL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61bkOe2JYL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/51NDH6hruL.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/51tsIdIZbL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61mCsRqgoL._UL1000_.jpg
macro:
'breakout multiple URLs from one cell with many URLs to multiple cells in a row with one URL each
ActiveCell.FormulaR1C1 = _
"hiRes"":""https://images-na.ssl-images-amazon.com/images....removed to conserve space but this was to specific
Range("E1").Select
Sheets("Sheet2").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=1
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"hiRes"":""https://images-na.ssl-images-amazon.com/images/I....removed to conserve space but this was to specific
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Selection.Copy
Sheets("Sheet1").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=1
Range("D3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"hiRes"":""https://images-na.ssl-images-amazon.com/images/I/6....removed to conserve space but this was to specific
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Selection.Copy
Sheets("Sheet1").Select
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=1
The first 3 or four lines of the sample file appear as they should after the actual transpose occurs
Any assistance is always appreciated.
imgBreakout.xlsm
ASKER
I get 99% of the needed data directly from the platform account via an export report. I'm using content grabber2 and various other tools to scrape the image URLs after. The only info this marketplace doesn't provide back to sellers is image URLs. I need these to be able to upload their products across other platforms. Each cell is the total list of quality images from each listing. One listing per row, so I need them to go lengthwise in the same row. Then I can parse them further from there
I did figure out a somewhat dirty solution but it worked. Using find and replace I replaced all of the line feeds wit a comma and space, then saved it as a CSV. Upon reopening, I have 2 columns the first URL in column a and then the subsequent URLs beginning in b2 and going downward. I used Kutools to transpose the data to rows using the blanks from the first cell as the separator. slow and ugly but it worked
Still hoping to come out with a macro here though
I did figure out a somewhat dirty solution but it worked. Using find and replace I replaced all of the line feeds wit a comma and space, then saved it as a CSV. Upon reopening, I have 2 columns the first URL in column a and then the subsequent URLs beginning in b2 and going downward. I used Kutools to transpose the data to rows using the blanks from the first cell as the separator. slow and ugly but it worked
Still hoping to come out with a macro here though
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
Also, are you sure you want all the URLs in one row?
Wouldn't it be better to have them going down one column with a single URL in each cell?