• 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.

Combining cells help

nsantos

New Member
Hi everyone,

I am trying to group the cells in column F separated by a comma based on the criteria in column B. For example F2:F6 would be grouped F8:10 etc. Can someone show me how to do this?

Thanks in advance!
 

Attachments

  • Copy of C 2017 missing EEIs blanco.output.xlsx
    18.8 KB · Views: 7
Hi ,

What do you want to do after this grouping ?

If you want to sum up some value based on this grouping , you can use the SUMIF function.

Please explain your complete requirement.

Narayan
 
Hi yes this is what I wanted. Thank you for your help

If you don't mind can you explain how you approached this? I don't really understand the formulas you implemented

Thanks!
 
Hi ,

There are two columns , column H being used as a helper column , and column I being used for the final output.

In column H , the formula in H2 is =F2 , since this is the first cell. Thereafter , the formula in H3 downwards is :

=IF(B3 <> B2, F3, H2 & "," & F3)

What this says is that when ever there is a change in the EMBARQUE number (in column B) , copy the contents of the corresponding cell in column F (invoice number) ; otherwise , concatenate the current invoice number with the contents of the previous cell in column H.

What this ends up doing is to concatenate all the invoice numbers in column F for each EMBARQUE number ; thus against the EMBARQUE number A5S0064171 , in cell H6 , you will see the concatenated invoice numbers :

I-001830,IP-17001,I-AF0058DG,I-001832,I-001831

The formula in column I is merely copying this final value for each EMBARQUE number into that column ; everywhere else , there will be blanks.

For this , I have used the formula :

=IF(ISNUMBER(FIND(",", H3)), "", H2)

What this does is look for a comma in the next cell in column H ; if it finds it , it does nothing , otherwise , it outputs what ever value is present in column H.

This works because the first entry against each EMBARQUE number will always be the invoice number itself , which will not contain a comma. Commas will only be introduced from the second entry , since that invoice will be concatenated with the first invoice , and the two will be separated by a comma.

I could have used a far simpler formula :

=IF(B3 = B2, "", H2)

Enter this in I2 and copy downwards.

Narayan
 
Another option without helper, and use conditional formatting to mask the undesired results.

1] In H2, copied down :

=IF(B2=B1,H1&","&F2,F2)

2] Conditional formatting in H2:H200, formula rule and format font color in choose "White" :
upload_2017-9-20_17-5-57.png

Regards
Bosco
 

Attachments

  • C 2017 missing EEIs blanco.output(1).xlsx
    36.8 KB · Views: 7
Last edited:
Back
Top