Hi all,
For each cell in column B, I want to count how many cells in that column have the same value. If there is only one cell in the column with that value, change the Cell.Value to Cell.Value & "a", if there are two cells in the column with that value, change the Cell.Value to Cell.Value & "b", and so on.
This ensures each cell will have a different string, despite having the same start to the value, which should still be recognisable. The code I have written works, however it takes a really long time to run. Can anyone suggest a smarter way of writing it which will cut down on run time?
LRD = ThisWorkbook.Worksheets("Data").Range("B" & Rows.Count).End(xlUp).Row 'Endpoint of Column B in Data tab
For Each Cell In ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD)
If WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD), Cell.Value) = 1 Then Cell.Value = Cell.Value & "a"
If WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD), Cell.Value) = 2 Then Cell.Value = Cell.Value & "b"
If WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD), Cell.Value) = 3 Then Cell.Value = Cell.Value & "c"
If WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD), Cell.Value) = 4 Then Cell.Value = Cell.Value & "d"
Next Cell
Background: I have a list of order numbers (i.e. 12345), sometimes the order numbers occur multiple times (i.e. 12783, 12783, 12784). I want to differentiate them so later when I VLOOKUP a corresponding value I still can pull all information, rather than just one value which may only apply to one of the order numbers. So alternatively is there another way of doing this, without renaming the order numbers?
Bonus: Is there a way to simplify the code so it would work for almost infinite amounts of identical values? The equivalent of going to "Cell.Value & "Z"" or "Cell.Value & "AF"" for example (without having to write that many lines)?
Thanks in advance!
For each cell in column B, I want to count how many cells in that column have the same value. If there is only one cell in the column with that value, change the Cell.Value to Cell.Value & "a", if there are two cells in the column with that value, change the Cell.Value to Cell.Value & "b", and so on.
This ensures each cell will have a different string, despite having the same start to the value, which should still be recognisable. The code I have written works, however it takes a really long time to run. Can anyone suggest a smarter way of writing it which will cut down on run time?
LRD = ThisWorkbook.Worksheets("Data").Range("B" & Rows.Count).End(xlUp).Row 'Endpoint of Column B in Data tab
For Each Cell In ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD)
If WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD), Cell.Value) = 1 Then Cell.Value = Cell.Value & "a"
If WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD), Cell.Value) = 2 Then Cell.Value = Cell.Value & "b"
If WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD), Cell.Value) = 3 Then Cell.Value = Cell.Value & "c"
If WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Data").Range("B2:B" & LRD), Cell.Value) = 4 Then Cell.Value = Cell.Value & "d"
Next Cell
Background: I have a list of order numbers (i.e. 12345), sometimes the order numbers occur multiple times (i.e. 12783, 12783, 12784). I want to differentiate them so later when I VLOOKUP a corresponding value I still can pull all information, rather than just one value which may only apply to one of the order numbers. So alternatively is there another way of doing this, without renaming the order numbers?
Bonus: Is there a way to simplify the code so it would work for almost infinite amounts of identical values? The equivalent of going to "Cell.Value & "Z"" or "Cell.Value & "AF"" for example (without having to write that many lines)?
Thanks in advance!