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.
<tbody>
</tbody>
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 | |
|---|---|---|---|---|
| 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 | IAN CLARK WOOD | 42, RUBISLAW DEN SOUTH, ABERDEEN, AB15 4BB |
| 7 | SC032574 | KINLOCHBERVIE FISHSELLING COMPANY LIMITED | GRAHAM GOOD | 218, QUEENS ROAD, ABERDEEN, AB15 8DJ |
| 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 | RICHARD WEBSTER | 75, MINSTER ROAD, GODALMING, BUSBRIDGE, SURREY, GU7 1SR |
| 11 | 00688414 | KINS DEVELOPMENTS LIMITED | HEATH STEWART DREWETT | DIOCESAN CHURCH HOUSE, NORTH HINKSEY LANE, OXFORD, BOTLEY, OX2 0NB |
| 12 | 00688414 | KINS DEVELOPMENTS LIMITED | ALAN JAMES CULLENS | WOODCOTE GROVE, ASHLEY ROAD, EPSOM, SURREY, KT18 5BW |
| 13 | 00688415 | KINS HOLDINGS LIMITED | RICHARD WEBSTER | 75, MINSTER ROAD, GODALMING, BUSBRIDGE, SURREY, GU7 1SR |
| 14 | 00688415 | KINS HOLDINGS LIMITED | HEATH STEWART DREWETT | DIOCESAN CHURCH HOUSE, NORTH HINKSEY LANE, OXFORD, BOTLEY, OX2 0NB |
| 15 | 00688415 | KINS HOLDINGS LIMITED | ALAN JAMES CULLENS | WOODCOTE GROVE, ASHLEY ROAD, EPSOM, SURREY, KT18 5BW |
| 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 |
<tbody>
</tbody>
