Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
Right now 105 rows which will slowly grow over time
"T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
Kinda involved? I Love it, but need time to digest. Thanks.
"T. Valko" wrote: Here's a small sample file that demnstrates this: xExtractConcat.xls 15kb http://www.fr?eefilehosting.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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
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" wrote: 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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
Well put. THanks. I will look some more tomorrow.
"T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
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" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
I'll explain how INDEX and SMALL work in this formula later on this evening
when I have more time. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
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")*(Stat usList="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........... "T. Valko" wrote: I'll explain how INDEX and SMALL work in this formula later on this evening when I have more time. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Blanks
Got it, forgot it was an array. Functions are still wishy washy.
"PAL" wrote: 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")*(Stat usList="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........... "T. Valko" wrote: I'll explain how INDEX and SMALL work in this formula later on this evening when I have more time. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "PAL" wrote in message ... Right now 105 rows which will slowly grow over time "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "PAL" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing blanks at the end of text | Excel Worksheet Functions | |||
Removing Blanks | Excel Worksheet Functions | |||
Removing blanks from a spreadsheet | Excel Worksheet Functions | |||
Removing blanks / sorting - common methods not applicable | Excel Discussion (Misc queries) | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |