How to increase values ββin one column depending on the values ββof another column
How can I increase the values ββin one column depending on the values ββin another column?
For ex
if this
A B
TS-1
TS-1
TS-1
TS-2
TS-2
TS-3
TS-3
TS-3
TS-3
it should appear as
A B
TS-1 TC-1-1
TS-1 TC-1-2
TS-1 TC-1-3
TS-2 TC-2-1
TS-2 TC-2-2
TS-3 TC-3-1
TS-3 TC-3-2
TS-3 TC-3-3
TS-3 TC-3-4
It should be like in column B depending on the number of values ββin columm A
source to share
It looks pretty straightforward.
Customization
- Have a variable valA that stores the number in colA.
- Have a variable valB that stores the right-most number to be printed in colB.
Algorithm
- Parsing text in a line.
- When valA is different from the previous line, you know that you should reset valB to 1.
- Print valB and increase it by 1.
- Continue parsing until you reach the end.
source to share
With good circumstances, you can compute a new value based on the old one by applying a function to the last one. In this case, you cannot write a pure formula / function, because the new value depends not only on the old one, but also on the number of elements of the group (1, 2, 3, ...) that belongs to.
So, you need to write a loop over the original items that keep track of the groups. When you see a "group," think of a "dictionary" - instead of tracking changes to the group ID using AND and a variable, let the dictionary work for you. Additional bonus: no dependence on the order.
In code:
Dim aSrc : aSrc = Split("TS-1 TS-1 TS-1 TS-2 TS-2 TS-3 TS-3 TS-3 TS-3")
WScript.Echo "aSrc:", Join(aSrc)
Dim aExp : aExp = Split("TC-1-1 TC-1-2 TC-1-3 TC-2-1 TC-2-2 TC-3-1 TC-3-2 TC-3-3 TC-3-4")
ReDim aRes(UBound(aSrc))
Dim dicN : Set dicN = CreateObject("Scripting.Dictionary")
Dim nRow : nRow = 0
Dim sInp
For Each sInp In aSrc
Dim aParts : aParts = Split(sInp, "-")
dicN(aParts(1)) = dicN(aParts(1)) + 1
aRes(nRow) = "TC-" & aParts(1) & "-" & dicN(aParts(1))
nRow = nRow + 1
Next
WScript.Echo "aExp:", Join(aExp)
WScript.Echo "aRes:", Join(aRes)
output:
aSrc: TS-1 TS-1 TS-1 TS-2 TS-2 TS-3 TS-3 TS-3 TS-3
aExp: TC-1-1 TC-1-2 TC-1-3 TC-2-1 TC-2-2 TC-3-1 TC-3-2 TC-3-3 TC-3-4
aRes: TC-1-1 TC-1-2 TC-1-3 TC-2-1 TC-2-2 TC-3-1 TC-3-2 TC-3-3 TC-3-4
source to share