Selecting minimum based on multiple criteria

**Vend1301** · 02-08-2016, 06:38 PM

Hi guys,
I'm a bit struggling to construct the following

3PTON-MECK.1 consists of two companies: 29712 and 11301.
I want to check for the overlap in three digit codes between the two companies.
If there is overlap, the minimum should be selected. If no overlap, it should be 0.
As can be derived from the picture: for 3Pton-Meck.1 there is overlap in 541 and 511. There is no overlap in 561 en 323.
Minimum of 541 is 0.0599. Minimum for 511 is 0.3454. Sum is 0.4054.

For AA-IXX there is no overlap in any 3 digit code, so outcome should be 0.

Hope you can help me out with this big problem for me.

Finger-Kreininv2.png

https://app.box.com/s/2sncet0a8cp5ubj4npjk42b31jfa2uow

**hemesh** · 02-08-2016, 10:58 PM

in E1 copy paste below and drag down

Formula:

Please Login or Register  to view this content.

**Vend1301** · 02-09-2016, 07:19 AM

Originally Posted by hemesh

in E1 copy paste below and drag down

Formula:

Please Login or Register  to view this content.

Hello Hemesh,

Thank you for your reply!

The formula only produces 0's unfortunately. The first term of your formula produces namely N.A.'s. In addition, I can't distinguish the factor in your factor that should give excel the order to give the value in column D if 3-digit code is present in both sets of companies within the same (...)-(...).

Hope we can work this out together!

Thsnks in advance!

**JohnTopley** · 02-09-2016, 07:26 AM

Please post an Excel file so we some data to work with and show expected results

**Vend1301** · 02-09-2016, 08:02 AM

ConstructionOverlap.xlsx

Of course! Hope this works. Thanks!

**José Augusto** · 02-09-2016, 08:36 AM

Hi
Try these in E2
=

Formula:

Please Login or Register  to view this content.

Regards

**shukla.ankur281190** · 02-09-2016, 08:36 AM

try

E2 with CRTL+SHIFt+ENTER

Formula:

Please Login or Register  to view this content.

Check the attached file.

**José Augusto** · 02-09-2016, 09:44 AM

My post did not give full response to the request. Now yes, I suppose

Formula:

Please Login or Register  to view this content.

**Vend1301** · 02-09-2016, 09:55 AM

Originally Posted by shukla.ankur281190

try

E2 with CRTL+SHIFt+ENTER

Formula:

Please Login or Register  to view this content.

Check the attached file.

Hi Shukla,

Thanks for your effort and quick reply. I think we're almost there!
However, after a quick check, some values are not correct using this formula.

See the file attached. Would aprreciate it, if you could take a look at it!

Is it also possible to construct a formula, (in a seperate column) that only counts these values once, so that it is easy to sum them and get the total?

Thanks so much!

**Vend1301** · 02-09-2016, 10:02 AM

Originally Posted by José Augusto

My post did not give full response to the request. Now yes, I suppose

Formula:

Please Login or Register  to view this content.

Hi Jose,

Thanks for thinking with me. Although your formula seems to be a lot different from the one Shukla posted, it leads to exactly the same results (and the same errors). Thank you for that!
I would appreciate it when you could have a look at the irregularities. And d you know a formula that only counts these once? So that it sums up easily.
Please refer to attached file

**Vend1301** · 02-09-2016, 11:05 AM

Row 50-51 should be green instead of red, I'm sorry.

**Vend1301** · 02-09-2016, 11:39 AM

I've grouped the errors.

It looks like there is only one case that gives errors:

2 or more of the same 3-digit code is reported for the same company, while this code is not present for the other company.

See attachment for details.

**José Augusto** · 02-09-2016, 01:18 PM

Hi
See these in O2

Formula:

Please Login or Register  to view this content.

**Vend1301** · 02-09-2016, 01:42 PM

Hi Jose

I solved it by adjusting the third row to only count once for each company, but this solution is even better!

Thanks guys for helping me!!

**José Augusto** · 02-09-2016, 02:03 PM

Hi

I am happy to have helped. Do not forget to mark this thread as solved.

Regards

Selecting minimum based on multiple criteria

LinkBack

Thread Tools

Rate This Thread

Display

Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Re: Selecting minimum based on multiple criteria

Thread Information

Users Browsing this Thread

Similar Threads

Selecting a vallue from multiple cells based on a set of criteria

[SOLVED] Finding minimum and maximum values based on multiple criteria and filtered data

Finding minimum value in a column based on multiple text criteria in other columns

Selecting Data Based on Multiple Criteria

Selecting an interval based on a number for multiple criteria...

Minimum based on criteria

find minimum of range based on multiple criteria

Bookmarks

Bookmarks

Posting Permissions