| Multiple aggregations for Customers (example) 3.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Sorted by Name, Phone | IF(B4=B3,E3,IF(VLOOKUP(C4,$C$15:$E$23,3,FALSE)=0,max($E$3:$E$11)+1,VLOOKUP(C4,$C$15:$E$23,3,FALSE))) | ||||||||
| 2 | Name | Name Agg | Phone Agg | Desired Agg (related by Name and Phone) | agg try | Phone | Method | |||
| 3 | abc co | 1 | 3 | 1 | 1 | 4444444444 | 1. Under Tools>Options>Calculation, select Maximum iterations = 2 | |||
| 4 | abc company | 2 | 2 | 1 | 1 | 3333333333 | 2. Sort first by Name agg then Phone agg | |||
| 5 | abc company | 2 | 3 | 1 | 1 | 4444444444 | 3. Create New agg field; populate first entry as 1 | |||
| 6 | def company | 3 | 1 | 2 | 3 | 2222222222 | 4. Copy list beneath old one or to new worksheet | |||
| 7 | fgi inc (same as abc) | 4 | 3 | 1 | 1 | 4444444444 | 5. In new list set formula for New agg to link to the same reference in the old list (may need Sheet ref) | |||
| 8 | fgi inc 2 | 5 | 3 | 1 | 1 | 4444444444 | 6. Sort new list by Phone agg then Name agg (make sure New agg sorts correctly) | |||
| 9 | fgi inc 2 | 5 | 4 | 1 | 1 | 5555555555 | 7. Copy down given formula to remaining cells | |||
| 10 | fgi inc 3 | 6 | 3 | 1 | 1 | 4444444444 | 8. Repeat as often as needed for aggregations | |||
| 11 | vv ltd | 7 | 5 | 3 | 4 | 9999999999 | ||||
| 12 | * Note: Order of the above steps is important! | |||||||||
| 13 | Sorted by Phone, Name | |||||||||
| 14 | Name | Name Agg | Phone Agg | Desired Agg (related by Name and Phone) | agg try | Phone | ||||
| 15 | def company | 3 | 1 | 2 | 3 | 2222222222 | ||||
| 16 | abc company | 2 | 2 | 1 | 1 | 3333333333 | ||||
| 17 | abc co | 1 | 3 | 1 | 1 | 4444444444 | ||||
| 18 | abc company | 2 | 3 | 1 | 1 | 4444444444 | ||||
| 19 | fgi inc (same as abc) | 4 | 3 | 1 | 1 | 4444444444 | ||||
| 20 | fgi inc 2 | 5 | 3 | 1 | 1 | 4444444444 | ||||
| 21 | fgi inc 3 | 6 | 3 | 1 | 1 | 4444444444 | ||||
| 22 | fgi inc 2 | 5 | 4 | 1 | 1 | 5555555555 | ||||
| 23 | vv ltd | 7 | 5 | 3 | 4 | 9999999999 | ||||
Sheet1 | ||||||||||
Hello,
This is my first post. I'm trying to create a procedure to clean up a large database with multiple related or duplicate clients. Related clients are defined (for example) as clients having either the same name or telephone number. I want to create an aggregation field with a unique number for each set of related clients.
In the file below I've started by creating a one-variable grouping for both variables by sorting and using an if formula to compare to the previous record. My attempt at consolidating the two aggregations is outlined in the file.
Note that this file uses multiple circular references with calculation interations set at max of 2. This is the first time I'm trying this, I imagine ideally loops might be better but I don't know visual basic.
{As a side note, I'm not sure why my Agg Try field has gaps in the consecutive numbering of the groups, ie 1,3,4 instead of 1,2,3. This didn't happen till I had to format to fit the html limit. Not a big deal, just hope it's not a sign of an unreliable procedure.)
Is the logic of this process reasonable? It seems to work on this small dataset, but I'm not sure if I've covered off all scenarios of sortations and groupings of client records, and if it would be efficient on a large file of 50,000 records. I've been staring at this for too long and need a fresh Any help or feedback would be appreciated. Thanks for your help!!!
Dee
