Sequencing duplicates

balajigade · #1 10-03-2015, 08:56 AM

I have a table something like this :

mon a b c e
tue b a f a
wed b d a d
thu a c b f
fri d b c a
sat c b c e

I want a sequential counting of appearance of the values a,b,c,d & e in the second table in following pattern :

mon a-1 b-1 c-1 e-1
tue b-2 a-2 f-1 a-3
wed b-3 d-1 a-4 d-2
thu
fri
sat
--and so on---

How I manage this ?

shg · #2 10-03-2015, 10:36 AM

Code:

   -A- B C D E
1   mon a b c e
2   tue b a f a
3   wed b d a d
4   thu a c b f
5   fri d b c a
6   sat c b c e

Select B1, then run

Code:

Sub BG()
  Dim avInp         As Variant
  Dim i             As Long
  Dim j             As Long
 
  avInp = Range(Selection(1), Selection(1).End(xlToRight).End(xlDown)).Value
  With CreateObject("Scripting.Dictionary")
    .CompareMode = TextCompare
    For i = 1 To UBound(avInp, 1)
      For j = 1 To UBound(avInp, 2)
        .Item(avInp(i, j)) = .Item(avInp(i, j)) + 1
        avInp(i, j) = avInp(i, j) & "-" & .Item(avInp(i, j))
      Next j
    Next i
  End With
  Selection(1).Resize(UBound(avInp, 1), UBound(avInp, 2)).Value = avInp
End Sub

Code:

   -A- -B- -C- -D- -E-
1   mon a-1 b-1 c-1 e-1
2   tue b-2 a-2 f-1 a-3
3   wed b-3 d-1 a-4 d-2
4   thu a-5 c-2 b-4 f-2
5   fri d-3 b-5 c-3 a-6
6   sat c-4 b-6 c-5 e-2

balajigade · #3 10-04-2015, 01:33 AM

Thanks, it worked !!

shg · #4 10-04-2015, 03:31 PM

You're welcome.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA: Delete duplicates in each row	bandaanders	Excel Programming	2	09-02-2015 08:15 AM
Removing duplicates	saurabhlotankar	Excel Programming	14	05-26-2015 10:13 AM
Conditional Formatting Duplicates	Joanne	Excel	6	08-05-2013 02:46 AM
Mail duplicates	mixy	Outlook	0	02-10-2011 12:54 AM
sum of duplicates	zxmax	Excel	1	09-29-2006 08:29 PM