• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenating subsequent cells in a column when adjacent cell is empty

johnmheinzel

New Member
I have data that was extracted, with a 'File' identifier in column A, and related 'Code' in column B. For some of the records the Code is on multiple rows, with only the first row showing the File. I'd like to have one row for each File with the code concatenated, if necessary.
For example, I'd like to turn this:
FileCode
PRODUCTTRANS('PROD.BR',@ID,40,"X");
INDEX(@1,"ALL",1);
DCOUNT(@1[1,@2],@SVM);
TRANS('PROD.BR',@ID,25,"X")<1,1,@3>
into this:
FileCode
PRODUCTTRANS('PROD.BR',@ID,40,"X");INDEX(@1,"ALL",1);DCOUNT(@1[1,@2],@SVM);TRANS('PROD.BR',@ID,25,"X")<1,1,@3>
Given that I don't know how many rows of code need to be concatenated, but I do know I need to start with the row that has something in the File column, then concatenate the following rows if the File column is null, so it seems like a formula in a third column could do this, then I could convert the results to values and sort by the File column to get all my data together and delete the rest. A sample file is attached with the Raw Data column and what the cleaned data should look like.
My first attempt seems too messy. Any suggestion as to a formula that would work to concatenate correctly?
 

Attachments

  • Concatenate to get rid of blanks.xlsx
    11.8 KB · Views: 7
Last edited:
Try,

1] In D2, formula copied down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$18)/(A$2:A$18<>""),ROW(A1))),"")

2] In E2, array (CSE) formula copied down :

=TEXTJOIN(CHAR(10),,IF(LOOKUP(ROW($2:$18),ROW($2:$18)/(A$2:A$18>0),A$2:A$18)=D2,B$2:B$18,""))

76571
 
Last edited:
  • Like
Reactions: Hui
Power Query can do this staightforwardly.
Right-click the green table at cell A12 in the Cleaned Data sheet of the attached and choose Refresh (after updating the Raw data sheet's table).
 

Attachments

  • Chandoo47004Concatenate to get rid of blanks.xlsx
    19.4 KB · Views: 2
Very interesting - I'll play with Power Query using your example and see if I can duplicate the results on my full set of data. Thanks!
 
Try,

1] In D2, formula copied down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$18)/(A$2:A$18<>""),ROW(A1))),"")

2] In E2, array (CSE) formula copied down :

=TEXTJOIN(CHAR(10),,IF(LOOKUP(ROW($2:$18),ROW($2:$18)/(A$2:A$18>0),A$2:A$18)=D2,B$2:B$18,""))

View attachment 76571
Wow, that is amazing. Now I have to look up the TEXTJOIN and LOOKUP functions so I can understand what you did here.
 
p45Cal, In Power Query I did not find the Text.Combine function when using the Group By dialog, so I chose Sum and just edited the formula to be Text.Combine instead. When you did this did you do Group By then edit the resulting formula to be Text.Combine, use some other menu, or do you just know what to type in the formula bar?
 
Back
Top