Clever Geek Handbook

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

+3


source to share


4 answers


  • Copy this formula into cell B1: =A1 & "-" & COUNTIF(A$1:A1;A1)

  • Drag content from B1 to the end of the dataselection.


+3


source


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.
0


source


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
0


source


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
0


source






More articles:


All Articles