1 for unique value, 0 for non-unique, based on row P filtered on row I

**varyen** · 07-06-2022, 06:26 AM

Hey all,

I've been struggling with excel and can't quite seem to get this right.
I want to get a 1 for each unique value in row M, and a 0 for each non-unique value based on the value in row K, filtered on the value in row I.

Example:
Row I has an ID, per this ID several assets are used, these assets are categorized in row M. Certain ID's in row I have used a certain asset in row M several times. I only want to count 1 of these, and not several.

Can anyone be of help here?

**alansidman** · 07-06-2022, 11:25 AM

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

**varyen** · 07-06-2022, 11:35 AM

Thanks for the comment, updated the post with a small visual

**AliGW** · 07-06-2022, 11:42 AM

A workbook, please, not a screenshot of it. Thanks.

**varyen** · 07-06-2022, 11:45 AM

Thank you, removed the screenshot and added the workbook.

**nick.williams** · 07-06-2022, 11:48 AM

Try in M2, copied down:

=IF(COUNTIFS($I$2:I2,I2,$K$2:K2,K2)>1,0,1)

**AliGW** · 07-06-2022, 11:50 AM

In M2 copied down:

=IF(COUNTIFS($I$2:$I$18,I2,$K$2:$K$18,K2)=1,1,0)

For a Dutch locale:

=IF(COUNTIFS($I$2:$I$18;I2;$K$2:$K$18;K2)=1;1;0)

**nick.williams** · 07-06-2022, 11:55 AM

@Ali, not sure that works, think $I$18 needs to be I2 and $K$18 needs to be K2?

**varyen** · 07-06-2022, 11:59 AM

This worked perfectly, thank you for the quick solve!

**AliGW** · 07-06-2022, 12:01 PM

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks for the rep.

**nick.williams** · 07-06-2022, 12:04 PM

Happy to help

1 for unique value, 0 for non-unique, based on row P filtered on row I

LinkBack

Thread Tools

Rate This Thread

Display

1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Re: 1 for unique value, 0 for non-unique, based on row P filtered on row I

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Transpose data in six columns into unique and multiple rows based on unique club name

[SOLVED] Filter Data Based on Unique Column Items and Save the Workbook with Filtered Items

[SOLVED] FREQUENCY of unique value and SUM of unique values based on criteria

Create unique # of worksheets based on list count with unique names

[SOLVED] A lookup function based on two criteria; one unique & one not unique to the lookup table

[SOLVED] Count unique entries based off unique criteria in another column

[SOLVED] Vlook up with Unique Id Returning Multiple values,show multiple rows based on unique ID

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions