If you are also in the position of moving from Joomla!’s DOCman to WP Download Manager you may find yourself needing to reassign categories for your entries. DOCman’s categories exist within DOCman whereas WP Download Manager’s categories exist with other categories within the site.
Fortunately, Excel presents a quick and easy way to do this. This is also good for if you need to match cells with values from a different sheet.
Excel’s VLOOKUP is your best friend in this situation. Take, for instance, these two tables, within the same sheet:
On the left we have our table we want to populate “Category description” with from our table on the right, marching on the ID column.
This is done simply by using this function:
VLOOKUP(lookup_value, table_array, col_index[range_lookup])
In our case we can use this:
=VLOOKUP(B2, Affiliation!A2:B8, 2)
Where:
B2 = the value we are searching
Affiliation!A2:B8 = The sheet “Affiliation” and the cells A2 through B8
2 = The column index from the table_array containing the value we want returned
In this instance the value returned in “Autobot”:
So now you want to paste that formula in every cell and move on about your day:
Yeah. It’s not that easy. Because Excel wants to help you out, you need to tell it “friend, please preserve the scope of the array we are selecting! It’s simple – just add $ before the values to preserve:
=VLOOKUP(B2, Affiliation!$A$2:$B$8, 2)
You can then select all of the cells in that column and paste the function:
And want to know something even cooler? If you change the value of the reference column on Sheet 2 (Affiliation) it automatically updates it on Sheet 1:
The new Sheet 1:
So when you need to match your new WordPress categories with your DOCman categories you can use this simple function to make like easier.