Array, Vlookup and Tables

[TheRedCardinal]

I have the following requirements:

• Look through 2 columns on 2 different sheets
• Identify all of the unique values in those 2 columns
• From that list then check a table in the workbook and add further details sourced from that table

I've run into a couple of problems.

The first is based on finding the unique values. I put together this code:

Set ObjDict = CreateObject("Scripting.Dictionary")
TaxCodeArray = Application.Transpose(WS2.Range("C2", Cells(Rows.Count, "C").End(xlUp)))

For Counter = 1 To UBound(TaxCodeArray, 1)
    ObjDict(TaxCodeArray(Counter)) = 1
Next

This works fine when on the WS2 worksheet (WS2 has been set earlier) but it doesn't work when either calling the sub from a button, or running it in VBA when another sheet is selected.
As I need to expand the formula to include the second sheet, I don't know what is going wrong with the above that it is only looking up the current active sheet.

Next stage is to create an array which is (y x 3) for my 2 additional pieces of info.

I want to lookup each unique value in a table called "TaxCodeDetails" in the column called "Tax Code" and then return into the 2nd column of the array the corresponding description.

This became my code:

TaxCodeArray(Counter,2) = Index(TaxCodeDetails[[#Headers],[Tax Code]], Match(TaxCodeArray(Counter,1), TaxCodeDetails[#Headers],[Description]], False))

This time the error is a compile error telling me I need a separator or ")" and then highlights the "[[#Headers],[Tax Code]]" section of the line which looks complete to me.

So my 2 questions are:

1. Is this method just too clunky (I will eventually output the array to a worksheet)
2. What is going wrong with my Index/Match function?

Thanks!

 

[jasonb75]

For the first part, I think that you might just need to qualify the second part of the range with WS2.

TaxCodeArray = Application.Transpose(WS2.Range("C2", WS2.Cells(Rows.Count, "C").End(xlUp)))

The second part, you have used worksheet formula syntax, not vba syntax, you need something like

TaxCodeArray(Counter,2) = Range("TaxCodeDetails[[#Headers],[Tax Code]]").Cells(Application.Match(TaxCodeArray(Counter,1), Range("TaxCodeDetails[#Headers],[Description]]"), False),1)

Note that I've only edited your code here, it has not been tested.

 

[TheRedCardinal]

Hi Jason,
Thanks for this. It didn't quite work for me.

I originally thought my array was causing the problem (when I transposed the contents of the Dictionary into it, it re-dimmed from 2D to 1D) so I reworked that section as follows:

For Each Entry In ObjDict.Keys
    TaxCodeArray(1, Counter) = Entry
    Counter = Counter + 1
Next Entry

Now my array is nicely preserved - this might have been unnecessary but at least I have ruled that error out.

Now at these two lines:

  TaxCodeArray(2, Counter) = Range("TaxCodeDetails[[#Headers],[Tax Code]]").Cells(Application.Match(TaxCodeArray(1, Counter), Range("TaxCodeDetails[#Headers],[Description]]"), False), 1)
    TaxCodeArray(3, Counter) = Range("TaxCodeDetails[[#Headers],[Tax Code]]").Cells(Application.Match(TaxCodeArray(1, Counter), Range("TaxCodeDetails[#Headers],[Indicator]]"), False), 1)

I had to rework it as I had my arrays the wrong way around.

In summary what I'm trying to do is:

• Find the value of taxcodearray (1, Counter) in a table called TaxCodeDetails
• Return the corresponding value from the column called "Description" into taxcodearray (2, Counter)
• Return the corresponding value from the column called "Indicator" into taxcodearray (3, Counter)

Thanks!

 

[jasonb75]

Find the value of taxcodearray (1, Counter) in a table called TaxCodeDetails

I think I missed a red flag when I was editing your code previously, things don't always stand out as well with table ranges as they do with normal ranges.
'in a table called' implies that you need to look for the value in an array of rows and columns, if that is the case then you would need to use the vba Range.Find method instead.
I'm finding it a little confusing because your reference appears to be a single cell.
Could you post a copy of the table, or a simplified version with any confidential info fictionalised?

 

[TheRedCardinal]

Of course, and thanks for your ongoing help.

Attached is a file in my google drive - hope that is the correct protocol?

So the current macro loops through the data in "Sample Data" and creates a unique list of tax codes in the column Tx.
It then creates an array,3 x number of unique tax codes
The tax codes are loaded into "column 1" of the array

I then want to look up that value in the table in the sheet "Table" - find the code in the header Tax Code, and add Description and Indicator to columns 2 and 3 of the array.

So my array should look like this:

MU	20%PurchService	RC
MT	20%AcqGoods	A
MB	0& Input Freight	P
B1	20%STO Outp(GB-EU)	S

Once this is done I will transpose the array into the table called "Summary Table" on the sheet "Summary Sheet" and then perform SUMIFs on the source data so I can get final data values.

Hope this is clearer.

Any advice on making the steps less complex would be welcomed.

Sample File

 

[jasonb75]

I think that this should do what you need, I was misreading / overthinking some of the details in your earlier post.

TaxCodeArray(2, Counter) = Range("TaxCodeDetails[Description]").Cells(Application.Match(TaxCodeArray(1, Counter), Range("TaxCodeDetails[Tax Code]"), False), 1)
TaxCodeArray(3, Counter) = Range("TaxCodeDetails[Indicator]").Cells(Application.Match(TaxCodeArray(1, Counter), Range("TaxCodeDetails[Tax Code]"), False), 1)

Another way would be

TaxCodeArray(2, Counter) = Range("TaxCodeDetails[Tax Code]").Find(TaxCodeArray(1, Counter), , xlValues, xlWhole, , , False, False, False).Offset(, 1).Value
TaxCodeArray(3, Counter) = Range("TaxCodeDetails[Indicator]").Find(TaxCodeArray(1, Counter), , xlValues, xlWhole, , , False, False, False).Offset(, 3).Value

I think that I have the syntax right, not sure which will process more efficiently though.

 

[TheRedCardinal]

Looks like it worked!

The dataset is not big enough to worry about processing time so I went with the first one.

Thanks!