Formula to TEXTJOIN if column contains the same values

Hi Everyone

I really need help on a formula please.

In the attached sheet, columns A-C show data we receive about train services failing to stop at certain stations.

The list represents these, where:

• If Column B is > 0, then a train has failed to stop at the station named in Column C.
• Each station where a service has failed to stop is recorded on separate rows.

If the value in Column B=0 for any of these stations, these are not stations where the train has failed to stop, so can be ignored.

As an example, service MAN1 failed to stop at DEIGHTON, MIRFIELD, MORLEY, BATLEY, DEWSBURY, COTINGLEY, RAVENTHPE, and CLE4 failed to stop at MANCROXRD, MANCR VIC. As the listed stations for SHF6 have a value of 0 in Column B, these can be ignored.

Basically, I need two formulas that:

1) List each train service individually
2) TEXTJOIN all the stations that that train service has failed to stop at, crucially providing that the value in Column B for that station is >0.

The desired output is shown in Columns F-G.

Any help greatly appreciated (but please, no macros).

Thanks

Originally Posted by bsalv

with textjoin

Formula:

Please Login or Register  to view this content.

too quick, add $-characters

Formula:

Please Login or Register  to view this content.

@bsalv

Thank you so much, that worked like a charm!

Cell F4 formula

Formula:

Please Login or Register  to view this content.

Cell G4 formula , drag down

Formula:

Please Login or Register  to view this content.