If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup Tables?
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. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Lookup Tables?
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. "Christine Wilso " wrote: 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. --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|