LinkBack URL
About LinkBacks
Hello Friends
From 2 to 20 according to the input serial numbers with bracket and space should be generated by formula.
Thanks in advance
Hello Friends
From 2 to 20 according to the input serial numbers with bracket and space should be generated by formula.
Thanks in advance
Sekar
Why must you have the results in one cell?
John, I could not able to understand this question `Why must you have the results in one cell?'.
This is because in 1 cell requirement will vary form 1 to 20. So by using input cell and by formula need to populate the required serial numbers.
Why can you not have serial numbers on 5 or 13 consecutive rows?
I always find it difficult to understand this need to "compress" the output to a single cell when you have 1 Million + rows!
This is because for the specific parameters sometimes we will get the 3 inputs sometimes it may be 7 it will very time to time. So if we can able to customize by using input number it will be fine.
Refer attached excel cell E2 in sheet 1.
If this is difficult / not possible then per input (2 to 20) is it possible to pull the required output by using the Cell E2 texts.
I have found, If the Cell G2 is input (2 to 20) then I2 = <=IFERROR(IF(G2=1,"",LEFT(E2,(FIND(G2,E2,1))+4)),"")> working fine, but is there any way to avoid the cell E2 contents.
This version uses rows rather than concatenation into one cell
See sheet2:
In D1
=IF($A$1=1,"",IF(ROWS($1:1)<=$A$1,ROWS($1:1)&")_____",""))
Copy down
D7 and D9 similar
This works fine for 2 to 20 range in I2 - <=IFERROR(IF(OR(G4<2,G4>20),"",LEFT(E4,(FIND(G4,E4,1))+4)),"")>
Much neater to use a small UDF:
You can then simply use as a formula:
Please Login or Register to view this content.
Formula:
Please Login or Register to view this content.
If you insist on a formula solution, in one cell, then this horrific creation does it:
Formula:Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Delete post...
Last edited by Phuocam; 04-20-2018 at 07:57 AM.
Hello John
The Sl. numbers required in single cell instead of multiple cells and concatenate
Hello Olly
This is exactly what i want since your formula is omits the helpher cell text of E2. Also i will use your formula solution instead of .xlsm udf.
Thanks to both of you.
Olly's formula is concatenation, using "&" to perform the concatenation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks