capturing repeat instances

[HWL]

Okay, say I have 2 columns of data. In column A I have a string on each row. Some strings may be duplicates. In column B I have a string on each row. No duplicates.

Here is what I want to do. In column D, I want to list on the same row as the first instance of a string in A, all of the associated strings from column B. Otherwise for all other instances from A, I want D to be blank. Need to be a formula I can drag down in col D.

EXAMPLE:
COL A______COL B________COL D
Bob________Wed_________Wed,Tue,Sat
Sam________Fri__________Fri,Sun
Tom________Mon_________Mon
Bob________Tue__________
Bob________Sat__________
Sam_______Sun__________

Thanks for the help

 

[Andrew Poulsom]

That's not going to be easy with built-in Excel functions, because Excel is week at concatenation. Would you be prepared to use a VBA solution?

 

[HWL]

That's not going to be easy with built-in Excel functions, because Excel is week at concatenation. Would you be prepared to use a VBA solution?

My enduser can't use macros. I have something like:

=IF(A5="","",IF(AND(TRIM(A5)<>"",A6=""),B5&","&B6&IF(AND(A6="",A7=""),","&B7,"")&IF(AND(A7="",A8=""),","&B8,"")&IF(AND(A8="",A9=""),","&B9,""),B5))

There is a bit a variation on what I orginally posted, as col A is a value-pasted pivot table so instead of duplicate entries, there is a blank in A when there are more than one associated entries in col B.

Obviously this solution will only work upto the 7 nested IF statements.

 

[HWL]

This is working with an xlsx file and upto 15 lvls --
=IF(A5="","",IF(AND(TRIM(A5)<>"",A6=""),B5&","&B6&IF(AND(A6="",A7=""),","&B7,"")&IF(AND(A6="", A7="",A8=""),","&B8,"")&IF(AND(A6="",A7="",A8="",A9=""),","&B9,"")&IF(AND(A6="",A7="",A8="",A9="",A10=""),","&B10,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11=""),","&B11,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11="",A12=""),","&B12,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11="",A12="",A13=""),","&B13,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11="",A12="",A13="",A14=""),","&B14,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11="",A12="",A13="",A14="",A15=""),","&B15,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11="",A12="",A13="",A14="",A15="",A16=""),","&B16,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11="",A12="",A13="",A14="",A15="",A16="",A17=""),","&B17,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11="",A12="",A13="",A14="",A15="",A16="",A17="",A18=""),","&B18,"")&IF(AND(A6="",A7="",A8="",A9="",A10="",A11="",A12="",A13="",A14="",A15="",A16="",A17="",A18="",A19=""),","&B19,""),B5))

 

[Andrew Poulsom]

Ugly isn't it? As I said Excel is poor at concatenation, so you are unlikely to find a more elegant solution.

 

[HWL]

Ugly isn't it? As I said Excel is poor at concatenation, so you are unlikely to find a more elegant solution.

Yeah, I wish someone like the LibreOffice team would not merely try to make a copy of Excel, but work to make these kind of improvements.

Thanks.

P.S. From what I understand, I can go out 64 lvls with this formula in xlsx -- so as ugly as it is, it should cover what I'm trying to do.