Possible IF and CONCATENATE??

[sx200n]

Hello,

I am trying to come up with a simple (or difficult - although I am sure it isn't) formula that will allow me to compile a list of each individual endorsement number that applies to the same client. I have attached an example spreadsheet showing how I want it to look. I took a minute to put together, but when I will have several thousand to do at once, it would make my life easier if I can use a formula to do the work for me as part of a macro.

Basically I will have a whole list of Endorsements that apply on clients insurance policies. These can change in amounts and endorsement codes each time. Some client may just have the one endorsement on their policy, others may have 7!!

Can anyone think of a formula so that I can compile each individual endorsement code (for each individual client) and compile them in a single cell at the end?

Any help would be great. I am sure the answer is starring me in the face.

Thanks

[daffodil11]

Perhaps you could Mike Rickson's legendary ConcatIf User-Defined-Function?

Here is your workbook with the UDF added in.

Arguments:
=CONCATIF(criteria range, criteria,output range, delimiter, prevent duplicates as boolean)

For you, I used:
=ConcatIf($C$2:$C$22,J2,$E$2:$E$22,"|",TRUE)
This looks at C2:C22, compares it to J2, Outputs all concatenations of E2:E22 where matched, put | between entries, and prevents duplicates.

Here's Mike Rickson's sweet beautiful code:

Please Login or Register  to view this content.

To use this code in your workbook:

• Copy the code
• Go to Excel
• Hit Alt+F11 to enter VB land
• Click Insert
• Choose Module
• Paste Code
• Hit Alt+F11

[sktneer]

You may also try this macro to get the desired output in col. H.
Please find the attached sheet and click on the Orange button to run the macro to get the desired output.

Please Login or Register  to view this content.

[sx200n]

Thank you both. All sorted.

[sktneer]

You're welcome and thanks for the feedback as well.