| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Identifying next number in a range and referencing it
|
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Identifying next number in a range and referencing it
Thanks for the link, Herbert.
|
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Identifying a Selected Range in a Macro | Excel Discussion (Misc queries) | |||
| Searching for mulitple strings and assigning identifying number | Excel Worksheet Functions | |||
| vba, identifying a range | Excel Worksheet Functions | |||
| Identifying Overlapping Dates within a range | Excel Worksheet Functions | |||
| Identifying single column within named range | Excel Discussion (Misc queries) | |||
Linear Mode
