Removing Blanks

[PAL]

I have a worksheet w/ many columns and rows. On a separate worksheet, I am
trying to create a concatenated list based on 2 criteria. To make the list,,
I use the formula and drag down the number of rows. This results in a list
with blanks throughout the list and I end up manually moving the blanks. Any
way to remove the blanks automatically. This will be a pain everytime I
refresh the list.

=IF(AND(Data!D2="Approved",Data!F2=1),CONCATENATE(Data!A2,"/",Data!G2,"(",Data!E2,")"),"")

Thanks.

 

[T. Valko]

I have a worksheet w/ many ... rows.

How many rows? 100? 500? 10,000?

How big is this list (in rows) after you've created it and removed all the
blanks?

This could be done with an array formula but it would be calculation
intensive if there are more than a few hundred rows involved.

 

[PAL]

Right now 105 rows which will slowly grow over time

 

[T. Valko]

Here's a small sample file that demnstrates this:

xExtractConcat.xls 15kb

http://www.freefilehosting.net/download/3g58a

I put everything on the same sheet so it'd be easy to see what's going on.

The formula in the yellow cell counts how many rows meet the criteria.

The green cells contain the array formula** that extracts the data that
meets the criteria.

I'd use dynamic named ranges for this since it sounds like the amount of
data is added to. This will automatically update the ranges used in the
formulas.

This is kind of "involved" but it should get you started in the right
direction.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[PAL]

Kinda involved? I Love it, but need time to digest. Thanks.

 

[NoodNutt]

G'day Pal

The following is all presumptuous, I'm using a code model that I used to
transfer rows from my main sheet into a second sheet for historical
archiving

The following Code will help out a little, though it is limited, it will
halt everytime it reaches a cell in your column that is blank

It will goto the NewSheet(Historical) & insert a new row @ Row 2 where the
row from Sheet1 is copied and will continue to copy until it reaches a Blank
Cell in Sheet1. (It is set to Row2 as most people use Row1 as their Header
Row).

This may work in favour for you, as you can then identify which row has the
blank and address the issue with whoever left it blank, for what reason, if
any.

To continue with the looping code click on the next cell below that contains
data and click the CmdBtn again, it will repeat the above, continue
repeating above steps until you are satisfied.

Place a CmdBtn on Sheet1

Put this code bahind it.

Sub Move_Data()

Application.ScreenUpdating = False
ActiveCell.Select
Do Until ActiveCell.Value = ""
Sheets("Historical").Select
Range("A2").Select
Selection.EntireRow.Insert
Sheets("Sheet1").Select
ActiveCell.Range("A1:W1").Select
Selection.Copy
Sheets("Historical").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
True, Transpose:=False
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A2").Select
Loop
Sheets("Historical").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
True, Transpose:=False
Range("A2:W1000").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess,
OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
End Sub

Once all the rows containing data have been copied it will then sort the
data into the desired layout.

HTH
Mark.

 

[PAL]

Hi Biff,

All these functions are new to me, I think I am getting it but was just
hoping you could explain the beginning of the conditional....

=IF(ROWS(I$2:I2)<=I$1,

Thanks.

 

[T. Valko]

hoping you could explain the beginning of the conditional....

=IF(ROWS(I$2:I2)<=I$1

That's a "pseudo" error trap.

I$1 holds the formula that returns the number of rows that meet the
condition. In the sample file there are 3 rows where D="approved" and F=1.
This means that we need to copy the array formula to *at least* 3 cells to
get all the results.

Since this is *dynamic* and the number of rows that will meet the conditions
will change over time we need to copy the array formula to more cells to
allow for future addition of data. That's why in the sample file I've copied
the array formula to many more than just the 3 cells needed.

ROWS(I$2:I2) compares the number of cells the formula is copied to against
the value in I$1. If the formula is copied to more rows than the value of
I$1 then the formula returns a blank cell. This is a more efficient means of
error trapping. Without some means of error trapping the formula would
return #NUM! errors after the 3rd cell.

Knowing how many cells to copy the array formula to is something only you
will know. Since this is a complex array formula it's kind of calculation
intensive and for that reason you don't want to copy to more cells than is
necessary. For example, if your table has 100 rows and it's possible that
all 100 rows will meet the conditions then you'd have to copy the array
formula to 100 rows. However, if it's only possible that at most 10 rows
will meet the conditions then you have to copy the formula to 10 rows. So,
you're the only one that knows how many rows the formula needs to be copied
to taking into consideration future data addition.

 

[PAL]

Well put. THanks. I will look some more tomorrow.

 

[PAL]

Hi Bif,

I am slogging through this. It makes sense for the most part, your
explanation was great. Index and Small are new functions not well explained
for the simpleton, so there is a bit of a leap there. Thanks for your
efforts.

 

[T. Valko]

I'll explain how INDEX and SMALL work in this formula later on this evening
when I have more time.

 

[PAL]

I am almost there. Here is what I have on my spreadsheet.

Formula 1:

=SUMPRODUCT(--(BUList="American"),--(StatusList="Approved"),--(PeriodList=1))

The value returned is 3.

Formula 2:

=IF(ROWS(A$3:A3)<=A$1,INDEX(ProductList&" / "&PartList&"
("&TypeList&")",SMALL(IF((BUList="American")*(StatusList="Approved")*(PeriodList=1),ROW(BUList)-MIN(ROW(BUList))+1),ROWS(A$3:A3))),"")

When I drag this down, I get 3 respones as expected. The first one is
correct, the other two are #NUM! So close...........

 

[PAL]

Got it, forgot it was an array. Functions are still wishy washy.