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

 

[bebo021999]

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"

Surely it does.
Will came back when I am at PC.

 

[ShilP]

Surely it does.
Will came back when I am at PC.

Thanks Bebo

Appreciate the help on this.

Regards

S

 

[ShilP]

Hi All

Does anyone have any solutions for the above? just seeing if i can improve this problem im having with the formula.

=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")

Thanks

S

 

[ShilP]

Hi All

Does anyone else have any suggestions i could try here?

Thanks

S

 

[bebo021999]

Could you upload a new mini sheet again via XL2BB, with newest formula and new id update ?

 

[ShilP]

Thanks Bebo

Here it is now the ID's are alphanumeric and not just numbers. The green column is the correct outcome. The column "Level Number is where i would to place a formula":

PersonParent.xlsx
	A	B	C	D	E	F
1	Person Number	Parent Number	Level 1 Population	Level Number		Level Number
2	AA123	AA124	AA124			2
3	AA159	AA112	AA112			2
4	AA165	AA111	AA111			2
5	AA124	AA987				1
6	AA112	AA987				1
7	AA111	AA987				1
8	AA235	AA123				3
9	AA236	AA159				3
10	AA237	AA165				3
11	AA546	AA787				Not Known
12	AA545	AA787				Not Known
13	AA544	AA789				Not Known
14	AA458	AA235				4
15	AA457	AA236				4
16	AA456	AA237				4
17	AA595	AA458				5
18	AA596	AA457				5
19	AA597	AA456				5
20	AA311	AA595				6
21	AA312	AA596				6
22	AA313	AA597				6
23	AA211	AA311				7
24	AA212	AA312				7
25	AA213	AA313				7
26	AA601	AA211				8
27	AA602	AA212				8
28	AA603	AA213				8
29	AA782	AA601				9
30	AA788	AA602				9
31	AA781	AA603				9
Sheet1

 

[ExceLoki]

using @bebo021999 original solution works for me using the recently added mini sheet. but i did find an issue once you add more data to column c, where the values can go over 9 as shown below. is this the issue you're having?

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

-----------------------------

ifna-if-ifna-if matching in columns.xlsx
	A	B	C	D
1	Person Number	Parent Number	Level 1 Population	bebo
2	AA123	AA124	AA124	2
3	AA159	AA112	AA112	2
4	AA165	AA111	AA111	2
5	AA124	AA987		1
6	AA112	AA987		1
7	AA111	AA987		1
8	AA235	AA123		3
9	AA236	AA159		3
10	AA237	AA165		3
11	AA546	AA787		Not Known
12	AA545	AA787		Not Known
13	AA544	AA789		Not Known
14	AA458	AA235		4
15	AA457	AA236		4
16	AA456	AA237		5
17	AA595	AA458	aa456	5
18	AA596	AA457		5
19	AA597	AA456		8
20	AA311	AA595		6
21	AA312	AA596		6
22	AA313	AA597		9
23	AA211	AA311		7
24	AA212	AA312		7
25	AA213	AA313		10
26	AA601	AA211		8
27	AA602	AA212		8
28	AA603	AA213		11
29	AA782	AA601		9
30	AA788	AA602		9
31	AA781	AA603		12
Sheet2

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

 

[ShilP]

using @bebo021999 original solution works for me using the recently added mini sheet. but i did find an issue once you add more data to column c, where the values can go over 9 as shown below. is this the issue you're having?

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

-----------------------------

ifna-if-ifna-if matching in columns.xlsx
	A	B	C	D
1	Person Number	Parent Number	Level 1 Population	bebo
2	AA123	AA124	AA124	2
3	AA159	AA112	AA112	2
4	AA165	AA111	AA111	2
5	AA124	AA987		1
6	AA112	AA987		1
7	AA111	AA987		1
8	AA235	AA123		3
9	AA236	AA159		3
10	AA237	AA165		3
11	AA546	AA787		Not Known
12	AA545	AA787		Not Known
13	AA544	AA789		Not Known
14	AA458	AA235		4
15	AA457	AA236		4
16	AA456	AA237		5
17	AA595	AA458	aa456	5
18	AA596	AA457		5
19	AA597	AA456		8
20	AA311	AA595		6
21	AA312	AA596		6
22	AA313	AA597		9
23	AA211	AA311		7
24	AA212	AA312		7
25	AA213	AA313		10
26	AA601	AA211		8
27	AA602	AA212		8
28	AA603	AA213		11
29	AA782	AA601		9
30	AA788	AA602		9
31	AA781	AA603		12
Sheet2

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

Thanks Excel Loki.

Yes thats correct, the population in Column C can grow up to 40 different numbers. As total sheet can contain around 6000 total records. I feel like we are nearly there but struggling to get it working.

Does anyone have any ideas?