Re: Find missing Numeric Text in Column A

[selvamnarayanan]

Re: Find missing Numeric Text in Column A

Sir

Can you pls help me

I have 1000 rows in Column A with numeric text from AA1 to AA1326
How can I extract the missing numeric text using Formulas or Macro

Thank you
Selvam Narayanan

 

[Gerald Higgins]

Re: Find missing Numeric Text in Column A

Hi - I think your request is FAR too vague for anyone to answer.

Please describe clearly, exactly what you want to do.

 

[selvamnarayanan]

Re: Find missing Numeric Text in Column A

Hi - I think your request is FAR too vague for anyone to answer.

Please describe clearly, exactly what you want to do.

Apologies Sir

This is what I need

In Column A I have data from Row 1 to Row 1000
eg.
Cell A1 AA1
Cell A2 AA2
Cell A3 AA3
Cell A4 AA6
Cell A5 AA7

AA4 & AA5 is missing from this range
How do I list out the missing data (AA4 & AA5)

Thank you
selvamnarayanan

 

[Gerald Higgins]

Re: Find missing Numeric Text in Column A

If your data is EXACTLY in the format "AA1" and so on, then you could use something like this

In B2...
=if(mid(A2,3,5)+0=mid(A1,3,5)+1,"OK","MISSING DATA")

And copy all the way down.

What this will do, is highlight where you have missing entries in the sequence.
It WON'T tell you what those entries should be.

 

[Rick Rothstein]

Re: Find missing Numeric Text in Column A

This is what I need

In Column A I have data from Row 1 to Row 1000
eg.
Cell A1 AA1
Cell A2 AA2
Cell A3 AA3
Cell A4 AA6
Cell A5 AA7

AA4 & AA5 is missing from this range
How do I list out the missing data (AA4 & AA5)

Still a little vague...

Is your numeric data sorted as shown? If not, can we sort it for you?

How did you want the data "listed out"... a comma delimited list in cells B1, B2, etc. or one "number" to a cell starting in Column B on out to the right, row-by-row?

 

[Rick Rothstein]

Re: Find missing Numeric Text in Column A

Still a little vague...

Is your numeric data sorted as shown? If not, can we sort it for you?

How did you want the data "listed out"... a comma delimited list in cells B1, B2, etc. or one "number" to a cell starting in Column B on out to the right, row-by-row?

The red text should have said "a comma delimited list in cell B1". Assuming that, and assuming the data is already sorted numerically as you showed in in Message #1 , give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub GetMissingNumbers()
  Dim X As Long, LastRow As Long, Prefix As String, Data As Variant, Numbers As Variant
  Prefix = "AA"
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Data = Evaluate(Replace("IF(A1:A#="""","""",MID(A1:A#," & Len(Prefix) + 1 & ",LEN(A1:A#)))", "#", LastRow))
  Numbers = Evaluate("ROW(" & Replace(Range("A1"), Prefix, "") & ":" & Replace(Cells(LastRow, "A"), Prefix, "") & ")")
  For X = 1 To LastRow
    Numbers(Data(X, 1), 1) = ""
  Next
  Range("B1").Value = Prefix & Replace(Application.Trim(Join(Application.Transpose(Numbers))), " ", ", " & Prefix)
End Sub[/td]
[/tr]
[/table]