Numeros correlativos por grupos

hola, José !

1) *talco...mo* expones los datos en tu ejemplo, se necesita *partir* cada celda en dos grupos...
los primeros dos digitos para *el grupo* de productos, y los restantes 4 a 7 para sus codigos
[asumiendo que tus codigos no estaran *limitados* a solo 4 digitos: 1542, 1543, etc.] -?-

2) con base en lo anterior, se necesita *crear* una serie [por grupo] basada en el minimo y maximo de sus codigos
y luego crear una *tabla* donde se puedan identificar los numeros que estan haciendo falta en el grupo-rango

3) usando solo formulas y funciones de hoja de calculo, resultaria en una formula *monstruosa* [por larga y matricial]
por lo que te sugiero definir [al menos] dos nombres [Serie y Tabla] desde menu: insertar / nombre / definir...
[pero]... como no expones p.e. de cuales rangos en la hoja de calculo estamos hablando :-((
los siguientes son *mis* supuestos...

a) el rango para tu listado de codigos de productos es: A2:A8 [omito la fila 1 por si los titulos] :))
b) a partir de C1, D1... pondras los dos digitos de identificacion de cada grupo: [C1] -> 69... [D1] -> 72... etc.
c) a partir de la celda 'C2' quieres obtener el listado de los numeros que faltan en el rango de cada grupo -?-

4) con los supuestos anteriores, y tomando en cuenta que mi sistema usa la coma para separar los argumentos...
realiza lo siguientes *pasos* para obtener lo que necesitas:

a) selecciona la celda 'C2' <= es muy importante cual sera la celda *inicial* para la definicion de los nombres
b) usa [menu] insertar / nombre / definir... para los dos nombres [sugeridos]: 1) Serie 2) Tabla
OJO: la primer formula es muy larga y la voy a dividir en dos lineas, y...
-> NOTA el signo ! que antecede a las referencias a rangos [por si necesitas modificar a tus rangos *reales*]

Nombre: Formula:
Serie =--texto(fila(indirecto(min(si(--izquierda(!$a$2:$a$8,2)=!c$1,--derecha(!$a$2:$a$8,7)))&":"&
max(si(--izquierda(!$a$2:$a$8,2)=!c$1,--derecha(!$a$2:$a$8,7))))),!c$1&"0000000")

Tabla: =si(eserror(coincidir(serie,!$a$2:$a$8,0)),serie)

c) todavia con la celda C2 seleccionada, la formula que te devuelve los numeros faltantes [si y hasta donde existan] es:
=k.esimo.menor(tabla,filas(c$2:c2))

d) la formula anterior la copias/arrastras/... desde C2 hasta C_n [hasta que obtengas valores de error]
y luego la continuas a las columnas/grupos restantes: D, E, F...

-> nota: puedes aplicar formatos condicionales para ocultar las celdas con valores de error...
o, alargar la formula condicionando a resultados NO de error -?-

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

__ la consulta original __

Tengo un listado de codigo de productos, por grupos:
690001542
690001543
690001546
720001502
720001503
... ncesito... hallar por grupos (los 2 primeros digitos) los nuneros que me faltan completar...
... cada grupo (69 - 72) tienen correlatividad independiente.
y la idea primero seria hallar la correlatividad, por grupo y luego los numeros que me estan faltando por grupo...

hola chicos,

la verdad es que como el tema se 'daba un aire', aunque lejano' a mi ultima consulta sobre los nros faltantes en excel,
he estado intentando adaptar alguna de las propuestas salidas de alli, pero al final he desisitido, y me liado como
siempre y esto es lo que me ha salido (segurmente la funcion lo unico que hace es ralentizar y se podrian cargar los
datos directamnete en la hoja2)

comento los requisitos:

copia la columna (titulo incluido) con tus numeros clave en un libro nuevo (en la celda a1, Hoja1 <CodeName>). El libro
debe contener tambien una 2ª hoja con CodeName Hoja2

pega el codigo en un modulo normal y ejecuta el procedimiento sacar unicos

esta preparado para devolverte todas las claves (del 00 al 99 para los 2 primeros nros) y 'subclaves' (del 0000 al 9999
en los 4 ultimos nros) que falten

te pone en la hoja 2: en la columna A las claves que te faltan de 2 y a partir de esta, en la fila 1 te pone las claves
(tambien de 2) que si tienes y debajo las claves de 4 que le faltan a cada una

no es demasiado rapida, pero tampoco parece eterna (70 segundos rellenado mas de 6000 celdas faltantes), y creo que se
podria agilizar bastante (mas aun con hector por aqui)

tambien puedes des/omitir algunos formatos para agilizar (inicialmente te los pongo como comentario)

bueno, si quieres probarla, lo unico ten 1 o 2 minutos de paciencia hasta que acabe (y OJO con los posibles trunques de
linea en el foro)

'******************copia desde aqui
Function NrosFaltan(rango As Range, min As Long, max As Long, _
nrosHay As Variant) As Variant
Dim n As Long, faltan
Application.ScreenUpdating = False
nrosHay = Array("")
faltan = Array("")
For n = min To max
If Application.CountIf(rango, n) = 0 Then
If faltan(LBound(faltan)) = "" Then
faltan(LBound(faltan)) = n
Else
ReDim Preserve faltan(UBound(faltan) + 1)
faltan(UBound(faltan)) = n
End If
Else
If nrosHay(LBound(nrosHay)) = "" Then
nrosHay(LBound(nrosHay)) = n
Else
ReDim Preserve nrosHay(UBound(nrosHay) + 1)
nrosHay(UBound(nrosHay)) = n
End If
End If
Next
NrosFaltan = faltan
End Function
'-
Sub SacarUnicos()
Dim uf As Long, i As Long, Esta, NoEsta, rng As Range, f As Integer, tarda
tarda = Timer * 1000
Application.ScreenUpdating = False
With Hoja1
uf = .[a65536].End(xlUp).Row
.[is:iv].Clear: .[iu1] = "Clave": .[iv1] = "Subclave"
.[iu2].Formula = "=Value(Left(Hoja1!a2,Len(hoja1!a2)-7))"
.[iv2].Formula = "=Value(Right(Hoja1!a2,4))"
.[iu2:iv2].AutoFill .Range("iu2:iv" & uf)
Set rng = .Range("iu1:iv" & uf)
With rng
.Copy
.Cells(1).PasteSpecial xlPasteValues
.Sort key1:=Hoja1.[iu2], order1:=xlAscending, _
key2:=Hoja1.[iv2], order2:=xlAscending, header:=xlYes
End With
NoEsta = NrosFaltan(.Range("iu2:iu" & uf), 0, 99, Esta)
With Hoja2
.Columns.Clear
With .Range(.Cells(1, 1), .Cells(1, UBound(NoEsta) + 1))
.Value = NoEsta
.Copy
.Cells(1).Offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
End With
.Rows(1).Clear
.[a1] = "Claves faltan"
With .Range(.Cells(1, 2), .Cells(1, UBound(Esta) + 2))
.Value = Esta: .Interior.color = vbCyan
End With
For i = 2 To .[ia1].End(xlToLeft).Column
Hoja1.[is2].Formula = "=(Hoja1!iu2=" & .Cells(1, i).Value & ")"
rng.AdvancedFilter action:=xlFilterCopy, _
criteriarange:=Hoja1.[is1:is2], _
copytorange:=.[iu1:iv1], unique:=True
NoEsta = NrosFaltan(.Range("iv2:iv" & _
.[iv65536].End(xlUp).Row), 0, 9999, Esta)
For f = LBound(NoEsta) To UBound(NoEsta)
.Cells(f + 2, i).Value = NoEsta(f)
Next
Next
.[iu:iv].Clear
'' para darles formato "00" y "0000" desmarca estas 3 lineas
' .Range("a1:a" & .[a65536].End(xlUp).Row).NumberFormat = "00"
' .Range(.Cells(1, 2), .Cells(.[b1].CurrentRegion.Rows.Count, _
' .[b1].CurrentRegion.Columns.Count - 1)).NumberFormat = "0000"
'' para ajustar las columnas desmarca la linea siguiente (queda muy abigarrado)
' .Columns.AutoFit
End With
.[is:iv].Clear

End With
Set rng = Nothing
End Sub
'*************pega hasta aqui

sobre todo como curiosidad, ahora (bueno, mañana) voy a estudiar la propuesta de Hector

un saludo
Ivan

hola de nuevo,

solo para añadir un par de datos y comentarios

por un lado, y como era de esperar,creo que tras ver la propuesta de
hector, el codigo se podria transformar para evitar al menos algun
bucle y posiblemente hacerlo considerablemente mas rapido

y por otro, se me paso comentar que se puede restringir la busqueda a
los intervalos numericos deseados tanto para el primer grupo (00<>99
en el ej) como para el 2º (0000<>9999) modificando los argumentos en
las llamadas a la funcion.

lo que si me ha ocurrido (me he dado cuenta viendo la prop. de Hector)
es que para el 2º he restringido a los 4 ultimos digitos la busqueda.
Se podria arreglar cambiando donde sse obtiene mediante Right(celda,4)
el 4 por otra cifra (7 pej)

bueno, solo como aclaracion

un saludo
Ivan

PD: tampoco incluye ningun manejo de errores (pej: si no falta ningun
nº, o si esta vacia la lista, etc)

José skraviuk escribió el 20/09/2007 19:48 :

Estimados, tengo la siguiente situeación.

Tengo un listado de codigo de productos, por grupos:

690001542
690001543
690001546
720001502
720001503

lo que ncesito es hallar por grupos (los 2 primeros digitos) los nuneros que
me faltan completar, asi completo directamente esos numeros que me faltan.

como verán, cada grupo (69 - 72) tienen correlatividad independiente.

y la idea primero seria hallar la correlatividad, por grupo y luego los
numeros que me estan faltando por grupo.

alguien me puede ayudar en este tema..???

de ante mano gcias..js

Aúnque ya ha pasado bastante tiempo desde la consulta, dejo este link por si a alguien le sirve.

http://www.o-cruzg.blogspot.com/201...-mx-x.html