vmohan1978
Member
In a sheet range a1:x25 , i have a set of 30nos staggered in the range and some of the cells are empty. The result i want from A30 in column wise all the numbers in a range.
Group to individual
- Thread starter vmohan1978
- Start date
vmohan1978
Member
Dear Balli,
Even if 2 or more numbers are in a row, i need all the numbers in a column only .
Even if 2 or more numbers are in a row, i need all the numbers in a column only .
vmohan1978
Member
hi old chippy ,
It worked very well , thank you very much
what about if its text or like below in a range.
0.60x1.00
0.3X3.15
0.55x1.00
0.60x1.00
0.70x0.70
0.75x0.85
0.75x1.00
0.75x1.10
0.75x2.15
0.75x2.25
0.75x2.65
0.875x0.55
0.90x1.10
1.00x0.55
1.00x0.650
1.00x0.75
It worked very well , thank you very much
what about if its text or like below in a range.
0.60x1.00
0.3X3.15
0.55x1.00
0.60x1.00
0.70x0.70
0.75x0.85
0.75x1.00
0.75x1.10
0.75x2.15
0.75x2.25
0.75x2.65
0.875x0.55
0.90x1.10
1.00x0.55
1.00x0.650
1.00x0.75
oldchippy
Active Member
Now Mohan, you've just made it harder! I'm struggling with that one. I can list the first part of the number 0.60x1.00 by modifying the last formula and creating an array formula
=SMALL(IFERROR(LEFT($A$1:$L$23,FIND("x",$A$1:$L$23)-1)+0,""),ROW(1:1)) Ctrl+Shift+Enter
But the problem is then getting the other part of the text to the correct number???
Hopefully someone will come up with a macro for you to extract each text and put them in order.
Good luck
=SMALL(IFERROR(LEFT($A$1:$L$23,FIND("x",$A$1:$L$23)-1)+0,""),ROW(1:1)) Ctrl+Shift+Enter
But the problem is then getting the other part of the text to the correct number???
Hopefully someone will come up with a macro for you to extract each text and put them in order.
Good luck
vmohan1978
Member
Dear Chippy ,
I think there is a misunderstanding in the result i required.
sorry , i think i didn't mention proper .
I require the whole number like 0.60x1.00 not 0.60 and 1.00 separately.
Please find attached the file , where i mentioned the numbers in Bold .
Thank you very much for your support and time.
I think there is a misunderstanding in the result i required.
sorry , i think i didn't mention proper .
I require the whole number like 0.60x1.00 not 0.60 and 1.00 separately.
Please find attached the file , where i mentioned the numbers in Bold .
Thank you very much for your support and time.
SirJB7
Excel Rōnin
Hi, vmohan1978!
As oldchippy wrote your earlier:
Regards!
As oldchippy wrote your earlier:
your little change from numbers to strings changed absolutely the way to face the problem:
turning unapplicable the function SMALL, which works only for numbers.
Regards!
NARAYANK991
Excel Ninja
Hi oldchippy / Mohan ,
Sajan had given this gem sometime back :
=INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(Data<>"",ROW(Data)+COLUMN(Data)%%),ROW(A1)),"00000.0000"),".","C"),FALSE)
entered as an array formula , using CTRL SHIFT ENTER.
See your file.
Narayan
Sajan had given this gem sometime back :
=INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(Data<>"",ROW(Data)+COLUMN(Data)%%),ROW(A1)),"00000.0000"),".","C"),FALSE)
entered as an array formula , using CTRL SHIFT ENTER.
See your file.
Narayan
Last edited:
vmohan1978
Member
Dear Narayank ,
Really it is Gem
. it worked perfectly . Thank you very much for your help.
It makes my 2-3 hours job into 10min work.
Really it is Gem
. it worked perfectly . Thank you very much for your help.It makes my 2-3 hours job into 10min work.
oldchippy
Active Member
@SirJB7
I knew I was barking up the wrong tree with my second attempt
@NARAYANK991 @Sajan
That's brilliant, but could one of you experts explain how that works, only I haven't a clue.
I knew I was barking up the wrong tree with my second attempt
@NARAYANK991 @Sajan
That's brilliant, but could one of you experts explain how that works, only I haven't a clue.
Somendra Misra
Excel Ninja
Hi @oldchippy
Absolutely Excellent formula by @Sajan & shared by @NARAYANK991 Sir. Thanks to Both.
Although I am not so good in explaining formulas. But here how this work.
It is basically a lookup function through INDIRECT function. It Uses the Indirect function to pass refrence where there is data in R1C1 style.
=INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(Data<>"",ROW(Data)+COLUMN(Data)%%),ROW(A1)),"00000.0000"),".","C"),FALSE)
Here FALSE is telling INDIRECT that the refrence in IN R1C1 style. So "R" is use for that now we need ROW number & coulmn Number where there is data. So SMALL function is returning ROW Number+Column NUMBER where there is data. Like 3.0001. Ther four decimal palce is acived via %%. So in row 3 & column 1 is data basically. Now this is a number which is converted to text by TEXT function outside SMALL function. Now it is like "00003.0001" because of format paseed in TEXT function 00000.0000. Now we know that 3 row & 1 column is data. So substitute function is Substituing "." with C so we get "00003C0001" which is concatenated with R. So basically you are passing a refrence as string to INDIERECT as "R00003C0001". Now INDIRECT will discard 0's and treat it as R3C1, since you already told INDIRECT that style is R1C1 via FALSE. It will return the data.
NOW WHEN you copy the data down the k of SMALL function will become 2. So it will give you second combination of row & column where there is data and rest all repeats.
I think 00000.0000 format is used to take atleast 99999 rows and 9999 columns of data.
Hope this make sense.
Regards,
Absolutely Excellent formula by @Sajan & shared by @NARAYANK991 Sir. Thanks to Both.
Although I am not so good in explaining formulas. But here how this work.
It is basically a lookup function through INDIRECT function. It Uses the Indirect function to pass refrence where there is data in R1C1 style.
=INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(Data<>"",ROW(Data)+COLUMN(Data)%%),ROW(A1)),"00000.0000"),".","C"),FALSE)
Here FALSE is telling INDIRECT that the refrence in IN R1C1 style. So "R" is use for that now we need ROW number & coulmn Number where there is data. So SMALL function is returning ROW Number+Column NUMBER where there is data. Like 3.0001. Ther four decimal palce is acived via %%. So in row 3 & column 1 is data basically. Now this is a number which is converted to text by TEXT function outside SMALL function. Now it is like "00003.0001" because of format paseed in TEXT function 00000.0000. Now we know that 3 row & 1 column is data. So substitute function is Substituing "." with C so we get "00003C0001" which is concatenated with R. So basically you are passing a refrence as string to INDIERECT as "R00003C0001". Now INDIRECT will discard 0's and treat it as R3C1, since you already told INDIRECT that style is R1C1 via FALSE. It will return the data.
NOW WHEN you copy the data down the k of SMALL function will become 2. So it will give you second combination of row & column where there is data and rest all repeats.
I think 00000.0000 format is used to take atleast 99999 rows and 9999 columns of data.
Hope this make sense.
Regards,
oldchippy
Active Member
Hi @Somendra Misra
Thanks for the explanation, I've read through it a few times now and the grey matter is slowly beginning to work. I've kept a copy of the spreadsheet for future reference because if this question comes up in the future I'll need it!
Thanks for the explanation, I've read through it a few times now and the grey matter is slowly beginning to work. I've kept a copy of the spreadsheet for future reference because if this question comes up in the future I'll need it!