Hi Excel Guru's..need a vba to resolve this.
Below table is my Onedrive source Data Set residing in Sheet1 (Table 1 )
[TABLE="class: grid, width: 20, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]ST[/TD]
[TD]NAME[/TD]
[TD]Asset[/TD]
[TD]Qty[/TD]
[TD]UOM[/TD]
[TD]Asset2[/TD]
[TD]Qty2[/TD]
[TD]UOM2[/TD]
[TD]Asset3[/TD]
[TD]Qty3[/TD]
[TD]UOM3[/TD]
[TD]Asset4[/TD]
[TD]Qty4[/TD]
[TD]UOM4[/TD]
[TD]Asset5[/TD]
[TD]Qty5[/TD]
[TD]UOM5[/TD]
[TD]Req type[/TD]
[TD]Justy[/TD]
[TD]Enclose[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16-07-19[/TD]
[TD]xyz[/TD]
[TD]top[/TD]
[TD]5[/TD]
[TD]100[/TD]
[TD]rock[/TD]
[TD]8[/TD]
[TD]80[/TD]
[TD]soft[/TD]
[TD]10[/TD]
[TD]110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Form[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]16-07-19[/TD]
[TD]abc[/TD]
[TD]rock-h[/TD]
[TD]9[/TD]
[TD]40[/TD]
[TD]soft[/TD]
[TD]6[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Email[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
I want a vba to convert above data set into below format (Table 2) pasting in same book under sheet2, but only for new entries done in Table 1
Sub ID will be generated based on Asset column : if value exists in Asset2 or Asset3, a new row is generatd below under same ID. If Value is Null in Asset2 or Asset3 then the array moves to next ID item line.
[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Sub ID[/TD]
[TD]ST[/TD]
[TD]NAME[/TD]
[TD]Asset[/TD]
[TD]Qty[/TD]
[TD]UOM[/TD]
[TD]Req type[/TD]
[TD]Justy[/TD]
[TD]Enclose[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.1[/TD]
[TD]16-07-19[/TD]
[TD]xyz[/TD]
[TD]top[/TD]
[TD]5[/TD]
[TD]100[/TD]
[TD]Form[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1.2[/TD]
[TD]16-07-19[/TD]
[TD]xyz[/TD]
[TD]rock[/TD]
[TD]8[/TD]
[TD]80[/TD]
[TD]Form[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1.3[/TD]
[TD]16-07-19[/TD]
[TD]xyz[/TD]
[TD]soft[/TD]
[TD]10[/TD]
[TD]110[/TD]
[TD]Form[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.1[/TD]
[TD]16-07-19[/TD]
[TD]abc[/TD]
[TD]rock-h[/TD]
[TD]9[/TD]
[TD]40[/TD]
[TD]Email[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2.2[/TD]
[TD]16-07-19[/TD]
[TD]abc[/TD]
[TD]soft[/TD]
[TD]6[/TD]
[TD]70[/TD]
[TD]Email[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
Using VBA MACRO Button.
I appreciate your time and efforts.
Please write if you find any difficulty to understand.
Regards
Centaur
Below table is my Onedrive source Data Set residing in Sheet1 (Table 1 )
[TABLE="class: grid, width: 20, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]ST[/TD]
[TD]NAME[/TD]
[TD]Asset[/TD]
[TD]Qty[/TD]
[TD]UOM[/TD]
[TD]Asset2[/TD]
[TD]Qty2[/TD]
[TD]UOM2[/TD]
[TD]Asset3[/TD]
[TD]Qty3[/TD]
[TD]UOM3[/TD]
[TD]Asset4[/TD]
[TD]Qty4[/TD]
[TD]UOM4[/TD]
[TD]Asset5[/TD]
[TD]Qty5[/TD]
[TD]UOM5[/TD]
[TD]Req type[/TD]
[TD]Justy[/TD]
[TD]Enclose[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16-07-19[/TD]
[TD]xyz[/TD]
[TD]top[/TD]
[TD]5[/TD]
[TD]100[/TD]
[TD]rock[/TD]
[TD]8[/TD]
[TD]80[/TD]
[TD]soft[/TD]
[TD]10[/TD]
[TD]110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Form[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]16-07-19[/TD]
[TD]abc[/TD]
[TD]rock-h[/TD]
[TD]9[/TD]
[TD]40[/TD]
[TD]soft[/TD]
[TD]6[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Email[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
I want a vba to convert above data set into below format (Table 2) pasting in same book under sheet2, but only for new entries done in Table 1
Sub ID will be generated based on Asset column : if value exists in Asset2 or Asset3, a new row is generatd below under same ID. If Value is Null in Asset2 or Asset3 then the array moves to next ID item line.
[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Sub ID[/TD]
[TD]ST[/TD]
[TD]NAME[/TD]
[TD]Asset[/TD]
[TD]Qty[/TD]
[TD]UOM[/TD]
[TD]Req type[/TD]
[TD]Justy[/TD]
[TD]Enclose[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.1[/TD]
[TD]16-07-19[/TD]
[TD]xyz[/TD]
[TD]top[/TD]
[TD]5[/TD]
[TD]100[/TD]
[TD]Form[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1.2[/TD]
[TD]16-07-19[/TD]
[TD]xyz[/TD]
[TD]rock[/TD]
[TD]8[/TD]
[TD]80[/TD]
[TD]Form[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1.3[/TD]
[TD]16-07-19[/TD]
[TD]xyz[/TD]
[TD]soft[/TD]
[TD]10[/TD]
[TD]110[/TD]
[TD]Form[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.1[/TD]
[TD]16-07-19[/TD]
[TD]abc[/TD]
[TD]rock-h[/TD]
[TD]9[/TD]
[TD]40[/TD]
[TD]Email[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2.2[/TD]
[TD]16-07-19[/TD]
[TD]abc[/TD]
[TD]soft[/TD]
[TD]6[/TD]
[TD]70[/TD]
[TD]Email[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
Using VBA MACRO Button.
I appreciate your time and efforts.
Please write if you find any difficulty to understand.
Regards
Centaur