Tengo que hacer coincidir los elementos de la columna de la hoja de trabajo "Sheet1" que pueden tener 30K filas con "Sheet2" elementos que pueden tener alrededor de 5K filas.

En mis datos de muestra, he tomado preguntas sobre el sitio Stack Overflow ya que Mis datos reales se parecen mucho a ellos. Los datos de Sheet2 generalmente deben ser únicos, pero las posibilidades de error humano permanecen y puede haber algunos duplicados en los que uno de los valores puede ser erróneo.

Mi consulta específica es por qué Dictionary está generando la segunda aparición del valor del elemento clave en lugar del primer valor del elemento de ocurrencia. Estoy usando un diccionario de secuencias de comandos como una búsqueda y leyendo / escribiendo todos los valores como matrices para la velocidad máxima.

Sheet2 tiene los siguientes datos de muestra.

Q_DESC                                                                               Q_ID   Q_NO
vba  array  assign  values  for  loop                                                300003 34595616
adding  values  to  list  using  vlookup                                             300008 34517577
indirect  function  excel                                                            300018 34437000
multiple  replace  excel  vba  vba                                                   300004 34538197
indirect  function  excel                                                            300018 34438222
how  to  reference  a  cell  in  another  excel  worksheet  using  id  from  a  formula300020 34401572

Sheet1 tiene los siguientes datos de muestra.

Q_DESC                                                                                Q_ID  Q_NO
copy  excel  worksheet  range  and  paste  into  outlook  as  a  picture             300002 34599462
vba  array  assign  values  for  loop                                                300003 34595616
use  a  for  to  move  from  cells                                                   300004 34538197
move  rows  to  another  sheet  if  meeting  criteria                                300005 34534837
subscript  out  of  range  error  excel  copy  paste                                 300006 34532985
extract  data  from  excel  file  joined  by  type  of  data  in  sheets             300007 34518747
adding  values  to  list  using  vlookup                                             300008 34517577
vba  workbook  close  function  cannot  make  this  work  within  required  location 300009 34514214
excel  vba  how  to  maintain  a  cell  value  from  an  instant  of  a  changing  variable 300010  34500540
excel  vba  open  csv  in  notepad  and  copy  all  to  excel                        300011 34498063
if  function  with  8  conditions   300012  34481381
excel  2013  how  to  copy  selective  columns  form  different  workbook            300013 34483403
formula  in  cells  missing  after  running  vba  macro                              300014 34464421
how  to  work  with  selection  range  in  excel  cell                               300015 34460417
multiple  replace  excel  vba  vba                                                   300016 34447116
copying  without  the  clipboard  and  without  formatting                           300017 34443932
indirect  function  excel                                                            300018 34438222
hiding  columns  based  on  cell  value  using  vba                                  300019 34424332
how  to  reference  a  cell  in  another  excel  worksheet  using  id  from  a  formula300020   34401572

El siguiente fragmento de código para la clave del diccionario y el valor de los elementos proporciona la salida mencionada posteriormente.

 Dim key As Variant
    For Each key In dict.Keys
        Debug.Print key, dict(key)
    Next key

Los resultados obtenidos son

    vba  array  assign  values  for  loop                                           34595616 
    adding  values  to  list  using  vlookup                                        34517577 
    indirect  function  excel                                                       34438222 
    multiple  replace  excel  vba  vba                                              34538197 
    how  to  reference  a  cell  in  another  excel  worksheet  using  id  from  a  formula    34401572 

Necesito ayuda para comprender por qué para la clave indirect function excel está imprimiendo el segundo valor del elemento, es decir, 34438222 en lugar del primer valor de aparición que es 34437000 ya que la clave ya existe.

El código adoptado por mí se basa en una de las respuestas proporcionadas por Tim Williams en este sitio como se menciona a continuación.

 Sub MatchItems()

    Dim ws1 As Worksheet
    Dim rws As Worksheet, t, arr1, arr2
    Dim dict As Object, rw As Range, res(), arr, nR As Long, i As Long

    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set rws = ThisWorkbook.Sheets("Sheet2")
    Set dict = CreateObject("scripting.dictionary")

    t = Timer

    'create a lookup from two arrays
    arr1 = rws.Range("a1").CurrentRegion.Columns(1).Value
    arr2 = rws.Range("a1").CurrentRegion.Columns(3).Value
    For i = 2 To UBound(arr1, 1)
        dict(arr1(i, 1)) = arr2(i, 1)
    Next i
    Dim key As Variant
    For Each key In dict.Keys
        Debug.Print key, dict(key)
    Next key
    Debug.Print "created lookup", Timer - t

    'get the values to look up
    arr = ws1.Range(ws1.Range("A2"), ws1.Cells(Rows.Count, 1).End(xlUp))
    nR = UBound(arr, 1)        '<<number of "rows" in your dataset
    ReDim res(1 To nR, 1 To 1) '<< resize the output array to match

    'perform the lookup
    For i = 1 To nR
        If dict.exists(arr(i, 1)) Then
            res(i, 1) = dict(arr(i, 1))
        Else
            res(i, 1) = ""
        End If
    Next i

    ws1.Range("E2").Resize(nR, 1).Value = res '<< populate the results

    Debug.Print "Done", Timer - t

End Sub
0
skkakkar 20 dic. 2019 a las 16:25

2 respuestas

La mejor respuesta

Parece que no desea escribir en el diccionario si ya existe un valor para esa clave. Si ese es el caso, simplemente reemplace esto:

For i = 2 To UBound(arr1, 1)
    dict(arr1(i, 1)) = arr2(i, 1)
Next i

Con esto:

For i = 2 To UBound(arr1, 1)
    If Not dict.exists(arr1(i, 1)) Then dict(arr1(i, 1)) = arr2(i, 1)
Next i
2
Josh Eller 20 dic. 2019 a las 14:54

Su respuesta se encuentra en este fragmento de código que básicamente recorre toda la columna con valores repetidos.

'create a lookup from two arrays
arr1 = rws.Range("a1").CurrentRegion.Columns(1).Value
arr2 = rws.Range("a1").CurrentRegion.Columns(3).Value
For i = 2 To UBound(arr1, 1)
    dict(arr1(i, 1)) = arr2(i, 1)
Next i

Donde el último registro (y no segundo registro) se rellena en el dict. Debe usar el método Exists o, alternativamente, puede modificar el bucle for como se muestra a continuación para devolver el primer registro, p.

'create a lookup from two arrays
arr1 = rws.Range("a1").CurrentRegion.Columns(1).Value
arr2 = rws.Range("a1").CurrentRegion.Columns(3).Value
For i = UBound(arr1, 1) To LBound(arr1, 1) Step -1
    dict(arr1(i, 1)) = arr2(i, 1)
Next i

Hth

1
shrivallabha.redij 20 dic. 2019 a las 14:53