Returning an ID# given two criteria

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
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):

PINPINStatusPINTYPEPREMISESID
01RevokedPON123
02ActiveHIPON223
03ActiveMIPON233
04RevokedHIPON123
05ActivePON243
06RevokedMIPON123

<tbody>
</tbody>

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

Premises IDPONPON#HIPONHIPON#MIPONMIPON#
123Y01Y04Y06

<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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
811
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

New Member
Joined
Feb 23, 2017
Messages
27
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:

PINPINStatusPINTYPEPREMISESID
01RevokedPON123
02ActiveHIPON223
03ActiveMIPON233
04RevokedHIPON223
05ActivePON243
06RevokedMIPON123

<tbody>
</tbody>

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

Premises IDPONPON#HIPONHIPON#MIPONMIPON#
223NNY06NN

<tbody>
</tbody>

Any suggestions how to tackle that problem?

Thanks again
 

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
811
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

New Member
Joined
Feb 23, 2017
Messages
27
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.
 

Forum statistics

Threads
1,171,832
Messages
5,877,817
Members
433,288
Latest member
Kietkiengiang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top