Find missing values in a series
 

I have a column with transaction numbers which are non unique values, but
some values are missing

1
1
2
2
2
4
4
5
5
6
6
6
8
8
.....
10000

I want to output a list of the missing values:
3
7
.....

Find missing values in a series
 

I want to add, and it is not clear from my example, that the values are not
sorted, however, all the entries with the same value are together, i.e. the
column can look like this:

1
1
1
3
3
3
....
45
45
2
2
....
"petermcwerner" wrote:

I have a column with transaction numbers which are non unique values, but
some values are missing

1
1
2
2
2
4
4
5
5
6
6
6
8
8
....
10000

I want to output a list of the missing values:
3
7
....

Find missing values in a series
 

Hello,

If your values are in A1:A99 then enter as array formula:
=multicat(IF(ISNA(MATCH(ROW(INDIRECT("1:"&MAX(A1:A 99))),A1:A99,)),ROW(INDIRECT("1:"&MAX(A1:A99)))&", ",""))

The UDF multicat you have to copy into a module (press ALT + F11,
insert a module and then insert code shown below):
'********************************************
'Purpose: Concatenate all cells in a range or
' array
'Inputs: vP - range/array to be concatenated
' sDelim - optional delimiter to be
' inserted between text parts
'Returns: Concatenated string
'*****************************************
Function MultiCat( _
ByRef vP As Variant, _
Optional ByVal sDelim As String = "") _
As String
Dim vE As Variant
For Each vE In vP
MultiCat = MultiCat & sDelim & vE
Next vE
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

Regards,
Bernd

Find missing values in a series
 

Hello,Sir Bernd,

calculation time is worth of it and this multicat function is good, yet is
there any way to have a final result something like this

{3,6,7,8,10}

i've made a few run on it and have a result of
3,6,7,8,10,

i need it as part of a lookup..if you can please

best regards,
driller

" wrote:

Hello,

If your values are in A1:A99 then enter as array formula:
=multicat(IF(ISNA(MATCH(ROW(INDIRECT("1:"&MAX(A1:A 99))),A1:A99,)),ROW(INDIRECT("1:"&MAX(A1:A99)))&", ",""))

The UDF multicat you have to copy into a module (press ALT + F11,
insert a module and then insert code shown below):
'********************************************
'Purpose: Concatenate all cells in a range or
' array
'Inputs: vP - range/array to be concatenated
' sDelim - optional delimiter to be
' inserted between text parts
'Returns: Concatenated string
'*****************************************
Function MultiCat( _
ByRef vP As Variant, _
Optional ByVal sDelim As String = "") _
As String
Dim vE As Variant
For Each vE In vP
MultiCat = MultiCat & sDelim & vE
Next vE
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

Regards,
Bernd

Find missing values in a series
 

Hello,

Then select a vertical array of cells of sufficient size and enter as
array formula:
=TRANSPOSE(ReturnNonEmpty(IF(ISNA(MATCH(ROW(INDIRE CT("1:"&MAX(A1:A99))),A1:A99,)),ROW(INDIRECT("1:"& MAX(A1:A99))),"")))

[you should omit TRANSPOSE if you take a horizontal array]

Insert the following UDF:
Function ReturnNonEmpty( _
ByRef vP As Variant) As Variant
Dim vE As Variant
Dim i As Long

ReDim vR(1 To UBound(vP)) As Variant
i = 0
For Each vE In vP
If Len(vE) 0 Then
i = i + 1
vR(i) = vE
End If
Next vE
ReDim Preserve vR(1 To i) As Variant
ReturnNonEmpty = vR
End Function

Please notice that in case of only 1 gap you get that value in all
cells of your result array. And, if there is no gap at all, error
values will appear.

Regards,
Bernd

Find missing values in a series
 

can u give one more and it mayBE my last,

i need only want to get the result if
a) one integer is missing between the 2 immediate integer
b) the integer is on this series (1,8,15,22) of 7's.

(e.g)

1
2
2
4
10
10
12
15

result is {3,7,11,14} in sequence,

gracias with thanks...
driller

" wrote:

Hello,

Then select a vertical array of cells of sufficient size and enter as
array formula:
=TRANSPOSE(ReturnNonEmpty(IF(ISNA(MATCH(ROW(INDIRE CT("1:"&MAX(A1:A99))),A1:A99,)),ROW(INDIRECT("1:"& MAX(A1:A99))),"")))

[you should omit TRANSPOSE if you take a horizontal array]

Insert the following UDF:
Function ReturnNonEmpty( _
ByRef vP As Variant) As Variant
Dim vE As Variant
Dim i As Long

ReDim vR(1 To UBound(vP)) As Variant
i = 0
For Each vE In vP
If Len(vE) 0 Then
i = i + 1
vR(i) = vE
End If
Next vE
ReDim Preserve vR(1 To i) As Variant
ReturnNonEmpty = vR
End Function

Please notice that in case of only 1 gap you get that value in all
cells of your result array. And, if there is no gap at all, error
values will appear.

Regards,
Bernd

Find missing values in a series
 

Hello,

Hmm, sorry, but that exceeds my coffee break.

Maybe I can solve this later.

Just out of curiosity: Why do you need this?

Regards,
Bernd

Find missing values in a series
 

WIth the post title, i have the same type of so-so problem.

multicat works yet the next one just gave me one result.
a short UDF or a monster formula will do magic to solve my missing series of
lookup for the unseen....

thanks and enjoy your coffee.

best regards,
driller

" wrote:

Hello,

Hmm, sorry, but that exceeds my coffee break.

Maybe I can solve this later.

Just out of curiosity: Why do you need this?

Regards,
Bernd

Find missing values in a series
 

Hello Bernd,

Thank you for your answer. I have tried to copy and paste your formula and
codes but I only get a "#VALUE!" error message. I must have done something
wrong, as it is the first time I work with a UDF in Excel. Just to give you
an idea of my capabilitîes to understand: I am a CPP programmer but utterly
ignorant of vBasic. I therefore shall explain step by step what I did and
hope you can find and point out my mistake.

1) The range of my values is B5:B1314

2) I copied the The UDF multicat without any modification as you instructed

3) In Cell C6 (of an empty coloumn C) I copied your formula, modified as
follows:
=multicat(IF(ISNA(MATCH(ROW(INDIRECT("1:"&MAX(B5:B 1314))),B5:B1314,)),ROW(INDIRECT("1:"&MAX(B5:B1314 )))&",",""))

Can you see from these data what is wrong?

Thank you, best regards
Peter

" wrote:

Hello,

If your values are in A1:A99 then enter as array formula:
=multicat(IF(ISNA(MATCH(ROW(INDIRECT("1:"&MAX(A1:A 99))),A1:A99,)),ROW(INDIRECT("1:"&MAX(A1:A99)))&", ",""))

The UDF multicat you have to copy into a module (press ALT + F11,
insert a module and then insert code shown below):
'********************************************
'Purpose: Concatenate all cells in a range or
' array
'Inputs: vP - range/array to be concatenated
' sDelim - optional delimiter to be
' inserted between text parts
'Returns: Concatenated string
'*****************************************
Function MultiCat( _
ByRef vP As Variant, _
Optional ByVal sDelim As String = "") _
As String
Dim vE As Variant
For Each vE In vP
MultiCat = MultiCat & sDelim & vE
Next vE
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

Regards,
Bernd

Find missing values in a series
 

Hello Peter,

1. Did you enter the formula as array formula (finally press CTRL +
SHIFT + ENTER, not only ENTER!)?

2. Sometimes ominous minuses ("-") are added into long lines of code
here. Please check whether there is any unwanted "-" in your worksheet
or your VBA code.

Does it work now?

If not send me a mail. I can send you my sample file then.

Regards,
Bernd

Find missing values in a series
 

Hello Bernd,
Working now, thank you very much
Best regards
Peter