Count if used at higher level

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
I have a list. The first column is the Parent Part Number, the second is the individual parts that go into each parent.
I am trying to see if the individual part is used in more than one parent. What I need is to come up with a percentage. its kind of hard to explain. Hope this helps. The Percentage is what I am trying to figure out the code for.

ABC 123 33.3%
ABC 234 50.0%
ABC 123 33.3%
ABC 752 100.0%
DEF 123 33.3%
DEF 234 50.0%
DEF 546 100.0%
GHI 123 33.3%
GHI 753 100.0%

Part 123 is used on all three (ABC, DEF and GHI) so its 33.3%. Even though its used twice on ABC I only count it once. That's what makes it tricky to calculate
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,308
Maybe...


A
B
C
1
Parent​
Parts​
Result​
2
ABC​
123​
33,30%​
3
ABC​
234​
50,00%​
4
ABC​
123​
33,30%​
5
ABC​
752​
100,00%​
6
DEF​
123​
33,30%​
7
DEF​
234​
50,00%​
8
DEF​
546​
100,00%​
9
GHI​
123​
33,30%​
10
GHI​
753​
100,00%​

Array formula in C2 copied down
=1/SUM(IF(FREQUENCY(IF(B$2:B$10=B2,MATCH(A$2:A$10,A$2:A$10,0)),ROW(A$2:A$10)-ROW(A$2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
Try

=1/(COUNTIFS($B$1:$B$9,$B1)-(--(COUNTIFS($A$1:$A$9,"<>"&$A1,$B$1:$B$9,$B1)>=2)))

Code:
[COLOR=#000000][FONT=-webkit-standard][TABLE="width: 261"]
<colgroup><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87"]ABC[/TD]
[TD="width: 87, align: right"]123[/TD]
[TD="class: xl64, width: 87, align: right"]33.3%[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC[/TD]
[TD="align: right"]234[/TD]
[TD="class: xl64, align: right"]50.0%[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC[/TD]
[TD="align: right"]123[/TD]
[TD="class: xl64, align: right"]33.3%[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC[/TD]
[TD="align: right"]752[/TD]
[TD="class: xl64, align: right"]100.0%[/TD]
[/TR]
[TR]
[TD="class: xl63"]DEF[/TD]
[TD="align: right"]123[/TD]
[TD="class: xl64, align: right"]33.3%[/TD]
[/TR]
[TR]
[TD="class: xl63"]DEF[/TD]
[TD="align: right"]234[/TD]
[TD="class: xl64, align: right"]50.0%[/TD]
[/TR]
[TR]
[TD="class: xl63"]DEF[/TD]
[TD="align: right"]546[/TD]
[TD="class: xl64, align: right"]100.0%[/TD]
[/TR]
[TR]
[TD="class: xl63"]GHI[/TD]
[TD="align: right"]123[/TD]
[TD="class: xl64, align: right"]33.3%[/TD]
[/TR]
[TR]
[TD="class: xl63"]GHI[/TD]
[TD="align: right"]753[/TD]
[TD="class: xl64, align: right"]100.0%[/TD]
[/TR]
</tbody>[/TABLE]
[/FONT][/COLOR]
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
Thanks, but i was looking for VBA Code. My range will vary depending on the data being imported, so these will not work for what I need. But I really appreciate the help.

I take that back, I can use it. Just need to tweak the formula
=1/(COUNTIFS($B:$B,$B1)-(--(COUNTIFS($A:$A,"<>"&$A1,$B:$B,$B1)>=2)))
 
Last edited:

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
OK I take that back once again - lol this only works if the part is listed once for the parent. 1234 is listed 4 times but its in three parents, so the answer should be 33.3% like it shows above. However when I use the formula in a sheet it calculates out to 25%
 

Forum statistics

Threads
1,078,442
Messages
5,340,308
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top