Data Re-arranging for Index file

[22strider]

Hello Friends

I need to data in Table 1 into Table2. Data in Table2 will then be used as an index file for some other application process. I tried to explain what needs to be done here. Ideally a code should be able to do this. I know little bit of VBA but there are sections of this tasks that I cannot do.

Table 1 has the current sample data.
For producing result Table2 this needs to be done:
1) A new column ‘IsDelete’ is added
2) In Table 1 if ‘Child Part’ = ‘New Child Part’
a. in Table2 column ‘IsDelete’ will have ‘No’
3) In Table 1 if ‘Child Part’ <> ‘New Child Part’,
a. in Table2 column ‘IsDelete’ will have ‘Yes’
b. The corresponding ‘New Child Part’ from Table 1 will get added to Table2 along with ‘Qty’ and ‘Ref Desig’. And Column ‘IsDelete’ will have ‘No’


Table1
Parent Part Child Part Qty Ref Desig New Child Part
150-0000 500-681AB 1 C3 500-681AB
150-0000 500-103AB 1 C4 500-103AB
150-0000 500-222AB 1 C2 500-222
150-0000 500-154AB 1 R3 500-154
150-0000 500-155 1 R2 500-155LF

Table2
Parent Part Child Part Qty Ref Desig IsDelete
150-0000 500-681AB 1 C3 No
150-0000 500-103AB 1 C4 No
150-0000 500-222AB 1 C2 Yes
150-0000 500-154AB 1 R3 Yes
150-0000 500-155 1 R2 Yes
150-0000 500-222 1 C2 No
150-0000 500-154 1 R3 No
150-0000 500-155LF 1 R2 No

Thanks for your help
Rajesh

 

[patel45]

can you explain in others words ?

 

[22strider]

Table1
Parent Part Child Part Qty Ref Desig New Child Part
150-0000 500-681AB 1 C3 500-681AB
150-0000 500-103AB 1 C4 500-103AB
150-0000 500-222AB 1 C2 500-222
150-0000 500-154AB 1 R3 500-154
150-0000 500-155 1 R2 500-155LF


Table2
Parent Part Child Part Qty Ref Desig IsDelete
150-0000 500-681AB 1 C3 No
150-0000 500-103AB 1 C4 No
150-0000 500-222AB 1 C2 Yes
150-0000 500-154AB 1 R3 Yes
150-0000 500-155 1 R2 Yes
150-0000 500-222 1 C2 No
150-0000 500-154 1 R3 No
150-0000 500-155LF 1 R2 No

 

[22strider]

Hello Friends

I am posting tables again:

Excel 2007
	B	C	D	E	F
2			Table1
3	Parent Part	Child Part	Qty	Ref Desig	New Child Part
4	150-0000	500-681AB	1	C3	500-681AB
5	150-0000	500-103AB	1	C4	500-103AB
6	150-0000	500-222AB	1	C2	500-222
7	150-0000	500-154AB	1	R3	500-154
8	150-0000	500-155	1	R2	500-155LF
9
10			Table2
11	Parent Part	Child Part	Qty	Ref Desig	IsDelete
12	150-0000	500-681AB	1	C3	No
13	150-0000	500-103AB	1	C4	No
14	150-0000	500-222AB	1	C2	Yes
15	150-0000	500-154AB	1	R3	Yes
16	150-0000	500-155	1	R2	Yes
17	150-0000	500-222	1	C2	No
18	150-0000	500-154	1	R3	No
19	150-0000	500-155LF	1	R2	No
Sheet1