Lookup Tables?

[Christine Wilso]

I receive data each day exported into an excel file. In column A there
is a list of Areas, in column B a list of months from April to whatever
the month we are in now, and C,D & E contain values.
For example:
A B C D E
Liverpool April x y z
Liverpool May a x b
Manchester April c z z
Manchester May b c d
In another spreadsheet I have these same areas with all 12 months
listed from April to March.
Rather than copying and pasting each individual section from one
worksheet to another, how do I paste the data from column C,D & E from
the chart above placing it next to the appropriate month & town and
leaving blank the months we have not reached yet.

Do I need to use some type of lookup table? If so how? As I have to do
this every day I can then build a macro around it, but I don't know how
to do the copy paste bit yet.

 

[Guest]

Hi

When I have had this kind of problem in the past I have used a pivot table to organize the data. If you can do that, then you can write a "GETPIVOTDATA" formula to extract it to the right place in your summary sheet.

If you aren't comfortable with pivot tables, you might need to come back for more help.

OR

A simpler way might be to create a helper column to the left of your data which concatenates your location and month, then you can use a VLOOKUP formula to find your data.

i.e. insert a new column A. Enter in A2: B2&" "&TEXT(B3,"mmmm")

Then in your summary sheet you can create a helper column to do the same thing in (say) column F. your lookup formulae would be something like:

=IF(ISNA(VLOOKUP($F1,Sheet1!$A$1:$F$100,4,FALSE)),0,VLOOKUP($F1,Sheet1!$A$1:$F$100,4,FALSE))

The third argument would change to 5 or 6 for the other data columns. The FALSE argument at the end makes the formula find an exact match.
The ISNA test is used to return 0 instead of #N/A if there is no exact match.

Hope this helps a bit.