Stack Multiple Column in one column

[mtr980]

Hi,

i have 3167 columns that i want to stack in one column, please NOTE I NEED TO STACK ON TOP OF EACH AND NOT MERGE.

here is sample of it. I HAVE ATTACHED actul file as well. please click on link to download file. THANK YOU

A B C D
1 6 11 16
2 7 12 17
3 8 13 18
4 9 14 19
5 10 15 20



A
1
2
3
4
5
B
6
7
8
9
10
C
11
12
13
14
15
D
16
17
18
19
20

[samba_ravi]

A21

Please Login or Register  to view this content.

Try this and copy towards down

[mtr980]

Hi Siva,

Thank you sooo much. life saver

another question,wondering if you can help as well. pls see attached link


let say i want to generate varient of product for all combination please see below sample data.


VAR 1 VAR 2 VAR 3 COLOR 1 COLOR 2 COLOR 3 SIZE 1 SIZE 2
SKU512813 C D E Black Blue **** 34 36



Result

VAR C Color Black Size 34
C Black 36
D BLUE 34
D BLUE 36
E **** 34
E **** 36

in some cases will have colors and size only.

[mtr980]

Screen Shot 2017-01-20 at 3.17.24 pm.png attached is image showing raw data and result data.

[avk]

Suppose your data from "A1" to "I2"
In "K1" formula

Please Login or Register  to view this content.

and copy paste across.

[avk]

Suppose your data from "A1" to "I2"
In "K1" formula

Please Login or Register  to view this content.

and copy paste across.

[mtr980]

Thanks AVK, but formula you provided just transpose the data. please see attached imageScreen Shot 2017-01-20 at 3.17.24 pm.png. we need to convert data row data to 2 columns or 4 columns or 6 columns depends on variables. in first column it will just show variant name. let say Color, next column it will show list of colors and 3rd column will show Size and 4th column will show list of all sizes and and so on.

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Please show more example than those in your image

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Tony Valko]

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

[mtr980]

Sorry my bad, please see attached LINK for sample data.

i have 5000 rows of data in actual.


Thanks everone

[JohnTopley]

What is the result for SKU SKU305749? and is the selection a given (single) SKU?

[mtr980]

Jhon,

please see attached LINK showing result for SKU305749

Thanks

[JohnTopley]

Thank you for the new file. As there no standard sequence of data an VBA solution might be the only option.

Is reasonable (correct) to assume your sample covers all variations in terms of coding: i.e Var1 is a single character (A,B ...) , Sizes are numeric or S, M ,L nXL?

[mtr980]

This how the raw data looks like for one SKU Cup:C,D,E, Color: Black,Blue,****,Red,Wine Red Size: 34,36,38,40,42,44,46,48,50

This is just one type of raw data, 2nd SKU can be Just Color and Size only(MOST CASES ONLY 2 TYPES). What i am looking for is results of all possible variants for ecommerce site because i will be uploading csv file so data need to be sorted in columns and in same structure as showing in this LINK

Thanks

[mtr980]

here is the actual file with all the RAW Data formatting and i am showing one result format as well. here is the LINK

I hope this will change the scenerio.

[JohnTopley]

See the attached:

As I didn't know how you wanted the output, I have added a drop-down list of SKUs. Select and click RUN.

Please Login or Register  to view this content.

[mtr980]

Hi John,

Great work, almost there. instead of showing results for one sku, we need to show output for all SKUs in one go. i have attached sample file LINK here.

Please note. format of outcome and combination its generating is absolutly what we need. just to need to show output for all the SKUs in sheet1 in one go.

Thank you for all the time you putting in.

[JohnTopley]

Please Login or Register  to view this content.

[mtr980]

John working absolutely perfect. you saved me weeks of works. LEGEND.

I should have mention this before but on cheking i found that, website import format requires SKU to copy up to variation range. please see below sample, right now its showing SKU only one time and thn variations when we run the code. what change i have to do in code to copy SKU upto to relevant range.

SKU445705
SKU445705 Green XL
SKU445705 Green 2XL
SKU445705 Green 3XL
SKU445705 Green 4XL
SKU445705 Green 5XL

[JohnTopley]

If I understand you want SKU on every row ..: add highlighted line

Please Login or Register  to view this content.

[mtr980]

Working perfect. thank you so much John.

[JohnTopley]

Thank for the feedback and rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.