Excel - Formula/VBA help

[ShilP]

Hi Guys

I hope you are all well. Im looking for some help to either work out a formula or some vba:

Sheet 1
Column A (Can contain an ID List)
Column B (Can contain an ID LIst)
Column C (Can Contain an ID List)
Column D (Will be used for workings)

1. In Column D - i want to look at Column A and if found in column C enter a value of 1 (as a base level to start with). Returning the rest as BLANK

Then in remainder cells of Column D, i want to look at Column B and if found in Column C enter value as 2. returning rest as blank

Then i would like to loop this somehow so that the remaining cells in Column D, look to see if Column B has a match with those with Column D and Value - 2, and if match found return in Column D Value as "3"

Then i would like to repeat this until we are left with a set of ID's not found when matched to Column B and will enter a value of Column D as "Not found".

In the End Column D will have a number 1-8, or "Not Found".

I currently do this manually using v lookups at the moment using multiple columns but was wondering if there was a quicker way as i repeat this each month.

Appreciate your help as always.

S

 

[ExceLoki]

Hey ExcelLoki

I tried extending the formula to fit with my real dataset (Up to 9 levels and 5965 records in Column A and B) to:
=IFNA(IF(MATCH(A2,$C$2:$C$21,0),1),IFNA(IF(MATCH(B2,$C$2:$C$21,0),2),IFNA(IF(MATCH(A2,$B$2:$B$5965,0),3),IFNA(IF(MATCH(B2,$A$2:$A$5965,0),4),IFNA(IF(MATCH(B2,$A$2:$A$5965,0),5),IFNA(IF(MATCH(A2,$B$2:$B$5965,0),6),IFNA(IF(MATCH(B2,$A$2:$A$5965,0),7),IFNA(IF(MATCH(A2,$B$2:$B$5965,0),8),IFNA(IF(MATCH(A2,$B$2:$B$5965,0),9),"not found")))))))))

However its only returning results up to Level 4, do you know what i need to change to get it showing 9 level numbers?

Thanks

S

some of your comparisons are the same, i've color coded the ones below that are the same. the way this formula works is to return the first one that works without an NA response. so any that will return for A2,$B$2:$B$5965 will always be "3" and B2,$A$2:$A$5965 will always be "4". what you're telling it to look for to return a 4 or a 5 are the same, so once it has a return value it stops running. hope this helps explain it.

=IFNA(IF(MATCH(A2,$C$2:$C$21,0),1),IFNA(IF(MATCH(B2,$C$2:$C$21,0),2),IFNA(IF(MATCH(A2,$B$2:$B$5965,0),3),IFNA(IF(MATCH(B2,$A$2:$A$5965,0),4),IFNA(IF(MATCH(B2,$A$2:$A$5965,0),5),IFNA(IF(MATCH(A2,$B$2:$B$5965,0),6),IFNA(IF(MATCH(B2,$A$2:$A$5965,0),7),IFNA(IF(MATCH(A2,$B$2:$B$5965,0),8),IFNA(IF(MATCH(A2,$B$2:$B$5965,0),9),"not found")))))))))

if you could put together a sample data set that goes up to 9 levels, might be able to help more. also using the xl2bb plugin helps to copy sheets and formulas. i'd recommend using it on this board.

 

[ShilP]

Thanks,

so your saying each condition in your formula is different which is why it worked to level 4.

here is a sample dataset going up to 9 Levels:

PersonParent.xlsx
	A	B	C	D
1	Person Number	Parent Number	Level 1 Population	Level Number
2	123	124	124	2
3	159	112	112	2
4	165	111	111	2
5	124	987		1
6	112	987		1
7	111	987		1
8	235	123		3
9	236	159		3
10	237	165		3
11	546	787		Not Known
12	545	787		Not Known
13	544	789		Not Known
14	458	235		4
15	457	236		4
16	456	237		4
17	595	458		5
18	596	457		5
19	597	456		5
20	311	595		6
21	312	596		6
22	313	597		6
23	211	311		7
24	212	312		7
25	213	313		7
26	601	211		8
27	602	212		8
28	603	213		8
29	782	601		9
30	788	602		9
31	781	603		9
Sheet1

Let me know if this is helpful, but i have gone and made a dataset which goes up to Level 9.

S

 

[ExceLoki]

that does help some. can you explain what are the conditions that would result in a 5, 6, 7, 8, 9?

 

[ShilP]

that does help some. can you explain what are the conditions that would result in a 5, 6, 7, 8, 9?

Sure, it works the same as the one that returns level 3 or 4.

So, if the remaining cells in Column D, look to see if Column B has a match with those with Column D and Value - 4, and if match found return in Column D Value as "5". Etc.

Then i would like to repeat this until we are left with a set of ID's not found when matched to Column B and will enter a value of Column D as "Not found".

Thanks

S

 

[bebo021999]

Below code could count up forever level:

=IFERROR(1/(1/(COUNTIF($C$2:$C$4,A2)+(COUNTIF($C$2:$C$4,B2)>0)*2+IFERROR(VLOOKUP(B2,$A$1:$D1,4,0)+1,0))),"Not Known")

 

[ShilP]

Thanks Bebo, i gave this a try and it didn't seem to work I'

Below code could count up forever level:

=IFERROR(1/(1/(COUNTIF($C$2:$C$4,A2)+(COUNTIF($C$2:$C$4,B2)>0)*2+IFERROR(VLOOKUP(B2,$A$1:$D1,4,0)+1,0))),"Not Known")

m afraid.

Would you be able to advise how to use this formula?

 

[ExceLoki]

this is using @bebo021999 's response. it is working for me.
-----------------------

ifna-if-ifna-if matching in columns.xlsx
	A	B	C	D
1	person number	parent number	level 1 population	should be
2	123	124	124	2
3	159	112	112	2
4	165	111	111	2
5	124	987		1
6	112	987		1
7	111	987		1
8	235	123		3
9	236	159		3
10	237	165		3
11	546	787		Not Known
12	545	787		Not Known
13	544	789		Not Known
14	458	235		4
15	457	236		4
16	456	237		4
17	595	458		5
18	596	457		5
19	597	456		5
20	311	595		6
21	312	596		6
22	313	597		6
23	211	311		7
24	212	312		7
25	213	313		7
26	601	211		8
27	602	212		8
28	603	213		8
29	782	601		9
30	788	602		9
31	781	603		9
Sheet1 (2)

Cell Formulas
Range	Formula
D2:D31	D2	=IFERROR(1/(1/(COUNTIF($C$2:$C$4,A2)+(COUNTIF($C$2:$C$4,B2)>0)*2+IFERROR(VLOOKUP(B2,$A$1:$D1,4,0)+1,0))),"Not Known")

 

[ShilP]

I haven't been able to apply it back to my dummy data, instead im trying to apply it to my real data. The only difference here is that the values in Column C are more than 3. Should this be an issue?

Of course i am extending the parts of the formula where it references C2 and C4.

Thanks

Shil

 

[bebo021999]

Just extend the range C2:C4 to what far ever you need

 

[ShilP]

Just extend the range C2:C4 to what far ever you need

I just tried this again and still no joy. I have updated two parts to the forumla to extend it from:
=IFERROR(1/(1/(COUNTIF($C$2:$C$4,A2)+(COUNTIF($C$2:$C$4,B2)>0)*2+IFERROR(VLOOKUP(B2,$A$1:$D1,4,0)+1,0))),"Not Known")
to:
=IFERROR(1/(1/(COUNTIF($C$2:$C$21,A2)+(COUNTIF($C$2:$C$21,B2)>0)*2+IFERROR(VLOOKUP(B2,$A$1:$D1,4,0)+1,0))),"Not Known")

Just a question on this, does it matter if my id's are alphanumeric? E.g "AA123456" Instead of what i gave in the dummny data "123"

Thanks

S