Buscar numero en rango de grupos

Si te he entendido bien, prueba con:

Sub Desglose()
Dim desde, hasta, fila, id As Long
Dim celda As Range
fila = 2
For Each celda In Sheets("Hoja1").Range("A5:A" _
& Sheets("Hoja1").Cells(60000, 1).End(xlUp).Row)
desde = celda.Offset(, 2).Value
hasta = celda.Offset(, 3).Value
If hasta = 0 Then hasta = desde
For id = desde To hasta
Sheets("Hoja2").Cells(fila, 1).Value = id
Sheets("Hoja2").Cells(fila, 2).Value = celda.Value
fila = fila + 1
Next id
Next celda
End Sub

Nota: Según tu ejemplo el ID "200 AZUL" está repetido.

Saludos desde Tenerife.
*************************************
"rodmac" escribió en el mensaje
news:

Gracias por tus comentarios Hector.

Te indico como estan los datos:
(Ya separe los rangos en las columnas C y D con la funcion de "TEXTO EN
COLUMNAS" de esta manera no hay problema si los grupos tiene uno o mas
digitos, estan con un valor de numero)

En la Hoja 1 a partir de la celda A4:

A B C D

4 GRUPO RANGO INICIO FINAL
5 VERDE 150 - 400 150 400
6 ROJO 111 111 (en este caso solo
tiene un valor)
7 ROJO 123 - 345 123 345
8 ROJO 678 - 900 678 900
9 AZUL 100 - 200 100 200
10 AZUL 200 - 500 200 500
...
100 MAGENTA 222-333 222 333

Saludos

Rod

hola, !

voy a suponer que los datos que comentas estan en la hoja1 rango [A4:D12] (sin las elipsis ...)
- fila 1 son los titulos
- columna D contiene algunas celdas vacias (donde el rango de numeros es solo de 1)

y que en la hoja2 tienes titulos en la fila1 [A1] Id, [B1] Verde, [C1] Rojo, [D1] Azul, [E1] Magenta, (...)
y en el rango [A2:A5] (columna Id) estan los numeros que quieres "rastrear" en cuantos y cuales grupos se encuentran

1) selecciona en la hoja1 (SOLO) el rango de datos de la columna D y pulsa {F5} y luego en el boton "especial..."
despues selecciona la opcion de "celdas en blanco" y acepta
luego introduce el signo '=' y pulsa una vez la tecla <flecha_izquierda> y pulsas {ctrl}+{enter}
(con esto se rellenaran los huecos donde solo hay un numero que sera igual a su columna izquierda)

2) selecciona en la hoja2 la primer celda para contar las coincidencias del Id buscado en cada grupo [B2]
y usa la siguiente formula:
[B2] =sumaproducto(--(hoja1!$a$5:$a$12=b$1),--($a2>=hoja1!$c$5:$c$12),--($a2<=hoja1!$d$5:$d$12))

-> la formula te devolvera el numero de veces que el "Id" buscado se encuentra en cada grupo (0 si no hay coincidencias)
solo comprueba si mi separador de argumentos (coma) es el mismo en tu sistema

saludos,
hector.

__ OP __

Te indico como estan los datos:
(Ya separe los rangos en las columnas C y D con la funcion de "TEXTO EN COLUMNAS"
de esta manera no hay problema si los grupos tiene uno o mas digitos, estan con un valor de numero)

En la Hoja 1 a partir de la celda A4:
A B C D
4 GRUPO RANGO INICIO FINAL
5 VERDE 150 - 400 150 400
6 ROJO 111 111 (en este caso solo tiene un valor)
7 ROJO 123 - 345 123 345
8 ROJO 678 - 900 678 900
9 AZUL 100 - 200 100 200
10 AZUL 200 - 500 200 500
...
100 MAGENTA 222-333 222 333

__ previos __

Quisiera saber si un numero se encuentra definido en uno o varios rangos, como ejemplo:
En la Hoja 1
rango

Grupo verde 150 - 400
Grupo rojo 111 (en este caso solo tiene un valor)
Grupo rojo 123 - 345
grupo rojo 678 - 900
Grupo azul 100 - 200
Grupo azul 200 - 500

En la Hoja 2
ID GRUPO VERDE GRUPO ROJO GRUPO AZUL
122 SI
145 SI SI
400 SI SI SI
399 SI SI

Hector

Muchas gracias por la formula.

Funciona de maravilla, esa funcion es muy poderosa, la tengo que entender y
estudiar.

El codigo que envio AnSanVal, tambien funciona muy bien, la puedo aplicar
para las tablas dinamicas.

Muchisimas gracias, a ambos.

rod



"Héctor Miguel" wrote:

hola, !

voy a suponer que los datos que comentas estan en la hoja1 rango [A4:D12] (sin las elipsis ...)
- fila 1 son los titulos
- columna D contiene algunas celdas vacias (donde el rango de numeros es solo de 1)

y que en la hoja2 tienes titulos en la fila1 [A1] Id, [B1] Verde, [C1] Rojo, [D1] Azul, [E1] Magenta, (...)
y en el rango [A2:A5] (columna Id) estan los numeros que quieres "rastrear" en cuantos y cuales grupos se encuentran

1) selecciona en la hoja1 (SOLO) el rango de datos de la columna D y pulsa {F5} y luego en el boton "especial..."
despues selecciona la opcion de "celdas en blanco" y acepta
luego introduce el signo '=' y pulsa una vez la tecla <flecha_izquierda> y pulsas {ctrl}+{enter}
(con esto se rellenaran los huecos donde solo hay un numero que sera igual a su columna izquierda)

2) selecciona en la hoja2 la primer celda para contar las coincidencias del Id buscado en cada grupo [B2]
y usa la siguiente formula:
[B2] =sumaproducto(--(hoja1!$a$5:$a$12=b$1),--($a2>=hoja1!$c$5:$c$12),--($a2<=hoja1!$d$5:$d$12))

-> la formula te devolvera el numero de veces que el "Id" buscado se encuentra en cada grupo (0 si no hay coincidencias)
solo comprueba si mi separador de argumentos (coma) es el mismo en tu sistema

saludos,
hector.

__ OP __
> Te indico como estan los datos:
> (Ya separe los rangos en las columnas C y D con la funcion de "TEXTO EN COLUMNAS"
> de esta manera no hay problema si los grupos tiene uno o mas digitos, estan con un valor de numero)
>
> En la Hoja 1 a partir de la celda A4:
> A B C D
> 4 GRUPO RANGO INICIO FINAL
> 5 VERDE 150 - 400 150 400
> 6 ROJO 111 111 (en este caso solo tiene un valor)
> 7 ROJO 123 - 345 123 345
> 8 ROJO 678 - 900 678 900
> 9 AZUL 100 - 200 100 200
> 10 AZUL 200 - 500 200 500
> ...
> 100 MAGENTA 222-333 222 333

>>>> __ previos __
>>>>> Quisiera saber si un numero se encuentra definido en uno o varios rangos, como ejemplo:
>>>>> En la Hoja 1
>>>>> rango
>>>>>
>>>>> Grupo verde 150 - 400
>>>>> Grupo rojo 111 (en este caso solo tiene un valor)
>>>>> Grupo rojo 123 - 345
>>>>> grupo rojo 678 - 900
>>>>> Grupo azul 100 - 200
>>>>> Grupo azul 200 - 500
>>>>>
>>>>> En la Hoja 2
>>>>> ID GRUPO VERDE GRUPO ROJO GRUPO AZUL
>>>>> 122 SI
>>>>> 145 SI SI
>>>>> 400 SI SI SI
>>>>> 399 SI SI

Siguiendo con el ejemplo (y con la solución de Héctor), para saber ". cuantos elementos tiene cada grupo ."; podríamos
insertar una fila1 (para no modificar la fórmula ofrecida por Héctor) y.

[A1] ¢
[B1] ²&" ("&SUMAPRODUCTO((Hoja1!$A$5:$A$12²)*(Hoja1!$D$5:$D$12-Hoja1!$C$5:$C$12+1))&")"

Copia/Pega B1 hasta E1.
Oculta la fila 2.

... con esto tendrás el nombre del grupo y entre paréntesis los elementos que contiene.

Saludos desde Tenerife: Antonio.
*********************************
"rodmac" escribió en el mensaje
news:

Hector

Muchas gracias por la formula.

Funciona de maravilla, esa funcion es muy poderosa, la tengo que entender y
estudiar.

El codigo que envio AnSanVal, tambien funciona muy bien, la puedo aplicar
para las tablas dinamicas.

Muchisimas gracias, a ambos.

rod



"Héctor Miguel" wrote:

hola, !

voy a suponer que los datos que comentas estan en la hoja1 rango [A4:D12] (sin las elipsis ...)
- fila 1 son los titulos
- columna D contiene algunas celdas vacias (donde el rango de numeros es solo de 1)

y que en la hoja2 tienes titulos en la fila1 [A1] Id, [B1] Verde, [C1] Rojo, [D1] Azul, [E1] Magenta, (...)
y en el rango [A2:A5] (columna Id) estan los numeros que quieres "rastrear" en cuantos y cuales grupos se encuentran

1) selecciona en la hoja1 (SOLO) el rango de datos de la columna D y pulsa {F5} y luego en el boton "especial..."
despues selecciona la opcion de "celdas en blanco" y acepta
luego introduce el signo '=' y pulsa una vez la tecla <flecha_izquierda> y pulsas {ctrl}+{enter}
(con esto se rellenaran los huecos donde solo hay un numero que sera igual a su columna izquierda)

2) selecciona en la hoja2 la primer celda para contar las coincidencias del Id buscado en cada grupo [B2]
y usa la siguiente formula:
[B2] =sumaproducto(--(hoja1!$a$5:$a$12=b$1),--($a2>=hoja1!$c$5:$c$12),--($a2<=hoja1!$d$5:$d$12))

-> la formula te devolvera el numero de veces que el "Id" buscado se encuentra en cada grupo (0 si no hay
coincidencias)
solo comprueba si mi separador de argumentos (coma) es el mismo en tu sistema

saludos,
hector.

__ OP __
> Te indico como estan los datos:
> (Ya separe los rangos en las columnas C y D con la funcion de "TEXTO EN COLUMNAS"
> de esta manera no hay problema si los grupos tiene uno o mas digitos, estan con un valor de numero)
>
> En la Hoja 1 a partir de la celda A4:
> A B C D
> 4 GRUPO RANGO INICIO FINAL
> 5 VERDE 150 - 400 150 400
> 6 ROJO 111 111 (en este caso solo tiene un valor)
> 7 ROJO 123 - 345 123 345
> 8 ROJO 678 - 900 678 900
> 9 AZUL 100 - 200 100 200
> 10 AZUL 200 - 500 200 500
> ...
> 100 MAGENTA 222-333 222 333

>>>> __ previos __
>>>>> Quisiera saber si un numero se encuentra definido en uno o varios rangos, como ejemplo:
>>>>> En la Hoja 1
>>>>> rango
>>>>>
>>>>> Grupo verde 150 - 400
>>>>> Grupo rojo 111 (en este caso solo tiene un valor)
>>>>> Grupo rojo 123 - 345
>>>>> grupo rojo 678 - 900
>>>>> Grupo azul 100 - 200
>>>>> Grupo azul 200 - 500
>>>>>
>>>>> En la Hoja 2
>>>>> ID GRUPO VERDE GRUPO ROJO GRUPO AZUL
>>>>> 122 SI
>>>>> 145 SI SI
>>>>> 400 SI SI SI
>>>>> 399 SI SI