Code for getting repeat value

[amit_gupta123]

Hii All

I have an excel data in data is arranged citywise. City is mentioned in a cell and related data is mentioned underneath it. I just want city name appears in adjacent cell and number of times as data belongs to that city. Input data is in Sheet 1 and desired output is in Sheet2
Can you please help with a VBA code
Thanks in advance

 

[Marc L]

Hi,

here is formula forum, move to VBA Macros forum !

You forgot to explain the destination of data, same worksheet ?

You can start your code by activating Macro recorder
and do some Copy/Paste operations : you'll get a free code base !

Post it after in this thread and we'll see to optimize it …​

 

[Asheesh]

Hi Amit - Assuming the destination is the same worksheet...

You may try the below

Option Explicit


Sub test()

Dim InSht As Worksheet
Dim l_Row As Long
Dim n As Long

Set InSht = ThisWorkbook.Worksheets("Input") ' Change the sheet name as required

InSheet.Activate

l_Row = InSht.Cells(InSht.Rows.Count, "C").End(xlUp).Row ' searches for the last used row in column C

For n = 1 To l_Row

If UCase(Cells(n, 1).Value) Like "BRANCH*" Then

Cells(n + 1, 9).Value = Cells(n, 3).Value

Cells(n, 3).EntireRow.Delete

ElseIf Cells(n, 3).Value = "" Then Exit Sub

Else

Cells(n, 9).Value = Cells(n - 1, 9).Value

End If

Next n

End Sub

 

[Asheesh]

Hi Amit - there was an error...just fixed it..use the below


Option Explicit

Sub test()
Dim InSht As Worksheet
Dim l_Row As Long
Dim n As Long
Set InSht = ThisWorkbook.Worksheets("Input") ' Change the sheet name as required
InSht.Activate
l_Row = InSht.Cells(InSht.Rows.Count, "C").End(xlUp).Row ' searches for the last used row in column C
For n = 1 To l_Row
If UCase(Cells(n, 1).Value) Like "BRANCH*" Then
Cells(n + 1, 9).Value = Cells(n, 3).Value
Cells(n, 3).EntireRow.Delete
ElseIf Cells(n, 3).Value = "" Then Exit Sub
Else
Cells(n, 9).Value = Cells(n - 1, 9).Value
End If
Next n
End Sub