I have two columns A1 (Section X) and B1 (Section Y) <o
></o>Each column has 20 rows of data (A2-B21) such as: <o
></o>Section X | Section Y |
1<o ></o> | 33<o ></o> |
2<o ></o> | 34<o ></o> |
3<o ></o> | 35<o ></o> |
4<o ></o> | 36<o ></o> |
5<o ></o> | 37<o ></o> |
3<o ></o> | 35<o ></o> |
7<o ></o> | 39<o ></o> |
8<o ></o> | 40<o ></o> |
9<o ></o> | 41<o ></o> |
10<o ></o> | 42<o ></o> |
11<o ></o> | 43<o ></o> |
12<o ></o> | 44<o ></o> |
10<o ></o> | 42<o ></o> |
14<o ></o> | 46<o ></o> |
10<o ></o> | 42<o ></o> |
16<o ></o> | 48<o ></o> |
17<o ></o> | 49<o ></o> |
18<o ></o> | 50<o ></o> |
19<o ></o> | 51<o ></o> |
20<o ></o> | 52<o ></o> |
<tbody>
</tbody>
> </o>What I need is either dynamic formula or macros that can do thefollowing:
1) Assess range A2-B21; <o
></o><o
> </o>2) Identify sets of duplicates based on the identicalvalues in both columns - for example in this case there are two sets of duplicates:<o
></o><o
> </o>-Set 1: A4 (4)/B4(36) isidentical with A7/(4)B7(36); and <o
></o><o
> </o>-Set 2: A11(10)/B11(42) isidentical with: A14(10/B14(42) is identical with A16(10)|B16 (42)
<o
> </o>3) Highlight cells that contain sets of duplicates withdifferent colours – for example:<o
></o><o
> </o>Cell colour for Set1 (A4 /B4 & A7/B7) should be different to cell colour for Set 2 (A11/B11, A14/B14 & A16|B16). <o
></o><o
> </o>This colouring should be dynamic – data in this table is updated on adaily basis so as the number of duplicates will vary all the time. <o
></o><o
> </o>Thanks in advance <o
></o><o
> </o><o
></o>
