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):

PIN	PINStatus	PINTYPE	PREMISESID
01	Revoked	PON	123
02	Active	HIPON	223
03	Active	MIPON	233
04	Revoked	HIPON	123
05	Active	PON	243
06	Revoked	MIPON	123

<tbody>
</tbody>

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

Premises ID	PON	PON#	HIPON	HIPON#	MIPON	MIPON#
123	Y	01	Y	04	Y	06

<tbody>
</tbody>

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:

PIN	PINStatus	PINTYPE	PREMISESID
01	Revoked	PON	123
02	Active	HIPON	223
03	Active	MIPON	233
04	Revoked	HIPON	223
05	Active	PON	243
06	Revoked	MIPON	123

<tbody>
</tbody>

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

Premises ID	PON	PON#	HIPON	HIPON#	MIPON	MIPON#
223	N	N	Y	06	N	N

<tbody>
</tbody>

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!