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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Chrisdontm

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

ADVERTISEMENT

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,136,331
Messages
5,675,157
Members
419,552
Latest member
jsanjur

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