Transpose values in column to rows, and split cell values to extend column

This one's a little weird, so I apologize in advance, but I can't set up a formula to do it for me reliably. I've attached the working file, the 'START' tabs are samples of the tabs that I receive, and the 'COMPLETE' tabs are those tabs as I need them. Currently this restructuring is a manual process, and these sheets are typically x times longer, so it's a laborious process. I need a VB macro to speed up this reconstruction, and I'm about as good with Excel macros as I am with braille.

I may be convinced to pay for the macro that solves this issue for me, as it would speed up portions of my day quite a bit. I think the tabs are fairly clear, but let me know if they aren't.

SKU SHEET.xlsx

Hi,

I'm not entirely sure what you want done here? You want the cells split by the hyphens? so "09778GRY-A" would become "09778GRY" and "A"?

Then the data transposed?

Please show a subset of the data (i.e. 3 or four rows) with what you want as the end result in a separate sheet. I'll then try to offer any help I can.

Alex

Hi..
This will get you 1/2 of your problems solved.. (and quite fast I must say.. ) In the attached Workbook... Click the blue "Click!" button on the "PRICE LIST START" sheet and see the result on "Sheet1".

If i get time.. I will look at the other half of your problem..

Please Login or Register  to view this content.

Originally Posted by alex.harrison

Hi,

I'm not entirely sure what you want done here? You want the cells split by the hyphens? so "09778GRY-A" would become "09778GRY" and "A"?

Then the data transposed?

Please show a subset of the data (i.e. 3 or four rows) with what you want as the end result in a separate sheet. I'll then try to offer any help I can.

Alex

Within the workbook there are 4 tabs. The two 'START' tabs are how the tabs are given to me, and the 'COMPLETE' tabs are how I need the 'START' tabs to be structured in their final state. The red comments gives a brief idea what needs to be done, but comparing the 'START' and 'COMPLETE' tabs make it fairly clear what needs to be done.

Originally Posted by apo

Hi..
This will get you 1/2 of your problems solved.. (and quite fast I must say.. ;) ) In the attached Workbook... Click the blue "Click!" button on the "PRICE LIST START" sheet and see the result on "Sheet1".

If i get time.. I will look at the other half of your problem..

Wonderful, that works perfectly, Apo. I hope you find time for the second half. :) :P

Hey sktneer, thanks for that. Slight problem though, and it's my fault: your script doesn't take into consideration there being more than a maximum of 3 sub-SKUs. In other working examples I've got more, and while it seems to push the value columns over, the header row only extend 3 out. This is my fault, though, my example material doesn't have any of these.

@mike7952...

I tested both your subs and they work well...

Sorry for the lack of response guys, I've been swamped.

mike, your subs seem to work (yay!), but they seem dependent on the sheets as they are in the example being named as they are and existing in worksheets outside of the example. This is my fault, but the example sheet names and which exist are really just to show what I get and what I need it to become.

So my working sheets will likely not have "X LIST START" and "X LIST COMPLETE" sheets, and will most likely be different names.

Ideally I'd like the script to act on the current active sheet, and create a new sheet entirely of the completed modification.

I changed sktneer's to act with ActiveSheet.Name & " (Modified)" or something to make it more adaptable, but I wasn't sure how to incorporate that change into your subs.