turn vertical list to horizontal list start new row when change in column A, B and E

[drosew]

I have a list of data that I need to turn from vertical to horizontal... I would like to do it via VBA as I have about 40K lines. I need to have it combine all data for each number group with the same type, prod and G/NG code. So all that have the same type, Prod, G/NG and Number need to be in one column with all from the Code column in the row....

I attached a sample file as well.

For example

Starting with

Number|Type | Code |Prod |G/NG |
:----:|:----:|:----:|:----:|:----:|
440 AF 1234 S7 G
440 AF 7865 S7 G
440 NY 1235 S7 G
440 NY 4567 S7 G
41MM AF 1234 S7 G
41MM AF 1235 S7 NG

Should now look like:

Number|Type | Code1 | Code2| Prod |G/NG |
:----:|:----:|:----: |:----:|:-----:|:----:|
440 AF 1234 7865 S7 G

[jaslake]

Hi drosew

What would 41MM look like reconfigured?

[drosew]

41MM would have two rows

one for the G
one for the NG

41MM AF 1234 S7 G
41MM AF 1234 S7 NG

[CK76]

Something like this? See attached sample as well.

Please Login or Register  to view this content.

Edit:Forgot to remove "Debug.Print" line, you can remove it. It was just used to check logic.

[CK76]

Update: While testing code for 40k lines, realized that I forgot to mention following.
Above code will only work if Code variation per distinct Type, Prod & G/NG is 4 or less. If there could be more, you'll need to modify code, starting at "ReDim resArray..." line.

Updated code (added maxCode check and made resArray more dynamic, added timer code).

Please Login or Register  to view this content.

Note: If there could be duplicate code for distinct combo... you'll need additional check inside first loop.

[drosew]

That is great I have made a helper column that I hope will help, as I don't know the exact number there will be for each different code group. I'm going to attache the new file and see if that might give an better way to go about this. I have done code to add a blank row at each change in a column but not transpose....

[CK76]

You won't need helper column with logic used in my code sample.

Here, see modified code for your new column order (I removed the helper column), it will accommodate for varying number of code as well.

Please Login or Register  to view this content.