Excel Macro Advice - Data Consolidation

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 
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.
 
Joined
Jul 25, 2004
Messages
5,458
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
 

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:
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top