Compare data with the same cell

[jedi_jr]

SO I have data all in one column that I need to compare.
It's basically the data between the 2nd and 3rd commas being compared to the data after the 2nd colon. I have over 860,000 entries in the database that this data is extracted from so I can't go through it one-by-one.

12EDM1,1,1638413100250,Match_Location_Number:1:1638413100250
VIK1,1,1638413100251,Match_Location_Number:1:1638413100251
WHI1,1,1638413100252,Match_Location_Number:1:1638413100252
HPR1,1,1638413100253,Match_Location_Number:1:1638413100253

 

[Jonmo1]

Is there a question there somewhere?

 

[jedi_jr]

How can I compare the data between the 2nd and 3rd commas, to the data after the 2nd colon? All i want is an output of true or false. that way I can go back and find the errors and fix them.

 

[Jonmo1]

Try
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1)))=REPLACE(A1,1,FIND("^",SUBSTITUTE(A1,":","^",2)),"")

 

[jedi_jr]

Jonmo1,

Thank you so much. my biggest problem was selecting the portions of data that I wanted to compare. So if I understand this correctly, i just need to replace the comma and colon with other characters and grab the data following those new characters.

Again, Thank you!

 

[Jonmo1]

Glad to help

 

[L. Howard]

A guess...

With these in A1 & A2, (A2 altered from posted example)

12EDM1,1,1638413100250,Match_Location_Number:1:1638413100250

VIK1,1,1688413100251,Match_Location_Number:1:1638413100251 And this formula pulled down from F1 to F2 =(MID(A1,(FIND(",1,",A1)+3),13))=RIGHT(A1,13) ReturnsTRUE in F1 Returns FALSE in F2. With 800K + entries, vba may be a better way to go. I'll play with some code for a personal exercise and perhaps you can use it if I am succesful. Regards, Howard

<colgroup><col style="width: 54pt;" width="72">
<tbody>


</tbody>

 

[L. Howard]

With 800K rows, what would be the process to make corrections?

Would you make the left number coincide with the right one, or the other way around or is it possible both numbers are incorrect?

The correction process seem more daunting than the finding errors process.

I have a vb macro that will do the ture/false, but is pretty slow. 80 sec for about 200K rows. It will either return true/false or highlight the cell with the missmatch yellow.

It may be quite possible to develope code to make correction if I knew how you would go about making those corrections.

Regards,
Howard

 

[L. Howard]

Try this on a sample of your data.
With your list in column E returns True or False in column L.

I'm running Excel 2010 and the code seems to error out with over 65536 rows. Best advice I am able to obtain on why is probably a memory resource thing since the "work" is dumped into an array, processed and then dumped back to the sheet.

The row number does indeed match Excel 2003 max row number of 65536, but the code was developed in Excel 2010.

The good news is it processes 65536 rows in about .65 of a second. (on my machine, a HP Pavilion g6 laptop.)

It might be an advantage to highlight the cell that result in a False instead of posting it in another column, it looked pretty convient with some super slow code I wrote. I have not tried to add highlighting in the array dumping code bullet code. I'll probably give it a try.

Perhaps this could be adapted to make corrections also. That may be beyond my pay grade to do so on my own. (Garry (GS) at another forum got me headed in the right direction on this.)

Sub Tester2GarryGS()
  Dim vDataIn, v1, v2, vDataOut(), n&
  vDataIn = Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)
  ReDim vDataOut(1 To UBound(vDataIn))
  For n = LBound(vDataIn) To UBound(vDataIn)
    v1 = Split(vDataIn(n, 1), ","): v2 = Split(v1(3), ":")
    vDataOut(n) = (v1(2) = v2(2))
  Next 'n
  Range("E1").Offset(0, 7).Resize(UBound(vDataOut)) = _
    WorksheetFunction.Transpose(vDataOut)
End Sub

Regards,
Howard

 

[L. Howard]

At the risk of "waaay too much information" here are two set of code, first one returns TRUE or FALSE to column L.
Tested on 500,000 rows, completed task in 0.762 seconds. Thanks to Adrien in MS Programming Forum.

The second produces Conditional Format to each cell and highlights Yellow if the numbers don't match. (Similar to a False)
Tested on 500,000 rows, 0.109 seconds. Thanks to Clause of the same forum/modified by me.

Option Explicit
Sub Tester4Adrien()
'Adrien
Dim LRow As Long
Dim st As Double
st = Timer
LRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("L1:L" & LRow).Formula = "=find(right(E1,13),E1)=8"
MsgBox Format(Timer - st, "0.000")
End Sub

'************************************************
Option Explicit
Sub testConditionalFormatting()
' BY: Clause/modified by me
Dim LRow As Long
 Dim st As Double
st = Timer
LRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("E$1:$E" & LRow).Select
With Selection
   .FormatConditions.Add Type:=xlExpression, Formula1:="=find(right(E1,13),E1)=8=FALSE"
   With .FormatConditions(1).Interior
      .ColorIndex = 6
   End With
End With
MsgBox Format(Timer - st, "0.000")
End Sub

Regards,
Howard