Returning an ID# given two criteria

[uceaamh]

Hello,

I've been trying to write a formula to return an ID based on two criteria: 1. a unique premises ID, and 2. category type. The data sits on one sheet, the return info on another.

It looks like this:

DATA (In Table2):

[TABLE="width: 500"]
<tbody>[TR]
[TD]PIN[/TD]
[TD]PINStatus[/TD]
[TD]PINTYPE[/TD]
[TD]PREMISESID[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]Revoked[/TD]
[TD]PON[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]Active[/TD]
[TD]HIPON[/TD]
[TD]223[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]Active[/TD]
[TD]MIPON[/TD]
[TD]233[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]Revoked[/TD]
[TD]HIPON[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]05[/TD]
[TD]Active[/TD]
[TD]PON[/TD]
[TD]243[/TD]
[/TR]
[TR]
[TD]06[/TD]
[TD]Revoked[/TD]
[TD]MIPON[/TD]
[TD]123[/TD]
[/TR]
</tbody>[/TABLE]

Trying to organize it, in a different sheet, like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Premises ID[/TD]
[TD]PON[/TD]
[TD]PON#[/TD]
[TD]HIPON[/TD]
[TD]HIPON#[/TD]
[TD]MIPON[/TD]
[TD]MIPON#[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Y[/TD]
[TD]01[/TD]
[TD]Y[/TD]
[TD]04[/TD]
[TD]Y[/TD]
[TD]06[/TD]
[/TR]
</tbody>[/TABLE]

I have this:

=IF(AND((INDEX(Table2[PremisesID],(MATCH(A13,Table2[PremisesID],0))))=A13,(INDEX(Table2[PINType],(MATCH(Table1[PON],Table2[PINTYPE]))))=Table1[PON]),LEFT(Table2[PIN)],999),"N")

Which has returned results, but the wrong ones.

Any help would be appreciated.

 

[Chrisdontm]

From the example of your desired results it looks like you are calling that Table: Table1
and it looks like it starts in cell A12.

Below are six formulas to put in each column to the right of A13
Let me know if I am not understanding you right.

In cell B13 try;
=IF(SUMPRODUCT((Table2[PREMISESID]=$A13)*1,(Table2[PINTYPE]=Table1[[#Headers],[PON]])*1)=1,"Y","N")

In cell C13 try;
=SUMPRODUCT((Table2[PREMISESID]=$A13)*1,(Table2[PINTYPE]=Table1[[#Headers],[PON]])*1,(Table2[PIN])*1)

In cell D13 try;
=IF(SUMPRODUCT((Table2[PREMISESID]=$A13)*1,(Table2[PINTYPE]=Table1[[#Headers],[HIPON]])*1)=1,"Y","N")

In cell E13 try;
=SUMPRODUCT((Table2[PREMISESID]=$A13)*1,(Table2[PINTYPE]=Table1[[#Headers],[HIPON]])*1,(Table2[PIN])*1)

In cell F13 try;
=IF(SUMPRODUCT((Table2[PREMISESID]=$A13)*1,(Table2[PINTYPE]=Table1[[#Headers],[MIPON]])*1)=1,"Y","N")

In cell G13 try;
=SUMPRODUCT((Table2[PREMISESID]=$A13)*1,(Table2[PINTYPE]=Table1[[#Headers],[MIPON]])*1,(Table2[PIN])*1)

 

[uceaamh]

Chrisdontm,

That worked. Thanks alot! Sorry if that explanation wasn't totally clear - I was struggling to explain to myself what I needed done, so the initial post wasn't great.

Now I'm running into a second problem. This spreadsheet is tracking notes (3 different types of note, PON, HIPON, and MIPON, each with their own identifier - the #) against a particular location (Premises ID). Sometimes one location can have multiple instances of one type of note. Using SUMPRODUCT now gives me the sum of these notes identifying #'s.

For example:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]PIN[/TD]
[TD]PINStatus[/TD]
[TD]PINTYPE[/TD]
[TD]PREMISESID[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]Revoked[/TD]
[TD]PON[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]Active[/TD]
[TD]HIPON[/TD]
[TD]223[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]Active[/TD]
[TD]MIPON[/TD]
[TD]233[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]Revoked[/TD]
[TD]HIPON[/TD]
[TD]223[/TD]
[/TR]
[TR]
[TD]05[/TD]
[TD]Active[/TD]
[TD]PON[/TD]
[TD]243[/TD]
[/TR]
[TR]
[TD]06[/TD]
[TD]Revoked[/TD]
[TD]MIPON[/TD]
[TD]123[/TD]
[/TR]
</tbody>[/TABLE]

Where HIPON 02 and 04 are both on the same Premises (223) would give the result:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Premises ID[/TD]
[TD]PON[/TD]
[TD]PON#[/TD]
[TD]HIPON[/TD]
[TD]HIPON#[/TD]
[TD]MIPON[/TD]
[TD]MIPON#[/TD]
[/TR]
[TR]
[TD]223[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]06[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions how to tackle that problem?

Thanks again

 

[Chrisdontm]

I just saw your response.
It seems like I didn't get an email response...???

Anyway;
So how many possible answers would you need in any one cell?

 

[uceaamh]

Ideally we need just the one - when there are multiples of one notice type on one premises, someone made a mistake.

I've seen up to 3 notices of the same type on one premises as maximum. I guess to be safe I'd want to be able to record up to 5 answers per cell.

 

[Chrisdontm]

This should take care of your situation;

https://www.dropbox.com/s/y1qspls21hzht1z/uceaamh.... MrExcel Helper Page.xlsb?dl=0

 

[uceaamh]

This should take care of your situation;

https://www.dropbox.com/s/y1qspls21hzht1z/uceaamh.... MrExcel Helper Page.xlsb?dl=0

Wow, that is some formula. Plugged it in, works smooth. Thanks so much @Chrisdontm. There's no way I could have structured that.

Thanks again!