Excel formula to find data with multiple criteria

[joy1mat]

I have a spreadsheet with over 100K rows (and growing) with surnames in 1 column (A) and Christian names in another (B) which I populate from another source. I need to find a formula/s to find when the same surnames and/or Christian names are not sequenced correctly eg surnames such as JONES should contain an _1 or _2 after them etc. If there is also a Christian name against a surname the same applies eg JONES John_1 etc. Basically I need to find same surnames with or without the _ so I can correctly sequence them. I suspect I may need 2 formulas to handle the surname separately from the surname/Christian names. I have a reasonably good understanding of formulas and have tried using LENS and MATCH but this one has me stumped. Any ideas?

 

[Peter_SSs]

Welcome to the MrExcel board!

It isn't quite clear to me. Could you provide a small set of dummy data and expected results (say 10-15 rows) to show the variety of circumstances that may occur with your data and what you want done with it?
For good ways to provide sample data that we can copy and test with, Look here.

 

[joy1mat]

Welcome to the MrExcel board!

It isn't quite clear to me. Could you provide a small set of dummy data and expected results (say 10-15 rows) to show the variety of circumstances that may occur with your data and what you want done with it?
For good ways to provide sample data that we can copy and test with, Look here.

I've used the forum tools and done a test and it looks ok. Hopefully I've done it right. I don't think a formula/s will automatically fix my issue but would like the formula to identify which ones I need to fix. I do appreciate your help.

Sample current		Sample end result
2014_01_12	JONES			2014_01_12	JONES_1
2014_01_12	JONES	John_1		2014_01_12	JONES	John_1
2014_01_12	JONES	John_2		2014_01_12	JONES	John_2
2014_01_12	JONES	John_3		2014_01_12	JONES	John_3
2014_01_12	JONES	John_4		2014_01_12	JONES	John_4
2014_01_12	JONES	John_5		2014_01_12	JONES	John_5
2014_01_12	JONES	John_6		2014_01_12	JONES	John_6
2014_01_12	JONES_1			2014_01_12	JONES_2
2015_06_01	JONES	John		2015_06_01	JONES	John_7
2015_06_01	JONES_2			2015_06_01	JONES_3
2015_06_01	JONES_3			2015_06_01	JONES_4

<tbody>
</tbody><colgroup><col><col span="2"><col><col><col span="2"></colgroup>

 

[Peter_SSs]

See if this does what you want, though I'm not sure how it will go with 100,000 rows of data.
Might require a macro if the sheet bogs down with formulas. Would that be acceptable?

Both formulas copied down.

Excel Workbook
	A	B	C	D	E
1	Sample current
2	2014_01_12	JONES		JONES_1
3	2014_01_12	JONES	John_1	JONES	John_1
4	2014_01_12	JONES	John_2	JONES	John_2
5	2014_01_12	JONES	John_3	JONES	John_3
6	2014_01_12	JONES	John_4	JONES	John_4
7	2014_01_12	JONES	John_5	JONES	John_5
8	2014_01_12	JONES	John_6	JONES	John_6
9	2014_01_12	JONES_1		JONES_2
10	2015_06_01	JONES	John	JONES	John_7
11	2015_06_01	JONES_2		JONES_3
12	2015_06_01	JONES_3		JONES_4
Repeat names

 

[joy1mat]

WOW your good. I did try to use LENS and FIND but I can see why my formula didn't work. We're nearly there. Whilst both formulas work they are also putting an _1 etc against names that are unique that don't require a sequence eg if I have a surname of ABBOTT and there are no other ABBOTT'S in the list then it doesn't need the _1. The same applies with Christian names eg ABBOTT_Fred. My apologies I should have included a couple of unique names on the sample I sent you. It's only where the same surname and/or Christian name appears more than once both with and without a _ that I have to fix it. Also I tend to agree that the spreadsheet is probably going to get bogged down the bigger it gets so once I get the formulas working I have to think about whether to go down the macro path. I'm on Excel 2013 and it keeps wanting to disable macros eg the Forum Tools one. If you need me to send another sample with the additional names for clarification let me know.

 

[Peter_SSs]

If you need me to send another sample with the additional names for clarification let me know.

I think that would be a good idea.

 

[joy1mat]

As requested. In the case below the names of ABBOTT are unique and not part of a sequence so they don't need the _ as part of the name. All the JONES should be part of a sequence and should all have an _ as part of the name for both the instances where it's just JONES or JONES John.

Sample current		Sample end result
2014_01_12	JONES			2014_01_12	JONES_1
2014_01_12	JONES	John_1		2014_01_12	JONES	John_1
2014_01_12	JONES	John_2		2014_01_12	JONES	John_2
2014_01_12	JONES	John_3		2014_01_12	JONES	John_3
2014_01_12	ABBOTT			2014_01_12	ABBOTT
2014_01_12	ABBOTT	John		2014_01_12	ABBOTT	John
2014_01_12	ABBOTT	Joseph		2014_01_12	ABBOTT	Joseph
2014_01_12	JONES_1			2014_01_12	JONES_2
2015_06_01	JONES	John		2015_06_01	JONES	John_4
2015_06_01	JONES_2			2015_06_01	JONES_3
2015_06_01	JONES_3			2015_06_01	JONES_4

<tbody>
</tbody><colgroup><col><col span="3"><col><col span="2"></colgroup>

I do hope I'm getting the screen shot correct for you.

 

[Peter_SSs]

I do hope I'm getting the screen shot correct for you.

The screen shots are fine as I can copy/paste that data to test with.
However, particularly with the size of your data, I think a formula solution is getting unwieldy and will probably bog your sheet right down.
Instead, I would suggest that you try this macro. Post back if you need help with how to implement it.

I'm also not sure if you want the results in separate columns like I have below in columns D:E, or whether to just over-write the original values in columns B:C?

Make sure that you test in a copy of your workbook.

Sub NameSequence()
  Dim a As Variant, b As Variant, ky As Variant
  Dim d As Object
  Dim i As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  Application.ScreenUpdating = False
  With Range("B2:C" & Range("B" & Rows.Count).End(xlUp).Row)
    b = .Value
    .Replace What:="_*", Replacement:="", LookAt:=xlPart
    a = .Value
    ReDim Preserve a(1 To UBound(a), 1 To 3)
    For i = 1 To UBound(a)
      s = a(i, 1) & "|" & a(i, 2)
      d(s) = d(s) + 1
      a(i, 3) = s
    Next i
    For Each ky In d.Keys
      If d(ky) = 1 Then d.Remove ky
    Next ky
    For i = UBound(a) To 1 Step -1
      s = a(i, 3)
      If d.exists(s) Then
        If Right(s, 1) = "|" Then
          a(i, 1) = a(i, 1) & "_" & d(s)
        Else
          a(i, 2) = a(i, 2) & "_" & d(s)
        End If
        d(s) = d(s) - 1
      End If
    Next i
    .Offset(, 2).Value = a
    .Value = b
  End With
  Application.ScreenUpdating = True
End Sub

Original data in columns A:C, columns D:E produced by the above code.

Excel Workbook
	A	B	C	D	E
1
2	2014_01_12	JONES		JONES_1
3	2014_01_12	JONES	John_1	JONES	John_1
4	2014_01_12	JONES	John_2	JONES	John_2
5	2014_01_12	JONES	John_3	JONES	John_3
6	2014_01_12	ABBOTT		ABBOTT
7	2014_01_12	ABBOTT	John	ABBOTT	John
8	2014_01_12	ABBOTT	Joseph	ABBOTT	Joseph
9	2014_01_12	JONES_1		JONES_2
10	2015_06_01	JONES	John	JONES	John_4
11	2015_06_01	JONES_2		JONES_3
12	2015_06_01	JONES_3		JONES_4
Repeat names (2)