This post is about a common business problem of grabbing messy data from a transactional document and updating a data set to collect history. So in this post we will use VBA arrays to gather messy data ie consolidate it.
In my previous life, I was in Commodity Export Marketing, so I am using the ‘real world’ case of a Commercial Invoice which is a key document in any export documentation for an export transaction. The Commercial Invoice is in a very traditional, human format with bits of data here and there. In the real world, if the shipment were under a Letter of Credit, the format of the Commercial Invoice could be messier with wording needing to comply exactly to the Letter of Credit requirements which could mean spelling things differently or changing the terminology on the Commercial Invoice which makes lookups tricky. This blog uses a VBA array mapping approach I would call it, that means it grabs cell contents and not concerned with matching with field names.
Let’s say Sheet1 (codename) has the Commercial Invoice (see below and the blue shaded bits is what we want) which has the static and transactional bits and pieces of data we want to record to our ongoing data in Sheet2 together with previous invoice data (see further below in the green Table format). By the way and as I have said in previous posts, any good analyst will want the datasets stored in a database format with transactions down the page and dimensions of that data across. In less technical terms it should be long and skinny! This is so you can make use of more efficient Excel tools like Pivot Tables, Pivot Charts and lookup formulas.
OK so we can add a button and this VBA would do the job of grabbing this data for us. Now note all the data we want to collect is in Column P except for one bit which I cater for towards the end of the code. By the way when I code, I use sheet codenames so it’s more robust and not mucked up by a user changing the Tab name.
[vb]
Option Explicit
Option Base 1
Sub GrabData()
Const SOURCE_COL = 16 ‘Which is column P where all but 1 piece of our data is on the Commercial Invoice
Dim i As Integer
Dim iSource As Variant
Dim iTarget As Variant
Dim newrow As Long
‘Get the new row in Sheet2
newrow = Sheet2.Cells(Cells.Rows.Count, 2).End(xlUp).Row + 1
‘Load these simple arrays
iSource = Array(21, 26, 28, 22, 16, 12, 25, 13, 35) ‘ Vessel, Bill of Lading Date, Tonnes Loaded, Load Port, Commodity, Inv No, L/C No, Inv Date, Inv Value
iTarget = Array(2, 3, 4, 5, 6, 8, 9, 10, 11) ‘Ditto
‘Loop through the arrays
For i = 1 To 9
Sheet2.Cells(newrow, iTarget(i)).Value2 = Sheet1.Cells(iSource(i), SOURCE_COL).Value2
Next i
‘Get the other bit of data not in column P
Sheet2.Cells(newrow, 7).Value2 = Sheet1.Cells(10, 2).Value2
End Sub
[/vb]
So the array part of this post which is I think is pretty handy is mapping nine bits of information from Sheet1 ‘ISource’ rows to Sheet2 ‘ITarget’ Columns. There needs to be the same number of elements in each array (logically as you are mapping from one bit to another) and note the Option Base 1 declaration allows us the more readily understandable For i = 1 to 9 part of the For Loop. Without that declaration it would simply be For i = 0 to 8 which is still 9 elements so no big deal.
The “newrow” variable is loaded with the last row in Sheet2 plus 1 row for the new line we want to populate.
Now you might be thinking forget the array approach and just put in 9 more statements like the one used for the Customer Name ie Sheet2.Cells(newrow, 7).Value2 = Sheet1.Cells(10, 2).Value2 . True, you could use this ‘long hand approach’. The array approach is simpler to read and maintain within VBA and kind of cool but the ‘long hand’ approach works fine also. If you were using this approach to loop through 1000’s of transactions the array approach I think would be quicker.
That’s it! I think this is fairly common problem to solve out there in any business especially small business. In fact, I should be using this myself for my small consultancy! In reality, I would build in some functionality to safe guard against the button being pressed twice and the data being duplicated before updating.
If you need help with setting up a time saving solution like this please contact me especially if you are in the Gold Coast or Brisbane Qld areas or the Northern NSW region.