Add new row by looping column (Scripting Dictionary)

[SamKhem]

Dear Senior member

I would like request you to guide coding vba with scripting dictionary to run loop in one column to create another row such
Loop column G is reference and F is amount to generate new column with add row in column L and M.

Thank in advance for your assist.
Best regards,

OLD						NEW
REFERENCE	AMOUNT					REFERENCE	AMOUNT
PL2308200035	-188.28					HD2308200035	188.28
						PL2308200035	-188.28
						REFERENCE	AMOUNT
AC2319900001	-100					AC2319900001	-100
PL2319900001	-0.02					HD2319900001	100
						PL2319900001	-0.02
						HD2319900001	0.02
						REFERENCE	AMOUNT
AC2319900009	120000					AC2319900009	120000
ACI2319900009	1787.8					HD2319900009	-120000
ACI2319900009	114.12					ACI2319900009	1787.8
						HD2319900009	-1787.8
						ACI2319900009	114.12
						HD2319900009	-114.12
						REFERENCE	AMOUNT
AC2319100027	-0.07					AC2319100027	-0.07
AC2319100027	0.07					PL2319100027	0.07
AC2319100027	100					AC2319100027	100
PL2319100027	0.07					HD2319100027	-100
						AC2319100027	0.07
						HD2319100027	-0.07
						REFERENCE	AMOUNT
AC2319100028	-0.76					AC2319100028	-0.76
PL2319100028	0.76					PL2319100028	0.76
AC2319100028	1000					HD2319100028	-1000
AC2319100028	0.83					AC2319100028	1000
PLF2319100028	0.05					HD2319100028	-0.83
						AC2319100028	0.83
						HD2319100028	-0.83
						PLF2319100028	0.05
						REFERENCE	AMOUNT
AC2319100029	10,000.00					HD2319100029	(10,000.00)
AC2319100029	23.64					AC2319100029	10,000.00
AC2319100029	(21.01)					HD2319100029	(23.64)
PL2319100029	21.01					AC2319100029	23.64
ACI2319100029	1.51					AC2319100029	(21.01)
PLF2319100029	0.01					PL2319100029	21.01
						HD2319100029	(1.51)
						ACI2319100029	1.51
						HD2319100029	(0.01)
						PLF2319100029	0.01

 

[Cubist]

1) Does it matter if the positive or the negative comes first?
2) I don't see a REF: PL2308200035 on the left side. Where did this come from? There are several instances where references on the right-hand side didn't come from the left
3) If there are multiple of instances the same amounts, how do you know which reference to choose from?

 

[SamKhem]

1) Does it matter if the positive or the negative comes first?
2) I don't see a REF: PL2308200035 on the left side. Where did this come from? There are several instances where references on the right-hand side didn't come from the left
3) If there are multiple of instances the same amounts, how do you know which reference to choose from?

Dear Cubist
1) Yes for sure, no matter - or + come first.
2) REF: PL2308200035 it just make it only one in column so it will create new columns with 2 rows value as above.
3) Yes I see, so just make left side ref. create to new reference for amount could be skip.

 

[Cubist]

1) Ok,
2) I don't understand I see PL23....35 comes from the OLD, where did HD23...35 come from?

 

[SamKhem]

1) Ok,
2) I don't understand I see PL23....35 comes from the OLD, where did HD23...35 come from?
View attachment 109503

it just create new value with another reference. so if old reference have one (PL......) it will create to new reference in HP.... and PL....

 

[Cubist]

it just create new value with another reference. so if old reference have one (PL......) it will create to new reference in HP.... and PL....

What's the rule for the first 2 characters?

 

[SamKhem]

What's the rule for the first 2 characters?

As you see number is the same so just add "HP" & in front to replace "PL". as I understand

 

[Cubist]

You mean you're not sure? If you're not sure, how would I know?

HP -> PL
What about AC, PLF, ACI, PL -> ???

Are these exhaustive or there are more?
Can't figure out how you're going from OLD to NEW. There are too many inconsistencies. Where is the offset negative for PLF...28?

 

[SamKhem]

You mean you're not sure? If you're not sure, how would I know?

HP -> PL
What about AC, PLF, ACI, PL -> ???

Are these exhaustive or there are more?
Can't figure out how you're going from OLD to NEW. There are too many inconsistencies. Where is the offset negative for PLF...28?

View attachment 109504

Sorry Sir, as left side (old reference: it could have first 2 characters (AC, ACI, PL, PLF) so when change to new reference it still keep the same just have another one partner with "HP"

 

[SamKhem]

You mean you're not sure? If you're not sure, how would I know?

HP -> PL
What about AC, PLF, ACI, PL -> ???

Are these exhaustive or there are more?
Can't figure out how you're going from OLD to NEW. There are too many inconsistencies. Where is the offset negative for PLF...28?

View attachment 109504

HD2319100028	-0.05​
PLF2319100028	0.05​