OFFSET AND FIND function in one

[thestones757]

Hey all-

I am setting up a workbook with multiple sheets to be a template used in the future. The first: "Providers" has the provider and insurance policy listed for every scenario. On a later sheet: "Individual", I am trying to sum data from a different sheet. In order to do this, I need to represent the different individual insurances on the "Individual" tab. Since there are multiple providers for the same insurance, I am trying to take out the duplicates. In order to represent this I equate A2 in "Individual" to =Providers!B3 because this is the first insurance company. Now I am trying to get the next one (further down the column) but not use any of the duplicates or it will mess up the totals from the data used in a different tab. I am having a very tough time with this, the problem is I want to find A2 and then offset until it is not a duplicate, so I think I am incorporating a FIND or MATCH function as the reference for an OFFSET function but what I am doing is not working. Here is what I tried but it did not work:

A3=OFFSET(MATCH(A2,Providers!$A$3:$A$500,FALSE),1,0,1,1)
and so on...

Any help is greatly appreciated!

 

[Aladin Akyurek]

Hey all-

I am setting up a workbook with multiple sheets to be a template used in the future. The first: "Providers" has the provider and insurance policy listed for every scenario. On a later sheet: "Individual", I am trying to sum data from a different sheet. In order to do this, I need to represent the different individual insurances on the "Individual" tab. Since there are multiple providers for the same insurance, I am trying to take out the duplicates. In order to represent this I equate A2 in "Individual" to =Providers!B3 because this is the first insurance company. Now I am trying to get the next one (further down the column) but not use any of the duplicates or it will mess up the totals from the data used in a different tab. I am having a very tough time with this, the problem is I want to find A2 and then offset until it is not a duplicate, so I think I am incorporating a FIND or MATCH function as the reference for an OFFSET function but what I am doing is not working. Here is what I tried but it did not work:

A3=OFFSET(MATCH(A2,Providers!$A$3:$A$500,FALSE),1,0,1,1)
and so on...

Any help is greatly appreciated!

Is this what you are after?...

A3, control+shift+enter, not just enter:

=IF(ROWS($A$3:A3)<=COUNTIF(Providers!$A$3:$A$500,$A$2),INDEX(Providers!$A$3:$A$500,SMALL(IF(Providers!$A$3:$A$500=$A$2,ROW(Providers!$A$3:$A$500)-ROW(Providers!$A$3)+1),ROWS($A$3:A3))),"")

 

[thestones757]

Great to see you back on and giving me advice again! It seems pretty complex for me right now so I tried just copying and pasting (control+****+enter) but it would not give me what I wanted, one thing I thought looked interesting is that two instances of $A$3:A3, is this really supposed to start as this location or is it intended to start at A2?

Thanks for your help I really hope you can help me figure this out!

 

[thestones757]

Specifically, I am thinking if we count the previous (cell above) like you had in the array from the "Providers" tab with a countif equation and if that is less than or equal to one we can OFFSET by 1 row (since it is singular we would only need to offset one row). However, if the countif equation is greater than one (it is listed more than once) than we need to OFFSET by the # returned by the countif equation.

Do you think something like this could work?

 

[Aladin Akyurek]

Great to see you back on and giving me advice again! It seems pretty complex for me right now so I tried just copying and pasting (control+****+enter) but it would not give me what I wanted, one thing I thought looked interesting is that two instances of $A$3:A3, is this really supposed to start as this location or is it intended to start at A2?

Thanks for your help I really hope you can help me figure this out!

The formula is supposed to return all instances of A2, the lookup value.

It must be entered in A3. ROWS($A$3:A3) gives 1, ROWS($A$3:A4) gives 2, and so on.

 

[T. Valko]

Hey all-

I am setting up a workbook with multiple sheets to be a template used in the future. The first: "Providers" has the provider and insurance policy listed for every scenario. On a later sheet: "Individual", I am trying to sum data from a different sheet. In order to do this, I need to represent the different individual insurances on the "Individual" tab. Since there are multiple providers for the same insurance, I am trying to take out the duplicates. In order to represent this I equate A2 in "Individual" to =Providers!B3 because this is the first insurance company. Now I am trying to get the next one (further down the column) but not use any of the duplicates or it will mess up the totals from the data used in a different tab. I am having a very tough time with this, the problem is I want to find A2 and then offset until it is not a duplicate, so I think I am incorporating a FIND or MATCH function as the reference for an OFFSET function but what I am doing is not working. Here is what I tried but it did not work:

A3=OFFSET(MATCH(A2,Providers!$A$3:$A$500,FALSE),1,0,1,1)
and so on...

Any help is greatly appreciated!

Maybe this is what you're after:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3