Identify Duplicates

I confess I don't think I follow the requirements...

If as implied you have only the address with which to base your search upon then I'm afraid there is no logic which can be applied to locate other addresses for a given Company Code.

Originally Posted by whamps11

I did a VLOOKUP where I looked up the address from the hospital Spreadsheet and matched it to the Insurance Specific code

I'm afraid I don't see where you're matching the Insurance Specific Code... all I can see is you returning Company Code / Carrier Name from Billings based on the one common field between the two tables namely the Address.

I would approach this slightly differently.

Please Login or Register  to view this content.

then...

Please Login or Register  to view this content.

try pivot.....but you must combine the the two worksheet and

Are you saying you can't get it to work in the sample file or you can't adapt it ? If you apply the steps to the sample file it should work without incident.

In terms of what it does...

The first formula on billing sheet creates a KEY code, the code is a concatenation of the count of times the address has appeared up to and including the current row and the address itself, eg:

1:Address1
1:Address2
2:Address1
3:Address1
etc...

it is the Key that allows you to create the table of Plan Codes relatively efficiently (without need for Arrays etc...)

Regards the formulae on the 2nd sheet, the first in Column F:

Please Login or Register  to view this content.

is as you say used to determine how many times the given address appears on the Billing sheet, however, it first checks to see if the address has already been listed on the current sheet and if so just uses prior calculated result given the result is the same - no need to recalculate the COUNTIF.

The second formula used to populate the matrix returns the plans based on the combination of Address and number (row 1) by using the KEY column on Billing sheet,

eg if header row is 1 and Address is "Address" the formula:

Please Login or Register  to view this content.

will look in they new KEY column for "1:Address" ... and will return the associated value from column B (plan)... note it will only look for a plan where the KEY will exist, ie if header > column F then there is no plan to retrieve (given the #:address won't exist)....
in the above this is done by using COLUMNS>F but in reality it might make more sense (logically) to use G$1>$F2.

It is of paramount importance therefore that the headers in G1 onwards are 1 to n where n is determined by the most no. of plans any one Address has listed on Billing sheet (in your sample this was I believe 19).

Originally Posted by DO

G1:Y1 -> 1 to 19

the above means:

enter numbers 1 thru 19 in cells G1:Y1 respectively
ie G1 is 1, H1 is 2, I1 is 3 and so on until Y1 is 19

it does not mean type Y1 -> 1 to 19 into G1 ... I should have perhaps been clearer on that fact.