Which function(s)?

[LB]

If I only have one column containing the product names, how do
I pull in the name of the product that does not have either a "sub-product"
or is named "sub-product;1" (i.e. Product B;1, Product E;1 Product F;1).

In other words, I only want to pull in the following product names from my
master list below:
Product A
Product B;1
Product C
Product D
Product E;1
Product F;1
Product G

Master List:
Product A
Product B;1
Product B;2
Product B;3
Product C
Product D
Product E;1
Product E;2
Product E;3
Product F;1
Product F;2
Product G
Product H;2

 

[N Harkawat]

The column next to your master list type the following and copy it all the
way down thru to the end of your master list.
assuming your master list begins from cell A1 type this on cell b1
=IF(ISNUMBER(--RIGHT(A1,1)),--RIGHT(A1,1)>1)

Using Auto filter hide out all values that are "TRUE"
you will have the desired result

 

[Peo Sjoblom]

Use data>filter>advanced filter, copy to another location, put the criteria
in let's say
H1:H2, leave H1 blank and in H2 refer to the first cell with data (not
header),
assume it is A2, then in H2 use

=OR(A2<>condition1,A2<>Condition2,A2<>Condition3 and so on)

you can also use autoflter and a help column that will tag the data row by
row and then filter on the help column TRUE

Regards,

Peo Sjoblom

 

[hrlngrv]

LB wrote...

If I only have one column containing the product names, how do
I pull in the name of the product that does not have either a "sub-product"
or is named "sub-product;1" (i.e. Product B;1, Product E;1 Product F;1).

In other words, I only want to pull in the following product names from my
master list below:
Product A
Product B;1
Product C
Product D
Product E;1
Product F;1
Product G

Master List:
Product A
Product B;1
Product B;2
Product B;3
Product C
Product D
Product E;1
Product E;2
Product E;3
Product F;1
Product F;2
Product G
Product H;2

If your master list were names MstrLst, try these *array* formulas.

B2:
=INDEX(MstrLst,MATCH(TRUE,MID(MstrLst,FIND(";",MstrLst&";"),1024)<=";1",0))

B3:
=INDEX(MstrLst,MATCH(1,(MID(MstrLst,FIND(";",MstrLst&";"),1024)<=";1")
*(COUNTIF(B$2:B2,MstrLst)=0),0))

Select B3 and fill down as needed.

If you want to go the advanced filter route, add field names in the row
above the first entry in master table, copy that field name to the cell
just above where you want the desired records to appear, and below a
blank cell enter the criteria formula. The criteria formula requires a
relative reference to the topmost entry in master list, so I'll need to
use range addresses as placeholders. With 'Items' in cell A1, master
table entries in A2:A14, enter 'Items' in cell C1 and with E1 blank
enter the following formula in E2.

=MID(A2,FIND(";",A2&";"),1024)<=";1"

Select A1:A14, and run Data > Filter > Advanced Filter. Select E1:E2 as
your criteria range, opt to copy to another location and select C1 as
the copy to range. Then click OK.