I have an excel column with about 100 rows, each row with an Excel formula that returns text under certain conditions. Note that, the text is different from one row to another. If conditions are not met, certain rows remain blank (so, without text).
Now, I would like in another excel cell to identify lines (rows) that contain text and join them through a comma. How do I do that?
Attaching a sample workbook enables others to work on your problem:
To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.
Click on GO ADVANCED and click "manage attachments" to open the upload window.
To add a file to a post
I've attached a sample file. The column I referenced in the first post is the column called "return".
So, I want in the G2 cell to be a formula that concatenate, by comma, randomly generated text from the return column. Sorry for my english.
I like using Concatenate, then you can work in each individual box, could use some work.
Formula:
Please Login or Register to view this content.
@davesexcel, you did not understand me. I marked the yellow cell G2, not C2.
So, in the G2 cell I would like the words generated in the "return" column to be displayed as follows: CAT II-Name 3 3, CAT V-Name 6 5, CAT IV-Name 10 5 ...
If I use concatenate in the G2 cell, the problem is I don't know how to jump over empty cells in the "return" column, because I will have to consider each row, even if it contains text, even if it does not contain.
Well here is a code you can use:
Please Login or Register to view this content.
Here a solution with a helper column
In cell F2 use the following formula and copy down (hide this column)
Formula:Please Login or Register to view this content.
In G2 use
Formula:Please Login or Register to view this content.
See the file
Thank you very much. Now I have one step. In this sense, I made a small update to the file posted by @ José Augusto (ee the attachment below).
In this file we transferred the final calculation to column H and in the G column we added the five categories: CAT I, CAT II, CAT III, CAT IV, CAT V.
I would like the result in column H to be distributed by category. Yours can help me in this regard! I have tried a SUMPRODUCT separately but it does not work.
No one, please!
Sort you data according your category to do so select A2:F101 then press Alt->A->S->S select short by category then ok.
now in H2Formula:Please Login or Register to view this content.
Copy down.!
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Thanks for the answer but unfortunately I can not use the sort date option every time.
In addition, your formula works well only in the H2 cell, in H3 ... H6 does not work well.
So, I would need a solution without using the manual sorting of the data.
Can you mention your expected result in sample file ?
At this point in cell H2 is returned: CAT I-Name 16 5, CAT I-Name 57 3; what is correct because we have CAT I in the G2 cell.
Further, in H3 cell should only show lines from column E starting with CAT II, because CAT II appears in G3 and so on... in H4 cell = CAT III, because G4 = CAT III... How can I make H3 ... H6 cells work?
Last edited by public; 12-08-2017 at 06:22 AM.
Hi
Sorry for late replay
I use a little better approach.
In a new sheet (draft in my example) use in A3, B3, C3 the following formulas:
The first formula gives me the line numbers where there is text in the name column!EPlease Login or Register to view this content.
The second formula allows me to get that text
The third formula uses the column header at C $ 1 to separate the text into columns. This formula can be copied to the other columns with headers.
Note: In name!J2 I provide a array formula, for compatibility with prior version of Excel2010, that can replace my first formula.
Formula:Please Login or Register to view this content.
See the file for clarification
Last edited by José Augusto; 12-08-2017 at 07:18 PM.
Thanks for the reply José Augusto but in your example there appears a countifs what excel 2003 does not support. In addition, I tried to implement your solution, that is to adapt it to my example and it is not working (maybe I did not understand something good, maybe!).
For this reason, I decided to use a VBA code that searches for two values on two columns and return the corresponding result for the third column (see the example in the file attached below).
I have tried to implement the VBA below in the attached excel file does not work on two search criteria !!! Please, some help.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks