CODEWITHSUNDEEP
excel
Chase Kramer
Chase Kramer

Reputation: 3

How to make an if statement based on multiple boolean variables contained in a single comma delimited cell

I have product data in an excel sheet. The products have a parent/child relationship, so in one column is the parent sku, and in the second column is all the child skus linked to the parent, in a comma delimited format.

I have a second excel sheet with the stock status of all the children (1 = in stock, 2 = out of stock).

I'm trying to figure out a way to say "if ALL the child skus for a given parent sku are out of stock, mark the parent out of stock, if not, mark it in stock".

Sheet 1

Sheet 2

I know I can use text to columns to separate the child skus into different columns, but then I'm left with rows of varying lengths, since the number of child skus tied to each parent varies. I thought about trying to apply vlookup or hlookup or match and index, but for the life of me I can't think of a practical way to accomplish this. In reality my data set is thousands of rows long and there are anywhere from 2 to 30 child skus associated with each parent sku.

FYI, I'm pretty versed at formula manipulation in Excel, but I don't know much of anything about using macros.

Upvotes: 0

Views: 133

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

This will iterate the comma delimited text and sum all the child status. If >0 it returns in stock otherwise out of stock

=IF(SUMPRODUCT(SUMIFS(G:G,F:F,TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",999)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))-1)*999+1,999)))),"In Stock","Not Instock")

enter image description here

Upvotes: 1

Related Questions