Sort a Series of Dates and Add A Value at End for Repeated Values

[OilEconomist]

Thanks in advance for any suggestions.

How would I write the VBA Code to do the following:

I have a series of entries in a column which are in ascending order. When repeated I need to add a value to the first number and then the second, third, and so on. So for example, how do I write VBA code to go from the "Original" to the "Modified"

Original
Blue.2019.01.31
Blue.2019.01.31
Blue.2019.02.28
Blue.2019.03.31
Blue.2019.03.31
Blue.2019.03.31

Modified
Blue.2019.01.31_01
Blue.2019.01.31_02
Blue.2019.02.28
Blue.2019.03.31_01
Blue.2019.03.31_02
Blue.2019.03.31_03

 

[sandy666]

it's acceptable?

Original		Result
Blue.2019.01.31		Blue.2019.01.31_1
Blue.2019.01.31		Blue.2019.01.31_2
Blue.2019.02.28		Blue.2019.02.28_1
Blue.2019.03.31		Blue.2019.03.31_1
Blue.2019.03.31		Blue.2019.03.31_2
Blue.2019.03.31		Blue.2019.03.31_3

 

[MoshiM]

Thanks in advance for any suggestions.

How would I write the VBA Code to do the following:

I have a series of entries in a column which are in ascending order. When repeated I need to add a value to the first number and then the second, third, and so on. So for example, how do I write VBA code to go from the "Original" to the "Modified"

Original
Blue.2019.01.31
Blue.2019.01.31
Blue.2019.02.28
Blue.2019.03.31
Blue.2019.03.31
Blue.2019.03.31

Modified
Blue.2019.01.31_01
Blue.2019.01.31_02
Blue.2019.02.28
Blue.2019.03.31_01
Blue.2019.03.31_02
Blue.2019.03.31_03

You could try something LIKE this

Sub Append_Number()


Dim AR() As Variant, Date_Dictionary As New Dictionary, X As Long


'take advantage of Dictionaries/Collections only being able to have unique keys
'AR=
'AR is a 2d Array

T = 1


For X = LBound(AR, 1) To UBound(AR, 1)


    If Date_Dictionay.Exists(AR(X, 1)) Then 'Dictionaries can only have unique keys so use the dates as a key
                                         'and test if the date is in the dictionary
        
        AR(X - 1, 1) = AR(X - 1, 1) & "_" & Format(T, "00") 'if it already exists then edit the previous
        AR(X, 1) = AR(X - 1, 1) & "_" & Format(T + 1, "00") 'and edit the current
        
        T = T + 1
        
        Else 'If the date isn't already in the dictionary then add the date to the dictionary
        
        Date_Dictionary.Add Item, Item
        
        T = 1 'reset the value of T
        
    End If
    
Next X

'now write AR back to the sheet in the column you want
End Sub

 

[sandy666]

I understand (from clicked buttons ) you want to see How To...

you'll need PowerQuery aka Get&Transform

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DupCol = Table.DuplicateColumn(Source, "Original", "Original - Copy"),
    Index = Table.AddIndexColumn(DupCol, "Index", 0, 1),
    Group = Table.Group(Index, {"Original"}, {{"Count", each _, type table}}),
    InnerIndex = Table.AddColumn(Group, "Counter", each Table.AddIndexColumn([Count], "Index2", 1, 1)),
    RC = Table.RemoveColumns(InnerIndex,{"Count"}),
    Expand = Table.ExpandTableColumn(RC, "Counter", {"Index2"}, {"Index2"}),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Expand, {{"Index2", type text}}, "en-GB"),{"Original", "Index2"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Result")
in
    Merge[/SIZE]

 

[OilEconomist]

Thank you sandy666!

It is, but if there is only one of them, is there a way to just keep the original value. For example, Blue.2019.02.28 would not change. Also, could you have a 0 before the numbers? Thanks so much for your help!

 

[sandy666]

Did you try vba from another post?

with 0 will not be a problem but with Blue.2019.02.28 without _1 will be

Result

Blue.2019.01.31_01

Blue.2019.01.31_02

Blue.2019.02.28_01

Blue.2019.03.31_01

Blue.2019.03.31_02

Blue.2019.03.31_03

 

[Fazza]

this is like a recent thread - when only one there was no suffix
except data now in a column not a row as previously

[URL]https://www.mrexcel.com/forum/excel-questions/1101768-vba-renaming-identical-headers.html[/url]

 

[sandy666]

you can try this

Result

Blue.2019.01.31_01

Blue.2019.01.31_02

Blue.2019.02.28

Blue.2019.03.31_01

Blue.2019.03.31_02

Blue.2019.03.31_03

but M-code is much longer

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DupCol = Table.DuplicateColumn(Source, "Original", "Original - Copy"),
    Index = Table.AddIndexColumn(DupCol, "Index", 0, 1),
    Group = Table.Group(Index, {"Original"}, {{"Count", each _, type table}, {"CR", each Table.RowCount(_), type number}}),
    InnerIndex = Table.AddColumn(Group, "Counter", each Table.AddIndexColumn([Count], "Index2", 1, 1)),
    RC = Table.RemoveColumns(InnerIndex,{"Count"}),
    Expand = Table.ExpandTableColumn(RC, "Counter", {"Index2"}, {"Index2"}),
    Criteria = Table.AddColumn(Expand, "Custom", each if [CR] = 1 then null else [Index2]),
    ROC = Table.SelectColumns(Criteria,{"Original", "Custom"}),
    Type = Table.TransformColumnTypes(ROC,{{"Custom", type text}}),
    Zeroes = Table.AddColumn(Type, "Custom.1", each Text.PadStart([Custom],2,"0")),
    ROC1 = Table.SelectColumns(Zeroes,{"Original", "Custom.1"}),
    Prefix = Table.TransformColumns(ROC1, {{"Custom.1", each "_" & _, type text}}),
    Merge = Table.CombineColumns(Prefix,{"Original", "Custom.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result")
in
    Merge[/SIZE]

M-code is not optimized

 

[OilEconomist]

Thanks so much MochinM. Looks good, since I'm a novice at VBA coding, could you help me with how I would use it if the sheet is called "Files" and the column is j starting in row 3 and the lastrow to search I find and call it Files_Tab_LR.

 

[MoshiM]

Thanks so much MochinM. Looks good, since I'm a novice at VBA coding, could you help me with how I would use it if the sheet is called "Files" and the column is j starting in row 3 and the lastrow to search I find and call it Files_Tab_LR.

Does Files_Tab_LR represent a number or a string? Would going to the last used row in column J works instead?