|
Identifying next number in a range and referencing it
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! |
Identifying next number in a range and referencing it
This simple, fast play might appeal to you
Source data assumed in A2 down In B2: =IF(A2="","",ROW()) Leave B1 empty In C2: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1)))) Copy B2:C2 down to cover the max expected extent of data in col A, eg down to C1000? Minimize/hide away col B. Col C will return the results that you seek, all neatly packed at the top -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "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! |
Identifying next number in a range and referencing it
=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! |
Identifying next number in a range and referencing it
I find the other way easier, having not much more formulas to deal with.
Thanks a lot though! "Max" wrote: This simple, fast play might appeal to you Source data assumed in A2 down In B2: =IF(A2="","",ROW()) Leave B1 empty In C2: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1)))) Copy B2:C2 down to cover the max expected extent of data in col A, eg down to C1000? Minimize/hide away col B. Col C will return the results that you seek, all neatly packed at the top -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "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! |
Identifying next number in a range and referencing it
Thanks a lot!
"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! |
Identifying next number in a range and referencing it
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). 2. And I want the result to be just part of the code =MID(data;4;10). 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! |
Identifying next number in a range and referencing it
|
Identifying next number in a range and referencing it
No prob. Beauty is in the eye of the beholder.
-- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "Verlaesslichkeit" wrote in message ... I find the other way easier, having not much more formulas to deal with. Thanks a lot though! |
Identifying next number in a range and referencing it
Any chance that your solution works in xl2003?
If so, could you post a link to your sample in xl2003 This would benefit those w/o the benefit of xl2007 |
Identifying next number in a range and referencing it
Max,
Since Excel 2003 does not have Label Filter, this version lost some of its beauty. http://www.savefile.com/files/1760956 |
Identifying next number in a range and referencing it
Thanks for the link, Herbert.
|
Identifying next number in a range and referencing it
Hi Max! it seems Im coming back to your solution. I would now like to make
this list only when the first four numbers coincide with another cell. Would really appreciate your help. "Max" wrote: This simple, fast play might appeal to you Source data assumed in A2 down In B2: =IF(A2="","",ROW()) Leave B1 empty In C2: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1)))) Copy B2:C2 down to cover the max expected extent of data in col A, eg down to C1000? Minimize/hide away col B. Col C will return the results that you seek, all neatly packed at the top -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "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! |
Identifying next number in a range and referencing it
Let's say the 4 digit number is input in D2
(input in D2 is assumed to be a real number) Just amend the criteria formula in B2 to: =IF(A2="","",IF(LEFT(A2,4)+0=$D$2,ROW(),"")) Then copy B2 down (no change to the formulae in col C) and col C will return the desired results neatly packed at the top -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "Verlaesslichkeit" wrote: Hi Max! it seems Im coming back to your solution. I would now like to make this list only when the first four numbers coincide with another cell. Would really appreciate your help. |
Identifying next number in a range and referencing it
that would mean I would need 50 different columns (I have 50 different D2
codes) to put the A formula. Is there no way to amend the formula in column C so that it only makes a list of the numbers starting with criteria in D2? "Max" wrote: Let's say the 4 digit number is input in D2 (input in D2 is assumed to be a real number) Just amend the criteria formula in B2 to: =IF(A2="","",IF(LEFT(A2,4)+0=$D$2,ROW(),"")) Then copy B2 down (no change to the formulae in col C) and col C will return the desired results neatly packed at the top -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "Verlaesslichkeit" wrote: Hi Max! it seems Im coming back to your solution. I would now like to make this list only when the first four numbers coincide with another cell. Would really appreciate your help. |
Identifying next number in a range and referencing it
Try this then, in B2, copied down:
=IF(A2="","",IF(LEFT(A2,LEN($D$2))+0=$D$2,ROW(),"" )) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "Verlaesslichkeit" wrote: that would mean I would need 50 different columns (I have 50 different D2 codes) to put the A formula. Is there no way to amend the formula in column C so that it only makes a list of the numbers starting with criteria in D2? |
Identifying next number in a range and referencing it
Thanks a lot for your patience!
"Max" wrote: Try this then, in B2, copied down: =IF(A2="","",IF(LEFT(A2,LEN($D$2))+0=$D$2,ROW(),"" )) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "Verlaesslichkeit" wrote: that would mean I would need 50 different columns (I have 50 different D2 codes) to put the A formula. Is there no way to amend the formula in column C so that it only makes a list of the numbers starting with criteria in D2? |
Identifying next number in a range and referencing it
Welcome
-- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "Verlaesslichkeit" wrote in message ... Thanks a lot for your patience! |
| All times are GMT +1. The time now is 08:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com