| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditioning an array formula
Teethless Mama gave me the following formula for my previous question:
=IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditioning an array formula
I would like this done without using a pivot table or filter. Many thanks for
your help! "Verlaesslichkeit" wrote: Teethless Mama gave me the following formula for my previous question: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditioning an array formula
=IF(ISERR(SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))),"",
INDEX(data,SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1)))) -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Teethless Mama gave me the following formula for my previous question: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditioning an array formula
Hi Bob! This did not do it. I saw what you did in the formula, I tried
something similar before but it did not work. The result is always "". "Bob Phillips" wrote: =IF(ISERR(SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))),"", INDEX(data,SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1)))) -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Teethless Mama gave me the following formula for my previous question: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditioning an array formula
Then you will need to give us more info, like some sample data, as it worked
in my test. -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Hi Bob! This did not do it. I saw what you did in the formula, I tried something similar before but it did not work. The result is always "". "Bob Phillips" wrote: =IF(ISERR(SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))),"", INDEX(data,SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1)))) -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Teethless Mama gave me the following formula for my previous question: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditioning an array formula
Hi Bob: Here the info!
Now I would like to have a list with all numbers starting with 4101 and then a list with numbers starting with 4102 depending on a value lets say in cell B5. 4101101 4101201 4101202 4101301 4101401 4101501 4101601 4101701 4101801 4101901 41011001 41011101 41011205 41011301 41011401 4102101 4102203 4102301 4102401 4102502 4102601 Thanks! "Bob Phillips" wrote: Then you will need to give us more info, like some sample data, as it worked in my test. -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Hi Bob! This did not do it. I saw what you did in the formula, I tried something similar before but it did not work. The result is always "". "Bob Phillips" wrote: =IF(ISERR(SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))),"", INDEX(data,SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1)))) -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Teethless Mama gave me the following formula for my previous question: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditioning an array formula
Try this:
B5 = 4101 Enter this formula in C5. This will return the number of items that meet the condition and it will also act as the error trap test cell: =IF(B5="",0,SUMPRODUCT(--(LEFT(Data,LEN(B5))=B5&""))) Then, to extract those items use this array formula** entered in D5 and copied down until you get blanks: =IF(ROWS(D$5:D5)<=C$5,INDEX(Data,SMALL(IF(LEFT(Dat a,LEN(B$5))=B$5&"",ROW(Data)),ROWS(D$5:D5))-MIN(ROW(Data))+1),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Verlaesslichkeit" wrote in message ... Hi Bob: Here the info! Now I would like to have a list with all numbers starting with 4101 and then a list with numbers starting with 4102 depending on a value lets say in cell B5. 4101101 4101201 4101202 4101301 4101401 4101501 4101601 4101701 4101801 4101901 41011001 41011101 41011205 41011301 41011401 4102101 4102203 4102301 4102401 4102502 4102601 Thanks! "Bob Phillips" wrote: Then you will need to give us more info, like some sample data, as it worked in my test. -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Hi Bob! This did not do it. I saw what you did in the formula, I tried something similar before but it did not work. The result is always "". "Bob Phillips" wrote: =IF(ISERR(SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))),"", INDEX(data,SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1)))) -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Teethless Mama gave me the following formula for my previous question: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditioning an array formula
Thats works!
You guys are great. Thanks a lot! "T. Valko" wrote: Try this: B5 = 4101 Enter this formula in C5. This will return the number of items that meet the condition and it will also act as the error trap test cell: =IF(B5="",0,SUMPRODUCT(--(LEFT(Data,LEN(B5))=B5&""))) Then, to extract those items use this array formula** entered in D5 and copied down until you get blanks: =IF(ROWS(D$5:D5)<=C$5,INDEX(Data,SMALL(IF(LEFT(Dat a,LEN(B$5))=B$5&"",ROW(Data)),ROWS(D$5:D5))-MIN(ROW(Data))+1),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Verlaesslichkeit" wrote in message ... Hi Bob: Here the info! Now I would like to have a list with all numbers starting with 4101 and then a list with numbers starting with 4102 depending on a value lets say in cell B5. 4101101 4101201 4101202 4101301 4101401 4101501 4101601 4101701 4101801 4101901 41011001 41011101 41011205 41011301 41011401 4102101 4102203 4102301 4102401 4102502 4102601 Thanks! "Bob Phillips" wrote: Then you will need to give us more info, like some sample data, as it worked in my test. -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Hi Bob! This did not do it. I saw what you did in the formula, I tried something similar before but it did not work. The result is always "". "Bob Phillips" wrote: =IF(ISERR(SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))),"", INDEX(data,SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1)))) -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Teethless Mama gave me the following formula for my previous question: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditioning an array formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Verlaesslichkeit" wrote in message ... Thats works! You guys are great. Thanks a lot! "T. Valko" wrote: Try this: B5 = 4101 Enter this formula in C5. This will return the number of items that meet the condition and it will also act as the error trap test cell: =IF(B5="",0,SUMPRODUCT(--(LEFT(Data,LEN(B5))=B5&""))) Then, to extract those items use this array formula** entered in D5 and copied down until you get blanks: =IF(ROWS(D$5:D5)<=C$5,INDEX(Data,SMALL(IF(LEFT(Dat a,LEN(B$5))=B$5&"",ROW(Data)),ROWS(D$5:D5))-MIN(ROW(Data))+1),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Verlaesslichkeit" wrote in message ... Hi Bob: Here the info! Now I would like to have a list with all numbers starting with 4101 and then a list with numbers starting with 4102 depending on a value lets say in cell B5. 4101101 4101201 4101202 4101301 4101401 4101501 4101601 4101701 4101801 4101901 41011001 41011101 41011205 41011301 41011401 4102101 4102203 4102301 4102401 4102502 4102601 Thanks! "Bob Phillips" wrote: Then you will need to give us more info, like some sample data, as it worked in my test. -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Hi Bob! This did not do it. I saw what you did in the formula, I tried something similar before but it did not work. The result is always "". "Bob Phillips" wrote: =IF(ISERR(SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))),"", INDEX(data,SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1)))) -- __________________________________ HTH Bob "Verlaesslichkeit" wrote in message ... Teethless Mama gave me the following formula for my previous question: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) it would be excellent if someone could add the following condition, as my array skills are scarce: The list will only include numbers where the first four numbers coincide with a cell (lets say E5). Many Thanks! That was great. But Im not very good at array formulas... complicating my results. I would like to do two more things with this 1. I want to do this for all the numbers in the list where the first four numbers coincide with a cell (lets say E5). (Necessary) 2. And I want the result to be just part of the code =MID(data;4;10). (Optional) To clarify, the long list has has many subcodes and I want not only the list of: 4101801 4101901 41011001 41011101 41011205 41011301 but also a list of in another part of the spreadsheet of 44041001 44041101 44041201 44041202 44041301 44041401 44041501 That is why I need the condition. Many Thanks! "Teethless mama" wrote: =IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed "Verlaesslichkeit" wrote: My column looks like this 4101801 4101901 41011001 41011101 there are spaces here 41011205 41011301 Now, I would like to have this list without spaces (without using a filter) purely by using formulas at another location, so that the final result looks like this: 4101801 4101901 41011001 41011101 41011205 41011301 I have been trying for hours but dont get tanywhere, Im probably thinking way to complicated. Many thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for conditioning a cell | Excel Discussion (Misc queries) | |||
| Conditioning formatting | Excel Worksheet Functions | |||
| Conditioning?? | Excel Worksheet Functions | |||
| cell conditioning | New Users to Excel | |||
| conditioning | Excel Worksheet Functions | |||
Linear Mode
