working with ID and ParentID as criteria

**rinkjames** · 06-23-2012, 02:30 PM

This is based on my older question (here).

I have 2 columns that list ID and parentID and a few others that list criteria, such as type, etc. I'd like to return a value (say 0) for those rows that satisfy certain criteria based on the criterial columns - call these *rows. However, I'd also like to return a value (say "blank") for those rows (and all its "children rows") whose parentID matches the ID of a *row - call these **rows.

There are 3 criteria for *rows:

1. type must be "branch," and
2. critA must be A, and
3. crit B must be 0

I've managed to use an array formula to return "blank" for **rows, but only for the first match. Here is a table illustrating what I'm trying to do and here is an attachment with formulae: Workbook1.xlsx.

	ID	parentID	type	critA	critB
	27		root	A	1
*row	39	29	branch	A	0	0
	635	32	terminal	A	0
	33	32	branch
**row	40	39	branch			blank
	775	22	terminal	A	1
	34	33	branch
**row	812	40	branch			blank
	37	34	terminal
**row	813	812	terminal			blank
*row	781	12	branch	A	0	0
	14	13	internal
**row	784	781	branch			blank
	15	14	internal
**row	782	784	branch			blank
	19	18	final

The last column illustrates what I'd like the formula to return. Help would be appreciated.

**travler75** · 06-23-2012, 02:56 PM

Copy of Workbook1.xlsx

Do the parent and ID mean anything, or are you just caring about Branch, internal, and final verse the criteria? I went ahead and provided you a different way to look at it. I think I understand what you are trying to do. I prefer an if/and statement it is cleaner.

**rinkjames** · 06-23-2012, 03:02 PM

Well these rows list info for different nodes on a tree diagram, so the ID and parentID shows the structure. Without them, I'm not sure I'd be able to figure out how to isolate specific nodes and their descendants, but if it seems like it might be unnecessary then I'd still be interested to see a solution.

**travler75** · 06-23-2012, 03:07 PM

I didn't see any thing to create criteria so I am assuming you are entering that manually? What is column G represent?

**rinkjames** · 06-23-2012, 03:12 PM

Yes, in the attachment, I've entered the IDs and parentIDs manually. I'm happy to do that though. Column G in the attachment is just my attempt to single out *rows, but it only picks up the ID of the first match. I'm not sure how to return 39 for the first match and 781 for the second match, so that I can feed them into a formula (in column H) where I can assign values for **rows. Does this makes sense?

**shg** · 06-23-2012, 03:16 PM

In A4 and copy down,

=IFERROR(IF(AND(D4="branch", E4="A", F4=0), "*row", IF(OR(INDEX(A$3:A3, MATCH(C4, B$3:B3, 0)) = {"*row","**row"}), "**row", "")), "")

Please Login or Register  to view this content.

**rinkjames** · 06-23-2012, 03:23 PM

That seems to work well, thanks.

**rinkjames** · 06-23-2012, 05:48 PM

Is it possible to alter the above formula so that it doesn't rely on the values for "*row" and "**row" ? I intend to replace those values with others that are not unique and I think it sort of breaks the formula.