1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro Advice - Data Consolidation

Discussion in 'Business Applications' started by CWDENVER, Jan 18, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. CWDENVER

    CWDENVER Thread Starter

    Joined:
    Dec 1, 2006
    Messages:
    38
    I have a column that will have values changing but not in consecutive order. For example if I had range A1:A10, only A1, A4, and A9 might have values, but the rest of the cells would be blank. I need to be able to generate A1, A4, and A9 into the next column consecutively as B1, B2, and B3. The A column values could change (including the number of values) and I need the B column to react accordingly. So the next time I run the data, I might have A2, A3, A4, A6, and A10 with data in them - so I need column B to be able to react and put the values (in order) as B1, B2, B3, B4, and B5. Oh and I need all this done in a MsgBox. Can you help? Thanks so much.
     
  2. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    If you will look on the other thread, you will find that the file I attached actually does exactly this. It just doesn't put it in a message box. But the code I gave you in answer to your previous question will do that. You just have to copy it and modify it.
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Without seeing your other thread or your data structure, I would venture to guess that your spreadsheet design is probably not the best and should be revised heavily.
     
  4. CWDENVER

    CWDENVER Thread Starter

    Joined:
    Dec 1, 2006
    Messages:
    38
    In the formula you have, one part reads [if($A$1:$A$10<>"",].... Usually the <> means not equal to, but how can a range not equal something? When I use that equation by itself, it returns a VALUE error. What is this supposed to do? Thanks.

    P.S. I've tried just copying and modifying the formula to my spreadsheet, but it doesn't work in my application.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You haven't even posted a formula! Quite hard to evaluate something when you do not specify the entire formula or state what is in the specified range or even what the desired results are supposed to be! You'll need to add much more information or we cannot help you.
     
  6. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    Zack,
    The other thread is here.
    That's where my attached spreadsheet is with the array formulae.

    CW - That's what I was trying to tell you. These are array formulas. They are different from regular formulas in that they act on arrays, like A1:A10. What it does is process each element of the array in turn. And you distinguish them from regular formulas by hitting CTRL + SHIFT + ENTER all at once when you are done entering the formula. If you don't, you get an error, quite often a #VALUE error like you did. I'm betting that is why it doesn't work for you.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Cwdenver, if you wanted to make the formula dynamic to encompass everything you entered into column A, you would only need to add some named ranges and change the formula slightly.

    Add these names...

    LastRow: =MAX(MATCH(REPT("z",255),Sheet1!$A:$A),MATCH(9.99999999E+307,Sheet1!$A:$A))
    LastA: =INDEX(Sheet1!$A:$A,LastRow,1)
    LastB: =INDEX(Sheet1!$A:$B,LastRow,2)

    Then your formula would become...

    =IF(ROW(B2)-ROW($B$2:LastB)+1>ROWS($A$2:LastA)-COUNTBLANK($A$2:LastA),0,INDIRECT(ADDRESS(SMALL((IF($A$2:LastA<>"",ROW($A$2:LastA),ROW(A2)+ROWS($A$2:LastA))),ROW(B2)-ROW($B$2:LastB)+1),COLUMN($A$2:LastA),4)))

    This is also the same array-entered formula, often called CSE formula (Ctrl + Shift + Enter = CSE), ddockstader gave you, so it must be confirmed with CSE instead of just Enter. This is very important: You must CSE this formula in one cell at a time! You cannot multi-enter this formula as you would Ctrl + Enter over a range with a normal formula (if you even do that). If you do not do that, then don't worry about it. Enter the formula in a single cell and copy down as needed.

    Btw, in the formula I posted where it says ROW(B2), that is because I inserted a row for headers and anchored the function to the current formula row. Subtle differences. The function will work without them as well..

    =IF(ROW()-ROW($B$2:LastB)+1>ROWS($A$2:LastA)-COUNTBLANK($A$2:LastA),0,INDIRECT(ADDRESS(SMALL((IF($A$2:LastA<>"",ROW($A$2:LastA),ROW()+ROWS($A$2:LastA))),ROW()-ROW($B$2:LastB)+1),COLUMN($A$2:LastA),4)))

    HTH
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Seeing Zack's work makes me realize I only ever srcatched the surface of Excel, and you would need a Microscope to see the scratches. (y)
    To think that I was considered the "Expert" on Excel where I worked. :eek:
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/536424

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice