3 column data into 1 column data by adding rows.

Hi,

Hope someone can help!

I have a C.4000 line list of data in 3 separate columns.
There are SKU numbers which are split into x3 types; "simples", "configs" & "grouped".

I need to find a way of taking the raw 3 column data & putting it into a single column however it needs to be in a specific order.

It needs to run;
simples
configs
grouped
simples
config
grouped
simples
config
grouped
etc.
etc.

The prefix for all the simples, configs & grouped are all the same, the suffix is different depending on the 'type'.

I've attached an example of the raw data + the end data (+ colour coded to make is easier to see what I'm trying to achieve).

I just don't know how to get from the raw data to the end data!

Ideally this needs to be a formula not a macro.

I hope I've made myself clear but please fire away any questions.

Thank you!!!

You could probably do this using a few helper columns. I'll take a more detailed look at it tomorrow.

Pete

Originally Posted by avk

Select data A2:C65 & define name as "DATA"
Enter formula in "E2" [Array formula : shift+ctrl+enter]

Formula:

Please Login or Register  to view this content.

copy down

Thanks Avk but unless I'm doing something wrong it's not working.

I understand how to Select data A2:C65 & define name as "DATA".
I also understand how to create an array formula {=IFERROR(INDEX(Data,1+INT((ROW(A2)-1)/COLUMNS(Data)),MOD(ROW(A2)-1+COLUMNS(Data),COLUMNS(Data))+1),"")}

This isn't however giving me the data I need.

My data should end up as follows;

SC-SO-3902-PNK-XS
SC-SO-3902-PNK-S
SC-SO-3902-PNK-M
SC-SO-3902-PNK-L
SC-SO-3902-PNK-XL
SC-SO-3902-PNK-XXL
SC-SO-3902-PNK_CON
SC-SO-3902-PNK_GRP
SC-HD-3424-SGE-XS
SC-HD-3424-SGE-S
SC-HD-3424-SGE-M
SC-HD-3424-SGE-L
SC-HD-3424-SGE-XL
SC-HD-3424-SGE-XXL
SC-HD-3424-SGE_CON
SC-HD-3424-SGE_GRP
SC-HD-3486-BLK-XS
SC-HD-3486-BLK-S
SC-HD-3486-BLK-M
SC-HD-3486-BLK-L
SC-HD-3486-BLK-XL
SC-HD-3486-BLK-XXL
SC-HD-3486-BLK_CON
SC-HD-3486-BLK_GRP

Using the formula you suggested I get this;
SC-SO-3902-PNK_CON
SC-SO-3902-PNK_GRP
SC-SO-3902-PNK-S
SC-HD-3424-SGE_CON
SC-HD-3424-SGE_GRP
SC-SO-3902-PNK-M
SC-HD-3486-BLK_CON
SC-HD-3486-BLK_GRP
SC-SO-3902-PNK-L
SC-JG-3432-SGE_CON
SC-JG-3432-SGE_GRP
SC-SO-3902-PNK-XL
SC-JG-3487-BLK_CON
SC-JG-3487-BLK_GRP
SC-SO-3902-PNK-XXL
SC-TS-3880-ONG_CON
SC-TS-3880-ONG_GRP
SC-HD-3424-SGE-XS
SC-JN-3542-BLK_CON
SC-JN-3542-BLK_GRP
SC-HD-3424-SGE-S
SC-JN-3913-LGHTD_CON
SC-JN-3913-LGHTD_GRP
SC-HD-3424-SGE-M

Have I missed something?

Originally Posted by Pete_UK

You could probably do this using a few helper columns. I'll take a more detailed look at it tomorrow.

Pete

Hi Pete_UK,

Yeah I was thinking a helper column might be the answer but I wasn't sure how to do this with different suffixes on each SKU or across multiple columns of data trying to convert into one single column in the right order.

If you have any suggestions, great!

The attached file produces the output that you require, but I have used five helper columns, all coloured blue.

The first helper is in column D, and this gives a unique identifier to each record in column A by adding a sequential number to the end of the SKU, following an underscore. This formula should be copied down at least to the bottom of the list that you have in column A, although I tend to copy a few rows further.

Column E produces a count of the number of records that exist in column A for each group that are shown in columns B/C, and column F shows the cumulative count, with column G also showing the cumulative count, but adding 2 extra on to each cumulative sum to account for the "configs" and "groups" rows that will be needed. It is important to have zero in F1 and G1, and the formulae in E:G only need to be copied down for as many records as you have in columns B/C (as before, I've copied a few rows further in the attached file).

Column H shows the row in columns B/C where the Prefix for the record can be located, and this is duplicated for as many records are needed (including the configs and group records). Finally, the formula in column I will reproduce the data in a single column in the order that you desire. The formulae in columns H and I will need to be copied down for as many rows as the highest number given in column G, though you can copy further to ensure that you have all the data. I have moved your original END DATA column to J, so that you can compare the results directly.

Hope this helps.

Pete

Originally Posted by Pete_UK

The attached file produces the output that you require, but I have used five helper columns, all coloured blue.

The first helper is in column D, and this gives a unique identifier to each record in column A by adding a sequential number to the end of the SKU, following an underscore. This formula should be copied down at least to the bottom of the list that you have in column A, although I tend to copy a few rows further.

Column E produces a count of the number of records that exist in column A for each group that are shown in columns B/C, and column F shows the cumulative count, with column G also showing the cumulative count, but adding 2 extra on to each cumulative sum to account for the "configs" and "groups" rows that will be needed. It is important to have zero in F1 and G1, and the formulae in E:G only need to be copied down for as many records as you have in columns B/C (as before, I've copied a few rows further in the attached file).

Column H shows the row in columns B/C where the Prefix for the record can be located, and this is duplicated for as many records are needed (including the configs and group records). Finally, the formula in column I will reproduce the data in a single column in the order that you desire. The formulae in columns H and I will need to be copied down for as many rows as the highest number given in column G, though you can copy further to ensure that you have all the data. I have moved your original END DATA column to J, so that you can compare the results directly.

Hope this helps.

Pete

Hi Pete_UK,
Sorry for the later reply.
I saw you'd commented on my other thread.

I'm still trying to get this bit to work and was certainly going to come back to you with my thanks once I'd got it working.

It certainly looks as if your solution will work however the example I gave in this thread was a very 'slimmed down' version of what I'm actually trying to achieve.
I posted a 'slimmed down' version to try to make it easier to understand the problem however the final spread sheet is far more complex.

The actual spread sheet I'm working on has loads & loads of column data that I'm trying to transpose from one worksheet into another in the specific order like my example.


For your info this is the whole problem...

We are running x2 separate software systems in our business.
The first piece of software is what we use to raise purchase orders & follow production with our factories.
The second piece of software is magento (not sure if you now it), which we use to run our consumer web sites.
There are A LOT of products and I need to find a way to export the raw data from our purchase orders, convert this in the right format & create a mass product import for our magento web sites.

There are x4 sheets in my master excel sheet.
'raw data' - This is the raw data as it's exported from our production software.
'summary sheet' - This is a list of data we cannot export from the production software but needs to be manually entered per product in order for the import to work.
'workings' - This sheet that takes all the raw data & summary sheet data, combines it into the right formats.
'export to csv' - This is the final sheet (this is the one my thread relates to), this needs to take the 'workings' sheet data & transpose it into the 'export to csv' sheet ready for import into magento.

Inside the 'workings' sheet you will see there are actually x6 groups of column data (each group containing 30 columns).
I've colour coded them in a similar way to the example I gave in my thread.
I then need to use the formula's you've kindly helped with to transpose not just the x3 columns of data in my example but (6x30) 180 columns of data from the 'workings' sheet, into the 'export to csv' sheet in the right order.

For this example I've simply copied & pasted the data into the 'export to csv' sheet so you can see the final order the data needs to be in.


It's a complex task I'm sure you'd agree!


p.s. regards my other threads.
I have other problems inside the 'workings' sheet which I also need to fix and don't know how to do it.
Therefore I started x2 other threads to try to solve these issues.

Thank you for your time!

The file is too large to attach to this thread, therefore see this dropbox link if you wanted to take a look;
https://www.dropbox.com/s/hgbyd0xrt4...load.xlsx?dl=0

Thanks for the more detailed explanation, Matthew. Many contributors are unwilling (or not allowed due to company firewalls etc.) to download files from 3rd party sites, so perhaps you can zip the file and see if that will attach here (or save it as an .xlsb format).

I think your requirements, though, go beyond what you might reasonably expect to get a solution for on a site which offers free advice and guidance, so you might get more responses if you submit the post to Commercial Services, using the link at the top of the screen. It strikes me that a VBA solution will be needed here, given the volume of data, and I am not in a position to offer any help in that direction.

Pete

Originally Posted by matthew_salter

Hi Pete_UK,
Sorry for the later reply.
I saw you'd commented on my other thread.

I'm still trying to get this bit to work and was certainly going to come back to you with my thanks once I'd got it working.

It certainly looks as if your solution will work however the example I gave in this thread was a very 'slimmed down' version of what I'm actually trying to achieve.
I posted a 'slimmed down' version to try to make it easier to understand the problem however the final spread sheet is far more complex.

The actual spread sheet I'm working on has loads & loads of column data that I'm trying to transpose from one worksheet into another in the specific order like my example.


For your info this is the whole problem...

We are running x2 separate software systems in our business.
The first piece of software is what we use to raise purchase orders & follow production with our factories.
The second piece of software is magento (not sure if you now it), which we use to run our consumer web sites.
There are A LOT of products and I need to find a way to export the raw data from our purchase orders, convert this in the right format & create a mass product import for our magento web sites.

There are x4 sheets in my master excel sheet.
'raw data' - This is the raw data as it's exported from our production software.
'summary sheet' - This is a list of data we cannot export from the production software but needs to be manually entered per product in order for the import to work.
'workings' - This sheet that takes all the raw data & summary sheet data, combines it into the right formats.
'export to csv' - This is the final sheet (this is the one my thread relates to), this needs to take the 'workings' sheet data & transpose it into the 'export to csv' sheet ready for import into magento.

Inside the 'workings' sheet you will see there are actually x6 groups of column data (each group containing 30 columns).
I've colour coded them in a similar way to the example I gave in my thread.
I then need to use the formula's you've kindly helped with to transpose not just the x3 columns of data in my example but (6x30) 180 columns of data from the 'workings' sheet, into the 'export to csv' sheet in the right order.

For this example I've simply copied & pasted the data into the 'export to csv' sheet so you can see the final order the data needs to be in.


It's a complex task I'm sure you'd agree!


p.s. regards my other threads.
I have other problems inside the 'workings' sheet which I also need to fix and don't know how to do it.
Therefore I started x2 other threads to try to solve these issues.

Thank you for your time!

The file is too large to attach to this thread, therefore see this dropbox link if you wanted to take a look;
https://www.dropbox.com/s/hgbyd0xrt4...load.xlsx?dl=0

Hi Pete_UK,

Do you think this is something you can help with.
I'm completely stuck.

You're solution was perfect, but I've been trying all afternoon to turn what you came up with into something I can use across multiple columns & rows.

If not, no problem & thank you anyway!

I just posted immediately before you - I've been out in the garden all day.

Pete

Originally Posted by Pete_UK

I just posted immediately before you - I've been out in the garden all day.

Pete

Ah yes, our messages crossed paths.

I fully understand it goes beyond 'free' advise, no problem, I'll try commercial.

I really had hoped to avoid VBA as editing in the future (with my reasonable, yet limited) knowledge then becomes a problem for me.

If it's the only solution then will have to look into it.

The file isn't letting me upload. It's 2.4Mb even for compresses.

Thank you anyway, I'll have to contact commercial.