labelling group if condition is met

[ajm]

interesting problem. i am working with machine parts. a machine part may come from one or many suppliers. I need to identify which parts have one supplier and which parts have many suppliers. i have my list of parts, and i have the name of each supplier that they have come from. i need a third field that states simply One or Many. i also will have to redo this table for multiple sites with thousands of parts so don't want to do it manually. i am thinking that there might be a solution using countif similar to when looking for duplicates in a long list. however, this will have to look for changes in the One or Many column for each part. ?????????

 

[Sufiyan97]

share your sample data using below link XL2BB

 

[ajm]

my apologies. i have worded my query incorrectly. have been discussing with the boss and i was going about this the wrong way. we have a list of suppliers providing certain parts. some parts are in use, some are not. need to label the supplier either Active, Inactive, or Partially Active based on their supply of parts. the part i am struggling with is the Partially. This applies when a supplier has provided a mix of in use and no longer used parts. table below

so, Supplier, part number, whether we use it or not. The last column is the desired outcome and is where i need to come up with a label. This grid is adapted from a pivot table but can apply a formula to the original data if easier.

Supplier	Part Number	Part Used	Supplier Activity
Supplier 1	0000000020200987	Active	Active
Supplier 2	0000000020188139	Active	Active
	0000000020198190	Active
Supplier 3	0000000020184425	Inactive	Inactive
Supplier 4	0000000020184649	Active	Partially Active
	0000000020185787	Inactive
	0000000020187295	Active
Supplier 5	0000000021794878	Active	Active
	0000000021810988	Active
Supplier 6	0000000021404803	Inactive	Partially Active
	0000000021404810	Active
	0000000021404817	Active
	0000000021404864	Active
	0000000021404871	Active

 

[Sufiyan97]

Try with helper column E

IF Practice.xlsx
	A	B	C	D	E	F	G
1	Supplier	Part Number	Part Used	Supplier Activity	Helper	Supplier Activity
2	Supplier 1	20200987	Active	Active	Supplier 1	Active
3	Supplier 2	20188139	Active	Active	Supplier 2	Active
4		20198190	Active		Supplier 2	Active
5	Supplier 3	20184425	Inactive	Inactive	Supplier 3	Inactive
6	Supplier 4	20184649	Active	Partially Active	Supplier 4	Partially Active
7		20185787	Inactive		Supplier 4	Partially Active
8		20187295	Active		Supplier 4	Partially Active
9	Supplier 5	21794878	Active	Active	Supplier 5	Active
10		21810988	Active		Supplier 5	Active
11	Supplier 6	21404803	Inactive	Partially Active	Supplier 6	Partially Active
12		21404810	Active		Supplier 6	Partially Active
13		21404817	Active		Supplier 6	Partially Active
14		21404864	Active		Supplier 6	Partially Active
15		21404871	Active		Supplier 6	Partially Active
16
17
Sheet1

Cell Formulas
Range	Formula
E2:E15	E2	=IF(ISBLANK(A2),E1,A2)
F2:F15	F2	=IF(COUNTIF(E:E,E2)=1,Q2,IF(AND(COUNTIFS(E:E,E2,C:C,"Active")>0,COUNTIFS(E:E,E2,C:C,"Inactive")=0),Q2,"Partially Active"))

 

[ajm]

Try with helper column E

IF Practice.xlsx
	A	B	C	D	E	F	G
1	Supplier	Part Number	Part Used	Supplier Activity	Helper	Supplier Activity
2	Supplier 1	20200987	Active	Active	Supplier 1	Active
3	Supplier 2	20188139	Active	Active	Supplier 2	Active
4		20198190	Active		Supplier 2	Active
5	Supplier 3	20184425	Inactive	Inactive	Supplier 3	Inactive
6	Supplier 4	20184649	Active	Partially Active	Supplier 4	Partially Active
7		20185787	Inactive		Supplier 4	Partially Active
8		20187295	Active		Supplier 4	Partially Active
9	Supplier 5	21794878	Active	Active	Supplier 5	Active
10		21810988	Active		Supplier 5	Active
11	Supplier 6	21404803	Inactive	Partially Active	Supplier 6	Partially Active
12		21404810	Active		Supplier 6	Partially Active
13		21404817	Active		Supplier 6	Partially Active
14		21404864	Active		Supplier 6	Partially Active
15		21404871	Active		Supplier 6	Partially Active
16
17
Sheet1

Cell Formulas
Range	Formula
E2:E15	E2	=IF(ISBLANK(A2),E1,A2)
F2:F15	F2	=IF(COUNTIF(E:E,E2)=1,Q2,IF(AND(COUNTIFS(E:E,E2,C:C,"Active")>0,COUNTIFS(E:E,E2,C:C,"Inactive")=0),Q2,"Partially Active"))

 

[ajm]

what's in Q2 in your formula at F2? is it just a random blank cell?

 

[ajm]

final version worked. small changes to your formula but couldn't have got there without your help. Many thanks.

=IF(COUNTIF(H:H,H7)=1,I7,IF(AND(COUNTIFS(H:H,H7,I:I,"Active")>0,COUNTIFS(H:H,H7,I:I,"Inactive")=0),"Active",IF(AND(COUNTIFS(H:H,H7,I:I,"Active")=0,COUNTIFS(H:H,H7,I:I,"Inactive")>0),"Inactive","Partially Active")))

 

[Sufiyan97]

what's in Q2 in your formula at F2? is it just a random blank cell?

I was just trying to build formula and put data in Q column and when I copied data to original location, I forgot to change the reference
below is an updated version
It was just C2 instead Q2

IF Practice.xlsx
	A	B	C	D	E	F
1	Supplier	Part Number	Part Used	Supplier Activity	Helper	Supplier Activity
2	Supplier 1	20200987	Active	Active	Supplier 1	Active
3	Supplier 2	20188139	Active	Active	Supplier 2	Active
4		20198190	Active		Supplier 2	Active
5	Supplier 3	20184425	Inactive	Inactive	Supplier 3	Inactive
6	Supplier 4	20184649	Active	Partially Active	Supplier 4	Partially Active
7		20185787	Inactive		Supplier 4	Partially Active
8		20187295	Active		Supplier 4	Partially Active
9	Supplier 5	21794878	Active	Active	Supplier 5	Active
10		21810988	Active		Supplier 5	Active
11	Supplier 6	21404803	Inactive	Partially Active	Supplier 6	Partially Active
12		21404810	Active		Supplier 6	Partially Active
13		21404817	Active		Supplier 6	Partially Active
14		21404864	Active		Supplier 6	Partially Active
15		21404871	Active		Supplier 6	Partially Active
16
17
Sheet1

Cell Formulas
Range	Formula
E2:E15	E2	=IF(ISBLANK(A2),E1,A2)
F2:F15	F2	=IF(COUNTIF(E:E,E2)=1,C2,IF(AND(COUNTIFS(E:E,E2,C:C,"Active")>0,COUNTIFS(E:E,E2,C:C,"Inactive")=0),C2,"Partially Active"))

 

[Sufiyan97]

Hello please see below Results for above formulas and updated formula in column H

IF Practice.xlsx
	A	B	C	D	E	F	G	H	I
1	Supplier	Part Number	Part Used	Supplier Activity	Helper	A Durfani	ajm	Update
2	Supplier 1	20200987	Active	Active	Supplier 1	Active	Active	Active
3	Supplier 2	20188139	Active	Active	Supplier 2	Active	Active	Active
4		20198190	Active		Supplier 2	Active	Partially Active	Active
5	Supplier 3	20184425	Inactive	Inactive	Supplier 3	Inactive	Inactive	Inactive
6	Supplier 4	20184649	Active	Partially Active	Supplier 4	Partially Active	Active	Partially Active
7		20185787	Inactive		Supplier 4	Partially Active	Partially Active	Partially Active
8		20187295	Active		Supplier 4	Partially Active	Partially Active	Partially Active
9	Supplier 5	21794878	Active	Active	Supplier 5	Active	Active	Active
10		21810988	Active		Supplier 5	Active	Partially Active	Active
11	Supplier 6	21404803	Inactive	Partially Active	Supplier 6	Partially Active	Inactive	Partially Active
12		21404810	Active		Supplier 6	Partially Active	Partially Active	Partially Active
13		21404817	Active		Supplier 6	Partially Active	Partially Active	Partially Active
14		21404864	Active		Supplier 6	Partially Active	Partially Active	Partially Active
15		21404871	Active		Supplier 6	Partially Active	Partially Active	Partially Active
16	Supplier 7	21794878	Inactive		Supplier 7	Partially Active	Inactive	Inactive
17		21810988	Inactive		Supplier 7	Partially Active	Partially Active	Inactive
18
19
Sheet1

Cell Formulas
Range	Formula
E2:E17	E2	=IF(ISBLANK(A2),E1,A2)
F2:F17	F2	=IF(COUNTIF(E:E,E2)=1,C2,IF(AND(COUNTIFS(E:E,E2,C:C,"Active")>0,COUNTIFS(E:E,E2,C:C,"Inactive")=0),C2,"Partially Active"))
G2:G17	G2	=IF(COUNTIF(A:A,A2)=1,C2,IF(AND(COUNTIFS(A:A,A2,C:C,"Active")>0,COUNTIFS(A:A,A2,C:C,"Inactive")=0),"Active",IF(AND(COUNTIFS(A:A,A2,C:C,"Active")=0,COUNTIFS(A:A,A2,C:C,"Inactive")>0),"Inactive","Partially Active")))
H2:H17	H2	=IF(COUNTIF(E:E,E2)=1,C2,IF(AND(COUNTIFS(E:E,E2,C:C,"Active")>0,COUNTIFS(E:E,E2,C:C,"Inactive")=0),C2,IF(AND(COUNTIFS(E:E,E2,C:C,"Active")>0,COUNTIFS(E:E,E2,C:C,"Inactive")>0),"Partially Active","Inactive")))