smart row generator based on columns

[modiria50989]

Hello,

Is there any Excel tool or VBA code to the following efficiently, thank you in advance:

I have a data sheet including a column of color names and a few columns of color codes in front of that. some colors have only 1 code, some 2, and some 3 codes. I need an efficient way that creates new row under a color that has 2 codes, and creates 2 new rows under a color that has 3 codes with the same color name. Unfortunately I don't see an option in this forum to attach my excel sheet. Anyway, for example, the date sheet (4-column) bellow is given and I need to get the second one (2-columns).

Just in case, in average, I'm dealing with a data sheet of "2000 Rows - 50 Columns".


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]color name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65, width: 61"]code1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 43"]
<tbody>[TR]
[TD="class: xl65, width: 43"]code2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 43"]
<tbody>[TR]
[TD="class: xl65, width: 43"]code3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD]Absolute Zero[/TD]
[/TR]
[TR]
[TD]Acajou[/TD]
[/TR]
[TR]
[TD]Acid green[/TD]
[/TR]
[TR]
[TD]Aero[/TD]
[/TR]
[TR]
[TD]Aero blue[/TD]
[/TR]
[TR]
[TD]African violet[/TD]
[/TR]
[TR]
[TD]Air Force blue (RAF)[/TD]
[/TR]
[TR]
[TD]Air Force blue (USAF)[/TD]
[/TR]
[TR]
[TD]Air superiority blue[/TD]
[/TR]
[TR]
[TD]Alabama crimson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD]#0048BA[/TD]
[/TR]
[TR]
[TD]#4C2F27[/TD]
[/TR]
[TR]
[TD]68768[/TD]
[/TR]
[TR]
[TD]#7CB9E8[/TD]
[/TR]
[TR]
[TD]#C9FFE5[/TD]
[/TR]
[TR]
[TD]12863[/TD]
[/TR]
[TR]
[TD]#5D8AA8[/TD]
[/TR]
[TR]
[TD]#00308F[/TD]
[/TR]
[TR]
[TD]#72A0C1[/TD]
[/TR]
[TR]
[TD]#AF002A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 43"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]fax[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]79%[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]70%[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]bucks[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]69%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]18%[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]man[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]434436[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




















[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]color name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65, width: 61"]code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD]Absolute Zero[/TD]
[/TR]
[TR]
[TD]Acajou[/TD]
[/TR]
[TR]
[TD]Acajou[/TD]
[/TR]
[TR]
[TD]Acajou[/TD]
[/TR]
[TR]
[TD]Acid green[/TD]
[/TR]
[TR]
[TD]Aero[/TD]
[/TR]
[TR]
[TD]Aero[/TD]
[/TR]
[TR]
[TD]Aero blue[/TD]
[/TR]
[TR]
[TD]African violet[/TD]
[/TR]
[TR]
[TD]African violet[/TD]
[/TR]
[TR]
[TD]African violet[/TD]
[/TR]
[TR]
[TD]Air Force blue (RAF)[/TD]
[/TR]
[TR]
[TD]Air Force blue (USAF)[/TD]
[/TR]
[TR]
[TD]Air Force blue (USAF)[/TD]
[/TR]
[TR]
[TD]Air superiority blue[/TD]
[/TR]
[TR]
[TD]Alabama crimson[/TD]
[/TR]
[TR]
[TD]Alabama crimson[/TD]
[/TR]
[TR]
[TD]Alabama crimson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD]#0048BA[/TD]
[/TR]
[TR]
[TD]#4C2F27[/TD]
[/TR]
[TR]
[TD]fax[/TD]
[/TR]
[TR]
[TD]18%[/TD]
[/TR]
[TR]
[TD]68768[/TD]
[/TR]
[TR]
[TD]#7CB9E8[/TD]
[/TR]
[TR]
[TD]79%[/TD]
[/TR]
[TR]
[TD]#C9FFE5[/TD]
[/TR]
[TR]
[TD]12863[/TD]
[/TR]
[TR]
[TD]70%[/TD]
[/TR]
[TR]
[TD]man[/TD]
[/TR]
[TR]
[TD]#5D8AA8[/TD]
[/TR]
[TR]
[TD]#00308F[/TD]
[/TR]
[TR]
[TD]bucks[/TD]
[/TR]
[TR]
[TD]#72A0C1[/TD]
[/TR]
[TR]
[TD]#AF002A[/TD]
[/TR]
[TR]
[TD]69%[/TD]
[/TR]
[TR]
[TD]434436[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[modiria50989]

This time I'm getting run time error. Also, some of my original rows have values in the middle, the way your code designed count the first empty cells as well for output. Here is an example in which I consider 0 as empty cell (first I'm replacing them with " "). Note that the red words are located in one cell (first column). Just to know I have over 250,000 rows and 169 columns originally and because of empty cells my output would be around 900,000 rows and 2 columns.

[TABLE="width: 1183"]
<colgroup><col><col span="16"></colgroup><tbody>[TR]
[TD]12/28/2020 AEPEXTDR 6 AEP 242953[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12/28/2020 AEPEXTDR 7 AEP 243828[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12/28/2020 AEPG18CC 1 AEP 247264[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]285.05[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]303.46[/TD]
[TD]285.05[/TD]
[TD]0[/TD]
[TD]285.05[/TD]
[/TR]
[TR]
[TD]12/28/2020 AEPG18CC 2 AEP 247267[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]573.01[/TD]
[TD]285.05[/TD]
[TD]285.05[/TD]
[TD]0[/TD]
[TD]285.05[/TD]
[TD]285.05[/TD]
[/TR]
[TR]
[TD]12/28/2020 AEPLTDDR 1 AEP 242533[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0

[/TD]
[/TR]
</tbody>[/TABLE]