How do I identify identical rows in Excel or VBA

[twinkle99]

I have a large data file which has thousands of rows of data.

In Column A is a text string which is the reference number followed by an element. In Column C is the amount of that element and Column D is the date of that element.

I have written a macro which reads through the data file and finds the data which is relevant to the reference number and date both of which are inputted on another sheet (not supplied).

The problem I have is that in Column A, there may be rows which are identical. The macro looks for the text string, however, I dont know how to get the Macro to distinguish between the identical rows - i.e. 1021#INCV.

An example of the VBA for one input is:

With Application.WorksheetFunction
a.Range("C10") = .Index(b.Range("C:C"), .Match(b.Range("C11").Value & "#INCV", s, 0))

C:C - is the result that I want - the amount
C11 - is the reference number
s - is Column A:A where the text string will be

Basically, the macro should find the text string, check whether the date in D:D is before the calculation date (input on another sheet) and if it isnt, it should move to the next string and check the date etc until it finds the one which is before. However, it doesnt work because the macro just finds the first line every time (because the other INCV's are identical)

Is there a way of either writing an excel formula in Column B which will rename the identical rows when they occur i.e. 1021#INCV2, then 1021#INCV3 etc. Note that the elements for each member can appear in any row and are not necessarily in the same place, The dates and amounts will also be different. So members may return more rows than others. If this is possible I can just look for the different text strings in the macro.

Alternatively, is there a way of doing something in the Macro?

I have supplied a very short piece of the data file.

Thanks

vba test.xls
	A	B	C	D
1	1021#INCV		1600	18/09/2005
2	1021#CA04		20	18/09/2005
3	1021#ADP		620	18/09/2005
4	1021#INCV		1452	20/09/2004
5	1021#CA04		15	20/09/2004
6	1021#ADP		88	20/09/2004
7	1021#CA04		90	17/09/2003
8	1021#ADP		33	17/09/2003
9	1021#INCV		1200	17/09/2003
10	1021#INCV		1166	16/08/2002
11	1021#INCV		1020	15/08/2001
12	1021#INCV		950	16/01/2000
13	1569#INCV		1560	18/09/2005
14	1569#CA05		200	18/09/2005
15	1569#ADP		615	18/09/2005
16	1569#INCV		1500	20/09/2004
17	1569#CA05		150	20/09/2004
18	1569#ADP		595	20/09/2004
19	1569#CA05		90	17/09/2003
20	1569#ADP		500	17/09/2003
21	1569#INCV		1400	17/09/2003
22	1569#INCV		1333	12/08/2002
Sheet1

 

[venkat1926]

this is reg identifying the identical rows.
in an empty column inex to data n a row for e.g. row no. 1
type
=a1&b1&c1&d1..........
then sort the whole data according to the helper column
the identical rows will be together.

venkat

 

[twinkle99]

ok, that will work but how do i then distinguish the identical rows from each other. I would prefer to have the latest row as say 1021#INCV and then working back 1021#INCV2, 1021#INCV3 etc

 

[venkat1926]

It is not clear what is meant by “identical rows”. The identical rows are those rows where the element in each column is same. There is no such identical row atleast within your data. In column A there is only INCV there is no INCV2 or INCV3
Anyhow see the spreadsheet below. Does it give any insight? The formulas in column E and F and G may be seen.

give your response please. you can add in column H what you expect.


venkat

Book3
	A	B	C	D	E	F	G
1	1021#INCV		950	16/01/2000	1021#INCV95016/01/2000	INCV95016/01/2000	INCV9501
2	1021#INCV		1600	18/09/2005	1021#INCV160018/09/2005	INCV160018/09/2005	INCV1600
3	1569#INCV		1560	18/09/2005	1569#INCV156018/09/2005	INCV156018/09/2005	INCV1560
4	1569#INCV		1500	20/09/2004	1569#INCV150020/09/2004	INCV150020/09/2004	INCV1500
5	1021#INCV		1452	20/09/2004	1021#INCV145220/09/2004	INCV145220/09/2004	INCV1452
6	1569#INCV		1400	17/09/2003	1569#INCV140017/09/2003	INCV140017/09/2003	INCV1400
7	1569#INCV		1333	12/8/2002	1569#INCV133312/8/2002	INCV133312/8/2002	INCV1333
8	1021#INCV		1200	17/09/2003	1021#INCV120017/09/2003	INCV120017/09/2003	INCV1200
9	1021#INCV		1166	16/08/2002	1021#INCV116616/08/2002	INCV116616/08/2002	INCV1166
10	1021#INCV		1020	15/08/2001	1021#INCV102015/08/2001	INCV102015/08/2001	INCV1020
11	1569#CA05		90	17/09/2003	1569#CA059017/09/2003	CA059017/09/2003	CA059017
12	1569#CA05		200	18/09/2005	1569#CA0520018/09/2005	CA0520018/09/2005	CA052001
13	1569#CA05		150	20/09/2004	1569#CA0515020/09/2004	CA0515020/09/2004	CA051502
14	1021#CA04		90	17/09/2003	1021#CA049017/09/2003	CA049017/09/2003	CA049017
15	1021#CA04		20	18/09/2005	1021#CA042018/09/2005	CA042018/09/2005	CA042018
16	1021#CA04		15	20/09/2004	1021#CA041520/09/2004	CA041520/09/2004	CA041520
17	1021#ADP		88	20/09/2004	1021#ADP8820/09/2004	ADP8820/09/2004	ADP8820/
18	1021#ADP		620	18/09/2005	1021#ADP62018/09/2005	ADP62018/09/2005	ADP62018
19	1569#ADP		615	18/09/2005	1569#ADP61518/09/2005	ADP61518/09/2005	ADP61518
20	1569#ADP		595	20/09/2004	1569#ADP59520/09/2004	ADP59520/09/2004	ADP59520
21	1569#ADP		500	17/09/2003	1569#ADP50017/09/2003	ADP50017/09/2003	ADP50017
22	1021#ADP		33	17/09/2003	1021#ADP3317/09/2003	ADP3317/09/2003	ADP3317/
23				sortedaccordingtoColFdescending
Sheet1

 

[twinkle99]

Hi

The identical Rows are in Column A, this is the problem i have. The data is exported from a database and this is how it is presented. My problem is that the Macro I have written will only read the first occurance of (for example) 1021#INCV and hence when it outputs the result, it is always 1600.

I think I have two options:

1. Write something in the Macro which will go to the next row where 1021#INCV exists (if the first occurance does not meet the criteria). Is there a next function in VBA?, this is abit beyond me to be honest.

2. Somehow manipulate the data in Column A. This appears to be the route you have taken but I dont think this will work for me.

If I sort Column A in the Macro I will get the following (for example):

1021#INCV
1021#INCV
1021#INCV
1021#INCV
1021#INCV

These are in date order as the data is exported in that way.

What I now need to do is manipulate the data somehow in Column A, along the lines:

1021#INCV1 - this being the latest one
1021#INCV2
1021#INCV3
1021#INCV4
1021#INCV5 - this being the earliest one

Or something very similar, where I have now seperated the identical strings.

I can then re write the Macro so that it starts with 1021#INCV1 and then moves to the next INCV (1021#INCV2 etc) for that members until it matches.

Hope this makes sense.

 

[venkat1926]

I shall come back to you
venkat

 

[venkat1926]

I hope I have understood.
See the she spreadsheet below AFTER running the macro.
I have utilized the columns B and E assuming they are empty. If they have data you take two columns after the last column. In that case the macro needs slight modification in . the addresses of the cells.
see comment lines in the macro.
see column E

you can run the macro again once or twice to see whetehr you get what you want.
there is rpovision in the macro to delete the range B2 down and E2 down and then tor run the othre ocde satements.



Venkat .

book1.xls
	A	B	C	D	E
1	H1	H2	H3	H4	H5
2	1021#ADP	ADP	88	20/09/2004	ADP_1
3	1569#ADP	ADP	595	20/09/2004	ADP_2
4	1569#ADP	ADP	615	18/09/2005	ADP_3
5	1021#ADP	ADP	620	18/09/2005	ADP_4
6	1021#ADP	ADP	33	17/09/2003	ADP_5
7	1569#ADP	ADP	500	17/09/2003	ADP_6
8	1021#CA04	CA04	15	20/09/2004	CA04_1
9	1021#CA04	CA04	20	18/09/2005	CA04_2
10	1021#CA04	CA04	90	17/09/2003	CA04_3
11	1569#CA05	CA05	150	20/09/2004	CA05_1
12	1569#CA05	CA05	200	18/09/2005	CA05_2
13	1569#CA05	CA05	90	17/09/2003	CA05_3
14	1021#INCV	INCV	1452	20/09/2004	INCV_1
15	1569#INCV	INCV	1500	20/09/2004	INCV_2
16	1569#INCV	INCV	1560	18/09/2005	INCV_3
17	1021#INCV	INCV	1600	18/09/2005	INCV_4
18	1021#INCV	INCV	1200	17/09/2003	INCV_5
19	1569#INCV	INCV	1400	17/09/2003	INCV_6
20	1021#INCV	INCV	1166	16/08/2002	INCV_7
21	1021#INCV	INCV	950	16/01/2000	INCV_8
22	1021#INCV	INCV	1020	15/08/2001	INCV_9
23	1569#INCV	INCV	1333	12/8/2002	INCV_10
Sheet1

THE MACRO IS GIVEN BELOW

Sub test()
Dim rng, c As Range
Dim i As Integer
i = 0
'the next two lines is to clear the result of the macro
'so that you can test the macro once or twice
Range(Range("B2"), Range("b2").End(xlDown)).Clear
Range(Range("e2"), Range("e2").End(xlDown)).Clear
'the actual macro starts
Set rng = Range(Cells(2, 1), Cells(Rows.Count, "a").End(xlUp))
'now introduce in column B the alphabet part of cells in column A
'if column B is not free change to a column adjacent to the data
For Each c In rng
Cells(c.Row, "b") = Mid(c.Value, 6, Len(c.Value) - 5)
Next
'now sort according col A ascending, col D descending
'column C ascending
'set rng as the full data including the new column
Set rng = Range(Cells(1, 1), Cells(Rows.Count, "d").End(xlUp))
rng.Sort order1:=xlAscending, key1:=Range("b1"), order2:=xlDescending, key2:=Range("d1"), _
    order3:=xlAscending, key3:=Range("c1"), header:=xlYes
    
'now according to date you add order no. to cells in col B and park it in column E
Set rng = Range(Range("B2"), Range("B2").End(xlDown))
For Each c In rng
If c = c.Offset(1, 0) Then
i = i + 1
Cells(c.Row, "e") = c.Value & "_" & i
Cells(c.Offset(1, 0).Row, "e") = c.Value & "_" & i + 1
Else
i = 0
End If
Next

End Sub

 

[jindon]

does this help?

Sub test()
Dim a, i As Long, dic As Object, txt As String, myNum As Long
a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
With CreateObject("VBScript.RegExp")
   For i = 1 To UBound(a,1)
      If Not IsEmpty(e) Then
         .Pattern = "\d+$"
         txt = .replace(Trim(a(i,1)),"")
         If .test(a(i,1)) Then
            myNum = .execute(Trim(a(i,1)))(0)
         Else
            myNum = 0
         End If
         If Not dic.exists(txt) Then
            dic.add txt, myNum
         Else
            a(i,1) = txt & dic(txt) + 1
            dic(txt) = dic(txt) + 1
         End If
      End If
   Next
End With
Range("a1").Resize(UBound(a,1),UBound(a,2)).Value = a
Set dic = Nothing
End Sub

P.S my understanding is different from Venkat, so if this is not what you after just ignore.

 

[twinkle99]

Hi venkat1926, Hi Jindon

Thanks you both for your input with this, it is much appreciated.

Jindon, I cant get your code to work, what is it supposed to do?, I am not getting any results.

Venkat, your code works, I made a few tweaks because the reference number (in column A) is not always 4 characters so when i tested it on one with 5 characters it didnt work correctly (see below).
I also didnt want the header and i wanted the result in column B (sorry but i removed your notes).


Sub test3()
Dim rng, c As Range
Dim i As Integer
i = 0
Range(Range("B2"), Range("b2").End(xlDown)).Clear
Range(Range("e2"), Range("e2").End(xlDown)).Clear
Set rng = Range(Cells(1, 1), Cells(Rows.Count, "a").End(xlUp))
For Each c In rng
Cells(c.Row, "b") = c.Value
Next
Set rng = Range(Cells(1, 1), Cells(Rows.Count, "d").End(xlUp))
rng.Sort order1:=xlAscending, key1:=Range("a1"), header:=xlNo
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In rng
If c = c.Offset(1, 0) Then
i = i + 1
Cells(c.Row, "b") = c.Value & i
Cells(c.Offset(1, 0).Row, "b") = c.Value & i + 1
Else
i = 0
End If
Next

End Sub



However, I have now found a flaw in the whole thing because i didnt realise that the data is in another sheet (sheet 1). This means that if we sort the sheet i have given you (sheet 2), the index match functions in the macro dont work because they pull data from sheet 1 from the exact same row where the text string was found in sheet 2. For example, where 1021#INCV is found in Sheet 2 (say A1), it will then find the amount from Sheet 1 - from row 1 (the column is always F:F). Similarly, if the text string was in row 22000, it will match on row 22000 in sheet 1.

So basically, can we get this code to work without sorting it?

A short piece of the unsorted data on sheet 2 is given below

Note that the data in column A sheet 2 will always be in reference order first but the elements are not in any particular order except that the latest elements will be shown first (and not all elements are applicable to every member).

Note also that the amounts and dates in column C and D are actually from Sheet 1!, I copied them over rather than sending you another sheet, sorry my mistake.

Many Thanks

vba test.xls
	A	B	C	D
1	1569#INCV		1560	18/09/2005
2	1569#CA05		200	18/09/2005
3	1569#ADP		615	18/09/2005
4	1569#INCV		1500	20/09/2004
5	1569#CA05		150	20/09/2004
6	1569#ADP		595	20/09/2004
7	1569#INCV		1400	17/09/2003
8	1569#CA05		90	17/09/2003
9	1569#ADP		500	17/09/2003
10	1569#INCV		1333	12/08/2002
11	1021#INCV		1600	18/09/2005
12	1021#CA04		20	18/09/2005
13	1021#ADP		620	18/09/2005
14	1021#INCV		1452	20/09/2004
15	1021#CA04		15	20/09/2004
16	1021#ADP		88	20/09/2004
17	1021#CA04		90	17/09/2003
18	1021#ADP		33	17/09/2003
19	1021#INCV		1200	17/09/2003
20	1021#INCV		1166	16/08/2002
21	1021#INCV		1020	15/08/2001
22	1021#INCV		950	16/01/2000
23	56233#INCV		233	18/09/2005
24	56233#ADP		50	18/09/2005
25	56233#INCV		200	17/09/2004
26	56233#INCV		163	16/09/2003
Sheet2

 

[venkat1926]

Quote
the reference number (in column A) is not always 4 characters so when i tested it on one with 5 characters it didn’t work correctly (see below).
Unquote
There is no problem about this What you should do is to find the number of characters when # occurs and use this in mid function.

I am thinking on certain lines and shall try on them. I am just now ging out and be back after 3 hours and then I shall try. Don’t worry the whole day is remaining.
Greetings
venkat