How to find set of duplicates from a column?

[shekhor]

Hello Experts,

I am working with 4 columns where I am in need of a formula where it will check if the director or directors of one company is exactly exist with the same name along with the same number of directors with the company just next to the first one. Let me explain it with a few example. Please check the below table where for the both company "KINLOCH TEXTILES LIMITED" and "KINTEX MFG LIMITED" have one director each and its the same person as they consist the same name. But still I dont want it as "KINTEX MFG LIMITED" is just not right after the company "KINLOCH TEXTILES LIMITED". After "KINLOCH TEXTILES LIMITED" there are 3 more companies ( "KINS DEVELOPMENTS LIMITED", "KINS HOLDINGS LIMITED", "KINSMAN REEDS LIMITED") before "KINTEX MFG LIMITED".

But if you check the both companies "KINS DEVELOPMENTS LIMITED", "KINS HOLDINGS LIMITED" they consist the same number of directors and also all the directors are the same. More that that the company "KINS HOLDINGS LIMITED" is just after the company "KINS DEVELOPMENTS LIMITED". And this is what I am looking for.

So what I need is a separate column where it will say just beside those company as its duplicate directors. As I am looking for real time result it will be helpful if someone create formulas for it. But if anyone can't create formulas rather creating macros which will work real time by auto running along with the spreadsheet itself than it will also work. But the formula will save my huge time. So it will be great if you guys can help me out solving it with some formulas. Thank you in advance.

	A	B	C	D
Reg	Company	Director	Address
KING TOOL INTERNATIONAL LIMITED	DEREK JOHN O'NEILL	BICTON, SLEEPERS HILL, WINCHESTER, HANTS, SO22 4NB
KING TOOL INTERNATIONAL LIMITED	IAN KEITH PARGETER	19, LOWER SWANWICK ROAD, SOUTHAMPTON, SWANWICK, SO31 7HG
KING TOOL INTERNATIONAL LIMITED	MARINA LOUISE THOMAS	120, GARLAND ROAD, POOLE, DORSET, BH15 2LD
KINIKI LIMITED	JOHN WILLIAM WALKER	20, PARK DRIVE, STOKE ON TRENT, TRENTHAM, ST4 8AB
SC032574	KINLOCHBERVIE FISHSELLING COMPANY LIMITED	IAN CLARK WOOD	42, RUBISLAW DEN SOUTH, ABERDEEN, AB15 4BB
SC032574	KINLOCHBERVIE FISHSELLING COMPANY LIMITED	GRAHAM GOOD	218, QUEENS ROAD, ABERDEEN, AB15 8DJ
SC032574	KINLOCHBERVIE FISHSELLING COMPANY LIMITED	WILLIAM MCKENZIE	10, MARKET TERRACE, FRASERBURGH, STRICHEN, ABERDEENSHIRE, AB43 6TS
KINLOCH TEXTILES LIMITED	THE LAWN 16, CHAPEL HILL HOPTON, MIRFIELD, WEST YORKSHIRE, WF14 8EW
KINS DEVELOPMENTS LIMITED	75, MINSTER ROAD, GODALMING, BUSBRIDGE, SURREY, GU7 1SR
KINS DEVELOPMENTS LIMITED	DIOCESAN CHURCH HOUSE, NORTH HINKSEY LANE, OXFORD, BOTLEY, OX2 0NB
KINS DEVELOPMENTS LIMITED	WOODCOTE GROVE, ASHLEY ROAD, EPSOM, SURREY, KT18 5BW
KINS HOLDINGS LIMITED	75, MINSTER ROAD, GODALMING, BUSBRIDGE, SURREY, GU7 1SR
KINS HOLDINGS LIMITED	DIOCESAN CHURCH HOUSE, NORTH HINKSEY LANE, OXFORD, BOTLEY, OX2 0NB
KINS HOLDINGS LIMITED	WOODCOTE GROVE, ASHLEY ROAD, EPSOM, SURREY, KT18 5BW
KINSMAN REEDS LIMITED	HENRY KENNEDY FAURE WALKER	SANDON BURY, BUNTINGFORD, SANDON, HERTS, SG9 0QY
KINSMAN REEDS LIMITED	PAUL ANTHONY HUNTER	58, CHURCH STREET, WEYBRIDGE, SURREY, KT13 8DP
KINTEX MFG LIMITED	THE LAWN 16, CHAPEL HILL HOPTON, MIRFIELD, WEST YORKSHIRE, WF14 8EW
KINVEL DEVELOPMENTS LIMITED	BALWANT SINGH RAI	84, BLACKWATCH ROAD, COVENTRY, CV6 3GW
KINVEL DEVELOPMENTS LIMITED	MONAHAR SINGH RAI	COLINTON HOUSE, LEICESTER ROAD, BEDWORTH, WARWICKSHIRE, CV12 8AB
KINVEL DEVELOPMENTS LIMITED	MOHAN SINGH	64, KENILWORTH ROAD, COVENTRY, CV4 7AH
KINVEL DEVELOPMENTS LIMITED	KULDIP AOJULA	OLDBURY GRANGE, OLDBURY ROAD, NUNEATON, WARWICKSHIRE, CV10 0TJ
KIRBY'S OF HACKNEY LIMITED	MICHAEL ROBERT GOLDBERGER	26, ASHLEY LANE, LONDON NW4, HENDON, NW4 1HG
KIRBY'S OF HACKNEY LIMITED	DAVID ALAN PEARLMAN	31, LOWER BROOK STREET, IPSWICH, IP4 1AQ

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01118702[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01118702[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01118702[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]02739873[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]01370299[/TD]

[TD="bgcolor: #FFFF00"]JAMES PHILIP SUTCLIFFE WALKER[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]00688414[/TD]

[TD="bgcolor: #FFFF00"]RICHARD WEBSTER[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]00688414[/TD]

[TD="bgcolor: #FFFF00"]HEATH STEWART DREWETT[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]00688414[/TD]

[TD="bgcolor: #FFFF00"]ALAN JAMES CULLENS[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]00688415[/TD]

[TD="bgcolor: #FFFF00"]RICHARD WEBSTER[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]00688415[/TD]

[TD="bgcolor: #FFFF00"]HEATH STEWART DREWETT[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]00688415[/TD]

[TD="bgcolor: #FFFF00"]ALAN JAMES CULLENS[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]02240075[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]02240075[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]00536498[/TD]

[TD="bgcolor: #FFFF00"]JAMES PHILIP SUTCLIFFE WALKER[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]01558087[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]01558087[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]01558087[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]01558087[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]00564454[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]00564454[/TD]

</tbody>

 

[Joe4]

Duplicate: http://www.mrexcel.com/forum/excel-...l-finding-duplicates-range-values-column.html

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).