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