Excel concatenate unknown number of cell values without VBA -

-

I need to send an excel file to the third party to capture the data and I should not use VBA.

The user must capture at least one string and by default, instead of capturing up to 10 stars, say in range D23: D32. Line 33 is an empty row, else data must be captured from line 34. I can define a name for D33 or D34.

If the user needs to capture more than 10 strings, then he can insert additional lines.

Now I have to add the user's strings, separated them by a semicolon. To clarify what I mean if the user was not able to insert the rows, then this formula would: 

  = D23 & amp; If (D24 = "", "", ";" and D24) & amp; If (D25 = "", "", ";" and D25) & amp; IF (D26 = "", "", "," & D26) & amp; IF (D27 = "", "", "," & D27) & amp; IF (D28 = "", "", ";" & D28) & amp; IF (D29 = "", "", "," & D29) & amp; IF (D30 = "", "", "," & D30) & amp; IF (D31 = "" "," ","; ", and D31) and IF (D32 =" "," ","; "and D32)

Is there a way to add a user can be added?

A simple VBA user defined function is very easy and

If you can ensure that your users will only insert rows on the list below , then you can use the table and the hidden column is D22: Data E22: End E23: = D23 E24: = CONCATENATE (E23, ";", D24)

E24 below the table

Show the formula results (D19 in the screen shot below): 

  = lookup (2,1 / (right (table 1 [inclusion] , 1) <> ">"), Table 1 [Insertion])

Because it is a table, the formula will increase when the rows are inserted and additional entries will be created. , The method would require discipline in the part of the user because the protection Chana can be easily messed up.

Here it is visible, with the column E no , and along with formulas appearing in adjacent cells:

On the other hand, a simple user-defined function will either empty all the data from D23 first Can be prepared to return to the line; Or in some other fashion, if the range is within the space. Empty within the range, I mean there will be entries in range D23: D32 is empty, with valid data below. for example. D2: Valid data in D23, D24, and D30 with nothing in D29

The UDF can look like this: 

  function concat line (in the form of RG Range, String = "" as the alternate delimiter, in the RG Conset Lines for each C in the form of String Dims, range = concat lance & amp; Delimiter & amp; C next concatLines = middle (concatLines, 2) termination function

and the formula to display the result, from the D23 to the first empty row below, array-entry will be done : 

  = concat line (offset ($ D $ 23,0,0, match (TRUE, ISBLANK ($ D $ 23: $ D $ 100), 0) - 1), ";);

Hold array-enter , ctrl-shift for a form when Enter . If you have done this correctly, suspend {...} around the Excel formula Received.


Comments

Post a Comment

Popular posts from this blog

java - org.apache.http.ProtocolException: Target host is not specified -

-
I have written a simple httprequest / response code and I am getting this error below: I used to type in my classpath httpclient, httpcore, common -Codecs and general logging. I am very new to Java and I do not know what's going on here. Please help me. code: import org.apache.http.client.HttpClient; Import org.apache.http.client.methods.HttpGet; Import org.apache.http.HttpResponse; Import org.apache.http.impl.client.HttpClientBuilder; Import org.apache.http.Header; Import org.apache.http.HttpHeaders; Public class UnshorteningUrl {public static zero main (string [] args throws exception {HttpGet request = zero; HTTP Client Client = HTTP ClientBuilder.credit (). Build (); Try {Request = New HTTP Gate ("trib.me/1lBFzSi"); HttpResponse httpResponse = client.execute (request); Header [] header = httpResponse.getHeaders (HttpHeaders.LOCATION); // Preconditions.checkState (headers.length == 1); String new url = header [0] .getValue (); System.out.println ("new url...
Read more

java - Gradle dependencies: compile project by relative path -

-
Is it possible to specify a dependency on another gradeal project in the granular (in android studios) outside the current project limits? For example, with a relative path like this: dependence {collection project ('../../ stdlib / dagger'}} MyApp (path / user / foo / workspace / mypad) The app (Path / User / Foo / Workspace / MyPass / App) And I have an Android Library Project with 3 subdomules: stdlib (path is / user / foo / Utilities (Path / User / Foo / Workspace / Stadlib / Utilis) http (Path / User / Foo / Workspace / Stadlib / HT) / Li> What I have to do is compile the Dagger, Util, http module in the MyApp project. The Stidelib Library module under heavy development And MyApp will grow in the form of Growth, so I do not want to push them into a Mewen repository. I am a little change. is likely to add another Gredle projects in some way? I will push the study library into the Maven repository after the source is in search of a temp...
Read more

ruby on rails - Object doesn't support #inspect when used with .include -

-
So I have participated in a glitch error that I do not even know how to debug. I am very new to the railing, so it has kicked my ass well when I use the .includes (school) seen by the student, it gives a blue box error, But when I look at any other model and .includes (school) , the school works just fine. class student & lt; ActiveRecord :: Base has has_many: relationships, foreign_key: "liker_id", dependent :: has_many: matches, foreign_key: "student_1_id", dependent :: deleted belong_to: school belong_to: conference belong_to: would_you_rather belong_to: home city belong_to: year is_to: Major and class school & lt; on the console activatecord :: Affiliate for the base: conference includes: respondent, class: "school", foreign_key: "rival_id" (e.g. For: School) Select SQL generated Select "School". * "School" from "school" "ID" IN (1) error message back NoMethodError: u...
Read more