vendredi 31 juillet 2015

Return matching values in other sheet according to multiple criteria

Warning: Complex situation requires wall of text

What I have as data

In sheet A, I have alphanumerical numbers In column A and sometimes, suppliers in columns B, C, D.

 colA      colB   colC   colD

H-19328    SupA   SupB   SupA
H-12801    SupC   SupD
H-32829    
H-23123    SupB   SupC
.......    ....   ....   ....

In sheet B, I have alphanumerical numbers in column A, and 1 supplier in column B. I also have a bunch of other information in the next columns.

 colA      colB    colC   colD

H-19328    SupA   stuffs stuffs 
H-52601    SupA   stuffs stuffs
H-3279     SupA   stuffs stuffs
H-4987123  SupB   stuffs stuffs
.......    ....   ...... ......

In sheet A, the alphanumerical number is unique in the list. The numbers in sheet A may or may not have a matching number in sheet B and vice versa. Even when the number matches, the suppliers may or may not match.

What I want to do

For each number in sheet A, I want to check if sheet B holds that number with the associated supplier. For example, for the first number H-19328, I will check if sheet B has:

 colA      colB    colC   colD

H-19328    SupA   stuffs stuffs   < This could match twice as it was twice in A
H-19328    SupB   stuffs stuffs

I don't know if the number/supplier combo will match, and if it does, I don't know how many times it will match. I want to retrieve the values from sheet B in the other columns, C and D.

What I have as code

I put the values in column A of sheet A in a dictionnary. The keys are the numbers, and the Supplier information is in an array tied to each key. The dictionnary works well. The issue is not about the dictionnary, if you are not good with them you can still help me.

Right now I have a loop that matches every key + supplier to the sheet b list and returns how many times it matched. To dispel confusion, Dict_Sup is the dictionnary. Dict_sup.items(1) is an array containing suppliers. Dict_sup.items(1)(0) is the first entry of that array. Dict_sup.items(1)(supcount) is the last entry of that array.

For i = 0 To Dict_Sup.Count - 1
    For j = 0 To supcount 'supcount is the size of the array containing the suppliers
        nb_of_matches = TimesExtracted(Dict_Sup.Keys(i), Dict_Sup.Items(i)(j))   
    Next j
Next

The function TimesExtracted looks into sheet B (which is an extract, sheet name is SupDocs) and matches what I mentioned to look at the number of matches. Here it is:

Function TimesExtracted(Key As String, Sup As String) As Integer()
    Dim lastline As Integer
    Dim AllSupDocs As Range
    Dim SupDoc As Range

    lastline = SupDocs.Range("A" & Rows.Count).End(xlUp).Row
    Set AllSupDocs = SupDocs.Range("E1:E" & lastline)

    For Each SupDoc In AllSupDocs
        If SupDoc.Value = Key And SupDoc(, 61).Value = Sup Then
              Timesextracted = TimesExtracted + 1
        End If
    Next
End Function

I would like to transform this function so that it would send the 'stuffs' on which it found matches, instead of sending the amount of matches. There are 3 'stuffs' values I want. I tried making it an Array function, but I was not successful in redimensioning the array to send back an appropriate amount of results;

Function TimesExtracted(Key As String, Sup As String) As String()
    Dim lastline As Integer
    Dim AllSupDocs As Range
    Dim SupDoc As Range
    Dim tmpArray(0) As String
    Dim j As Integer

    lastline = SupDocs.Range("A" & Rows.Count).End(xlUp).Row
    Set AllSupDocs = SupDocs.Range("E1:E" & lastline)

    For Each SupDoc In AllSupDocs
        If SupDoc.Value = Key And SupDoc(, 61).Value = Sup Then
            ReDim Preserve tmpArray(UBound(tmpArray) To UBound(tmpArray) + 2) 'adds 2 places in the array
            tmpArray(j) = SupDoc(, 3).Value
            tmpArray(j + 1) = SupDoc(, 4)Value
            j = j + 2
        End If
    Next
    Timesextracted = tmpArray 'Doing this so I can redim 
End Function

Is there a better way to return the values I want? Am i making this way too complex? If both answers are no, then what do i need to modify in this last block for it to send an array with the following information

If only SupA matched in column A100:
    (C100.Value, D100.Value)

If supA matched in A100 and matched again in A110:
    (C100.Value, D100.Value, C110.Value, D110.Value)

Aucun commentaire:

Enregistrer un commentaire