How to get non-duplicates with fixed data?

[chriswoon]

Hi everyone,

I have a set contact data.

First column: name
Second column: type of contact
Third column: number for type of contact

Issue is that some "types of contacts" have the same number as other "types of contacts" and I don't need the duplicates.

Fourth column: Formula that outputs the number for type of contact only once. ie non-duplicates if any.

eg

Col 1-- Col 2--- Col 3----------- Col 4
Jack-- home-- 0312345678 0312345678
(blank) work-- 0958765456 0958765456
John-- home-- 0210123456 0210123456
(blank) work-- 0358712567 0358712567
(blank) mobile 0210123456 -
**More Contacts**

One contact may have 0 contacts or unlimited number of contact number entries (realistically 6 max).
Hope you guys can help. I have attached an example on excel.

[DonkeyOte]

It's not really clear to me if you're resulting list is to be one contiguous listing of unique numbers or if in fact the numbers should be listed adjacent to the name ?

Assume for sake of ex. that in your sample file B4:C4 and B5:C5 were the same such that row 5 is ignored.
In terms of the resulting output in Col D would D5 then be blank (ie no more unique numbers for john) or would it in fact contain the next unique number in the entire data set (ie jack's first number ?)

Is the relationship between number and type 1:1 ?
that is to say a given no. would only ever appear under one "type" - never #123 home; #123 mobile
If that's not the case how do these duplicates but unique by type affect your output in Col D ?

On a final note, how big is the real dataset in terms of rows etc...

Perhaps worth posting back with a slightly larger data set.

[chriswoon]

Sorry I wasn't descriptive enough.

The unique number should be listed adjacent to the number in Col C, blank if not unique.
The relationship between number and type is not 1 to 1.
type home and type mobile can be the same
The type column is not actually there, I only put it there so it would clearer. Assume theres no type column only the numbers.

I'm actually comparing these list of numbers with the telephone call list made on a day to determine how many attempts were made on a single number and then finding the average calls made per contact. The way I did this was by counting the number of times the number appears on that telephone call list. However some contacts listed have duplicate numbers, hence double counting when counting on that telephone call list.
eg home #123 was called 5 times and mobile #123 was also called 5 times. Total would be 10 on a single contact when actually only 5 was made.

Hope you understand, welcome other suggestions if there's any.
Thanks for your time

I have attached the actual list I have problems with, changing the names and the numbers on them.

[DonkeyOte]

You will find that sorting your data appropriately will allow you to conduct more efficient calcs, however, based on your current sample - and so as to replicate your expected results:

E4:
=LOOKUP(REPT("Z",255),$A$4:$A4)&":"&$B4
copied down to E15

D4:
=IF(ISNUMBER(MATCH($E4,$E$3:$E3,0)),"",$C4)
copied down to D15

G4:
=IF($A4="","",SUMIF($I$4:$I$15,$A4&":*",$J$4:$J$15))
copied down to G15

adjust ranges as necessary.

(the concatenation in Col E limits the calc burden to an extent in handling a data set of this nature)

[chriswoon]

Thanks, that's solved.

The data comes from a program which I have no control on the structure of the output. Or can I sort the data with current output?

Also curious about how the lookup formula works with the repetitions of "Z". Care to elaborate?

Thanks again.

[DonkeyOte]

Re: use of LOOKUP ... something of a recurring discussion but see: http://www.xldynamic.com/source/xld.LastValue.html for info. on last values.

In terms of LOOKUP mechanics:

LOOKUP assumes all values in the lookup_vector are sorted in ascending order (irrespective of reality)

LOOKUP returns last value <= criteria in the lookup_vector (or associated value from result_vector if specified)

LOOKUP ignores all values in the lookup_vector that are not of the same data type as the criteria

So if we set the criteria to be a BIG text string - ie Z repeated 255 times it follows that given:

a) LOOKUP assumes the last value found in the lookup vector to be the "biggest"

b) All values in the lookup vector are < criteria (and non-text values are ignored - different type)

that LOOKUP returns the last text string from the LOOKUP vector (in this case the last name).