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
- Copy this formula into cell B1:
=A1 & "-" & COUNTIF(A$1:A1;A1)
- Drag content from B1 to the end of the dataselection.
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.
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
You can also do it without VBA by using an extra column. Place the following formula in column C (starting at C2!):
= IF (A1A2,1, C1 + 1)
Note: this assumes that your list in column a is sorted!
Then use the following formula on column B:
= SUBSTITUTE (A2, "S", "C") & "-" & C2