Excel finding Duplicates From a Range of values of a column

[shekhor]

Hello Experts,

I am working with 4 columns where I am need a formula where it will check if the one director or directors of one company is exactly with the same name and same number of the company just next to the first one. For 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. 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".

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 than fix it by creating macros than it will also work as with that I can check the whole sheet by running the macro once after completing my task on the spreadsheet. But the formula will save my huge time. So it will be great if you guys can help me out with formulas. Thank you in advance.

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

<br /><br />

 

[DILIPandey]

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.

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.

both are contradictory statement.

Not sure what you need ? Can you mention your expected results ?



Regards,
DILIPandey