Excel - BUSCAR EN RANGO, MENOR VALOR CON CONDICIONES Y ARROJAR TEXTO

   
Vista:

BUSCAR EN RANGO, MENOR VALOR CON CONDICIONES Y ARROJAR TEXTO

Publicado por octavio (7 intervenciones) el 26/10/2014 07:12:57
ESPERO ME PUEDA AYUDAR!!!

El siguiente es un ejemplo en donde proveedores diversos pueden tener el mismo producto. la columna Proveedor tiene los nombres de proveedores, la de En existencia indica 1 para los que si hay y 0 para los que no hay, la de "Veces que se repite producto" indica el número de proveedores que tiene el producto" la de producto indica el modelo, y la de costo indica el costo. Para lo que necesito y SUPLICO me apoyen es para automatizar la columna de valor esperado. Es para un sistema que se basa en este excel y solo muestra los valores que dice visible. Los que se indiquen como "visibles" deben ser, el más económico que haya en existencia de entre los proveedores que tengan disponible el mismo producto o el único que haya en existencia. El planteamiento es este:

Si es un item con valor en columna B>0, (es decir en existencia) y valor en columna C>1 (es decir, varios proveedores tienen el producto) , entonces buscar "PRODUCTOS" que se repitan en la columna de D de los proveedores que tengan la columna B>0 (es decir en existencia) y arrojar el valor "visible" para el que contenga el MENOR VALOR del de la columna (costos) y el valor "oculto" para los demás. De lo contrario (else) SI columna B>0 (en existencia) y columna C=1 (es decir, es el único que cuenta con el producto) arrojal el valor "visible" de lo contrario (else) SI valor en columna B=0 (no hay en existencia) arrojar el valor "hidden"

Explicado de otra forma. Se requiere que si el renglon tiene valor en la columna (En EXISTENCIA>0 y VECES QUE SE REPITE PRODUCTO>1) busque el menor de los valores de la columna (COSTOS) a partir de los repetidos en el (PRODUCTO) y le arroje a este el valor de "visible" y a los demás el valor de "oculto". Además si la columna (EN EXISTENCIA) tiene valor =1 y en Repetidos=1 arrojar valor "VISIBLE", de lo contrario arrojar valor "OCULTO" a los restantes

EL COLOR DEL RENGLON SOLO INDICA EL PROVEEDOR QUE TIENE EL PRODUCTO

ejemplo
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de JuanC

BUSCAR EN RANGO, MENOR VALOR CON CONDICIONES Y ARROJAR TEXTO

Publicado por JuanC juanc2942@gmail.com (1052 intervenciones) el 26/10/2014 12:39:14
Fiel a mi estilo lo hice con una macro (no sé si pueda hacerse con fórmulas)...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
Option Explicit
Option Private Module
Option Base 1
 
'//By JuanC - Oct. 2014
 
'//(A)(B)(C) Sólo son válidos si la tabla está ordenada por productos.
'//(cada línea reduce el rango de productos para mayor velocidad de trabajo)...
 
Type mt
     sRef As String
     dCosto As Double
End Type
 
Private Const cOCULTO = "oculto"
Private Const cVISIBLE = "visible"
 
Sub main()
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
Call vo(Range("F4:F18"))  '//Rango de Valor esperado...
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
End Sub
 
Private Sub vo(ByVal rng As Range)
Dim cell As Range, cell2 As Range, rngProducto As Range
Dim vit() As mt, n&, min#, i&, sProducto$
 
rng.Value = ""
Set rngProducto = rng.Cells(1, 1).Offset(, -2).Resize(rng.Count) '//Rango de productos...
 
For Each cell In rng    '//Rango de valor Esperado...
    With cell
         If .Value <> "" Then GoTo siga          '//Valor esperado ya completado ('oculto'/'visible')...
            If .Offset(0, -4).Value <> 1 Then    '//Sin existencia...
               .Value = cOCULTO
               If rngProducto.Count > 1 Then Set rngProducto = rngProducto.Resize(rngProducto.Count - 1).Offset(1) '(A)
            Else
                If .Offset(0, -3).Value = 1 Then '//Existe, ¿único proveedor?...
                   .Value = cVISIBLE
                   If rngProducto.Count > 1 Then Set rngProducto = rngProducto.Resize(rngProducto.Count - 1).Offset(1) '(B)
                Else
                    sProducto = .Offset(, -2).Value '//Producto a evaluar...
                    n = 0
                    min = 100000000
 
                    For Each cell2 In rngProducto
                        If n >= .Offset(, -3).Value Then Exit For  '//Alcanzó repeticiones del producto...
                        If sProducto = cell2.Value Then            '//¿Mismo producto?...
                           n = n + 1
                           ReDim Preserve vit(n)
                           vit(n).dCosto = cell2.Offset(, 1).Value          '//Guarda costo del producto y referencia de 'Valor esperado' para el producto...
                           vit(n).sRef = cell2.Offset(0, 2).Address(0, 0)
                           If vit(n).dCosto < min Then min = vit(n).dCosto  '//Calcula costo mínimo...
                        End If
                    Next
 
                    For i = 1 To UBound(vit)         '//Completa Valor esperado para el grupo de productos basado en el costo mínimo...
                        If vit(i).dCosto = min Then
                           Range(vit(i).sRef) = cVISIBLE
                        Else
                             Range(vit(i).sRef) = cOCULTO
                        End If
                    Next
 
                    If rngProducto.Count > n Then Set rngProducto = rngProducto.Resize(rngProducto.Count - n).Offset(n) '(C)
            End If
         End If
    End With
siga:
Next
Erase vit
End Sub

Saludos, desde Baires, JuanC
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

BUSCAR EN RANGO, MENOR VALOR CON CONDICIONES Y ARROJAR TEXTO

Publicado por octavio (7 intervenciones) el 26/10/2014 22:35:41
Juan C Te agradezco muchisimo el apoyo, solo corroborando la macro, solo sirve si estan los datos ordenados pro producto cierto? Gracias!!!!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de JuanC

BUSCAR EN RANGO, MENOR VALOR CON CONDICIONES Y ARROJAR TEXTO

Publicado por JuanC juanc2942@gmail.com (1052 intervenciones) el 26/10/2014 23:06:51
no, la macro sirve de cualquier manera, las líneas A, B y C sólo sirven si está ordenado por producto...

Saludos, desde Baires, JuanC
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Nolberto

BUSCAR EN RANGO, MENOR VALOR CON CONDICIONES Y ARROJAR TEXTO

Publicado por Nolberto nlr@formulasexcel.com (81 intervenciones) el 27/10/2014 16:07:35
También se puede hacer con formula.

En esta pagina hay un ejemplo sobre como encontrar proveedor con mejor precio.
http://formulasexcel.com/formulas-excel-para-buscar-proveedor-con-mejor-precio/

Solo hace falta hacer unos pequeños cambios a la segunda formula que se usa en ese ejemplo, básicamente se pone una condicionante para que cuando el precio no sea el mas bajo, ponga oculto, de lo contrario pone visible.

En esa misma pagina esta el archivo de ejemplo para descargar, la formula en ese caso quedaría como sigue, solo debes adaptarla a tu caso.

=SI(C12<>INDICE($C$12:$C$21,COINCIDIR(K.ESIMO.MENOR(($A$12:$A$21=A12)*($C$12:$C$21),CONTARA($A$12:$A$21)-CONTAR.SI($A$12:$A$21,A12)+1),$C$12:$C$21,0),1),"oculto","visible")
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

BUSCAR EN RANGO, MENOR VALOR CON CONDICIONES Y ARROJAR TEXTO

Publicado por octavio (7 intervenciones) el 28/10/2014 17:42:27
Nolberto te agradezco mucho tu apoyo, solo que necesitaría que además yo tengo una a columna que tiene un valor binario el cual es 1 si el producto está en existencia o 0 si el producto esta agotado, faltaría agregar esto a tu excelente ejemplo. Esto me debe servir para arrojar el valor "visible" cuando el producto no lo tiene otro proveedor o cuando el producto lo pueden tener varios proveedores pero no necesariamente el mas económico lo tenga en existencia. Crees que me puedas ayudar basándote en tu mismo ejemplo? Todo esto es, para que un sistema determine que productos mostrar y que productos ocultar sin duplicar ni mostrar los más caros. GRACIAS!!!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

BUSCAR EN RANGO, MENOR VALOR CON CONDICIONES Y ARROJAR TEXTO

Publicado por octavio comtech@live.com.mx (7 intervenciones) el 02/11/2014 03:37:56
Nolberto te agradezco mucho tu apoyo, solo que necesitaría que además yo tengo una a columna que tiene un valor binario el cual es 1 si el producto está en existencia o 0 si el producto esta agotado, faltaría agregar esto a tu excelente ejemplo. Esto me debe servir para arrojar el valor "visible" cuando el producto no lo tiene otro proveedor o cuando el producto lo pueden tener varios proveedores pero no necesariamente el mas económico lo tenga en existencia. Crees que me puedas ayudar basándote en tu mismo ejemplo? Todo esto es, para que un sistema determine que productos mostrar y que productos ocultar sin duplicar ni mostrar los más caros. GRACIAS!!!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar