Posts by BJL

BJL · Apr 18th 2017

Re: When G matches B, count the number of values in C (that do not match H)

Yes, Thank you!

BJL · Apr 18th 2017

Re: When G matches B, count the number of values in C (that do not match H)

Quote from NBVC;790743

Try these formulas copied down

=SUMPRODUCT(($B$2:$B$324=G2)*($C$2:$C$324<>H2)/COUNTIFS($B$2:$B$324,$B$2:$B$324&"",$C$2:$C$324,$C$2:$C$324&""))

=SUMPRODUCT(($B$2:$B$324=G2)*($C$2:$C$324<>H2)*($E$2:$E$324="MTY")/COUNTIFS($B$2:$B$324,$B$2:$B$324&"",$C$2:$C$324,$C$2:$C$324&"",$E$2:$E$324,$E$2:$E$324&""))

Yes! It's working.

But I notice one flaw in my own description of the problem. I also want to specify that the MPN Status in column D should be either "RL" or "SR" as well, because an MPN shouldn't count if it's already blocked. Likewise if multiple MPNs are linked to the same component (like G2 and G3) then both need to be excluded from the final count in columns J and K...

I think I know how to do it, too...

=SUMPRODUCT(($B$2:$B$324=G2)*($C$2:$C$324<>H2)*($D$2:$D$324="RL")/COUNTIFS($B$2:$B$324,$B$2:$B$324&"",$C$2:$C$324,$C$2:$C$324&"",$D$2:$D$324,$D$2:$D$324&""))

But how can I include "SR" in the formula, too?

BJL · Apr 18th 2017

Re: When G matches B, count the number of values in C (that do not match H)

edit

BJL · Apr 18th 2017

Hello,

Column H is a list of six-digit MPNs to be blocked, and Column G shows the component that the MPN relates to. So, within the list of components (columns A:E), I need to count the number of MPNs each Component has...but not including the ones to be blocked (column H). So, if there are 4 different MPN numbers; I want to show that in column J. Then, in column K, I want to show how many of those MPNs have the location of "MTY."

So, I'm just trying to find a formula for columns J and K....

I provided an example in the picture below:
H2 is the first MPN in the list. So, G2 must match B:B (I highlighted the matching component in red). So, I want to count the related MPNs in column C...but excluding the ones where C matches H (highlighted in yellow). You can see I counted the different MPNs from 1-to-4 in blue (I skipped the one in yelow) and I listed that final count in column J. Likewise, I counted column E, where the location was "MTY" and listed the final count in Column K.
[Blocked Image: http://i26.photobucket.com/albums/c117/shocktrooper327/Excel%20Example_zpso4wlcouf.png]

forum.ozgrid.com/index.php?attachment/72217/

BJL · Jan 14th 2016

Re: Marco to Insert Objects onto spreadsheet without them overlapping eachother

Perfect! Thank you! :flower:

BJL · Jan 14th 2016

I want to put 30+ .jpg pictures into a spreadsheet. I have them all in one folder too. I want to have a column of the objects, so that you just have to double click on one to view it.

Using Insert > Object > Create from File makes it easy to do one jpg at a time, but I hoping to use a macro to make it easier. I googled it, and came across a pretty nice solution here and here using the same macro, but the macro has a problem where the objects are all on top of one another.

This picture is pretty much what I want the spreadsheet to look like, only with jpg instead of txt files

[ATTACH=CONFIG]67973[/ATTACH]

BJL · Jun 2nd 2015