+ Reply to Thread
Results 1 to 5 of 5

shifting refence tables of 18 rows after 18 rows keeping them blocked over an area

  1. #1
    Registered User
    Join Date
    04-22-2013
    Location
    Milan, Italy
    MS-Off Ver
    Enterprise 2010
    Posts
    8

    shifting refence tables of 18 rows after 18 rows keeping them blocked over an area

    Hello guys,

    I'm having a problem with a formula I have to repeat a lot of times that, if I can make automatic, can save me literally a lot of time.
    The formula is the following:

    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD2;FALSE);"#N/A N/A")

    in cell B2, and I drag it down until B17 and then to the right until AB17. In cell 1 there is what I need to look in the HLOOKUP formula, and are fixed values.

    In the AD column I have this formula:

    =MOD(ROW();18)

    and I want this formula to be copied down in cell B20 with this result

    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD2;FALSE);"#N/A N/A")

    to be dragged down and to the right again in order to cover that area, and then down again in cell B38 to be like

    =IFERROR(HLOOKUP(B$1;Prep!$B$37:$AB$53;$AD2;FALSE);"#N/A N/A")


    An example of how the formula appear in the B column is:

    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD2;FALSE);"#N/A N/A") -> starting in B2, in B1 I have years, 1986 to 2012
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD3;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD4;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD5;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD6;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD7;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD8;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD9;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD10;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD11;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD12;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD13;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD14;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD15;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD16;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD17;FALSE);"#N/A N/A")


    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD2;FALSE);"#N/A N/A") > starting in B20, in B19 I have years, 1986 to 2012
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD3;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD4;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD5;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD6;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD7;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD8;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD9;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD10;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD11;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD12;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD13;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD14;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD15;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD16;FALSE);"#N/A N/A")
    =IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD17;FALSE);"#N/A N/A")

    I will then drag them to the right to fill the space until the AB column.
    I have to use this formula until cell B53984 that's why I'm asking if there is any way to make it automatic.

    Let me explain why I'm trying to do this: in the Prep worksheet I have 3000 companies and I'm analyzing a time series data for 16 balance sheet items. I'm analyzing this starting from 1986 to 2012. The problem is that I'm downloading this with Bloomberg, and on the B column data starts from the first year available for that company, and it might also skip years where there is no data available for that specific company. So in the B column a company might have data from 1997, another from 1989, another from 2001, and it might happen that the first does not have a 1998 so in the C column I have 1999.
    What I'm doing is organizing data in order to have each year in the same column: I'm therefore looking for the year in the Prep sheet (raw 1 in the first case) and if it's the same as the one in my actual sheet it then pastes the value of that row in the new table; if there is not it will put #N/A N/A. So all companies will have the same years in the same column.

    The problem is that I have to do this 3000 times (then copy this excel worksheet into others and adjust formulas, since I have other 24 sheets where I have to do this). I managed to make the row selection automatic using the =mod(row();18) formula, but I cannot make the table selection shift down automatically.

    Is there any way to make it automatic, maybe using macros (which I've never used before)? I just cannot adjust it manually for so many times.

    I don't know if I can upload the file since it's around 60MB.

    If you need more infos to understand my problem and help me, please feel free to ask.

    Thank you again for your help,

    Manuel

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: shifting refence tables of 18 rows after 18 rows keeping them blocked over an area

    Hi

    Can you put up an example file showing the raw data, and how you want it formatted. Put up enough data to cover samples of possible variations, but make it small enough to be loaded.

    rylo

  3. #3
    Registered User
    Join Date
    04-22-2013
    Location
    Milan, Italy
    MS-Off Ver
    Enterprise 2010
    Posts
    8

    Re: shifting refence tables of 18 rows after 18 rows keeping them blocked over an area

    Hello Rylo,

    I'm attaching a sample file of what I'd like to do.
    The Prep worksheet is all made of formulas, capturing the data from the downloaded sheet from Bloomber but transforming each date into only years with the =year() formula, because otherwise if I look for the year only in the downloaded sheet it won't find a corrispondence because dates are downloaded as 31/12/2012 for example, but for different companies there might be a 29/12/2012 or 31/12/2012 (Italian format for dates, dd/mm/yyyy, and if I look for the year only with the HLOOKUP formula it won't work.
    I've simply pasted the results of the transformation, but as the name says, the Prep worksheet is a "preparation" worksheet for this job.

    This is only a portion of the whole job, the real file goes down until row 53999.


    ExampleProblem.xlsx

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: shifting refence tables of 18 rows after 18 rows keeping them blocked over an area

    Hi

    Based on your example file try this
    Sheet2!B2:
    Please Login or Register  to view this content.
    Copy this down / across as required.

    HTH

    rylo

  5. #5
    Registered User
    Join Date
    04-22-2013
    Location
    Milan, Italy
    MS-Off Ver
    Enterprise 2010
    Posts
    8

    Re: shifting refence tables of 18 rows after 18 rows keeping them blocked over an area

    It seems working perfectly so far.
    I'll update this when I will complete copying-pasting this till the end of the file.

    Many thanks for the help

    UPDATE:
    Many thanks, it works perfectly!

    Can you help me with another little thing? How can I make a macro that does this in automatic? Like pressing the combination and it pastes the whole thing there and then switches down. Then I press it again and it pastes and go down to the next group. I know some basic programming language, but this is beyond my personal knowledge.

    Many thanks again.
    Last edited by maxgue; 04-24-2013 at 06:31 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1