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".

color name <tbody> </tbody>	code1 <tbody> </tbody>	code2 <tbody> </tbody>	code3 <tbody> </tbody>
Absolute Zero Acajou Acid green Aero Aero blue African violet Air Force blue (RAF) Air Force blue (USAF) Air superiority blue Alabama crimson <tbody> </tbody>	#0048BA #4C2F27 68768 #7CB9E8 #C9FFE5 12863 #5D8AA8 #00308F #72A0C1 #AF002A <tbody> </tbody>	fax 79% 70% bucks 69% <tbody> </tbody>	18% man 434436 <tbody> </tbody>

<tbody>
</tbody>

color name <tbody> </tbody>	code <tbody> </tbody>
Absolute Zero Acajou Acajou Acajou Acid green Aero Aero Aero blue African violet African violet African violet Air Force blue (RAF) Air Force blue (USAF) Air Force blue (USAF) Air superiority blue Alabama crimson Alabama crimson Alabama crimson <tbody> </tbody>	#0048BA #4C2F27 fax 18% 68768 #7CB9E8 79% #C9FFE5 12863 70% man #5D8AA8 #00308F bucks #72A0C1 #AF002A 69% 434436 <tbody> </tbody>

<tbody>
</tbody>

 

[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.

12/28/2020 AEPEXTDR 6 AEP 242953	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12/28/2020 AEPEXTDR 7 AEP 243828	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12/28/2020 AEPG18CC 1 AEP 247264	0	0	0	0	0	285.05	573.01	573.01	573.01	573.01	573.01	573.01	303.46	285.05	0	285.05
12/28/2020 AEPG18CC 2 AEP 247267	0	0	0	0	0	0	573.01	573.01	573.01	573.01	573.01	285.05	285.05	0	285.05	285.05
12/28/2020 AEPLTDDR 1 AEP 242533	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

<colgroup><col><col span="16"></colgroup><tbody>
</tbody>