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]

which version of excel are you using?
also, do you have a sample to share?

 

[ShilP]

Hi ExceLoki

I am using Office 365

Here is a picture.

Thanks

 

[Fluff]

I am using Office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ShilP]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks Fluff. All done!

Hopefully someone somewhere comes up with a suggestion.

S

 

[ExceLoki]

might not be the prettiest solution possible, but it works. imagine someone else can clean it up or improve on it
----------------------

Book1
	A	B	C	D	E
1	person number	parent number	level 1 population	level number	Formula
2	123	124	124	2	2
3	159	112	112	2	2
4	165	111	111	2	2
5	124	987		1	1
6	112	987		1	1
7	111	987		1	1
8	235	123		3	3
9	236	159		3	3
10	237	165		3	3
11	546	787		not known	not found
12	545	787		not known	not found
13	544	789		not known	not found
14	458	235		4	4
15	457	236		4	4
16	456	237		4	4
Sheet1

Cell Formulas
Range	Formula
E2:E16	E2	=IFNA(IF(MATCH(A2,$C$2:$C$16,0),1),IFNA(IF(MATCH(B2,$C$2:$C$16,0),2),IFNA(IF(MATCH(A2,$B$2:$B$16,0),3),IFNA(IF(MATCH(B2,$A$2:$A$16,0),4),"not found"))))

 

[ShilP]

Thanks do you know if i can extend the IFNA's so that the level number can reach 8?

 

[ExceLoki]

should be able to. i saw you wanted 1-8 and not found in the post, but i wasnt sure what other comparisons you were looking at.

 

[ShilP]

Thats me rushing to create some dummy data! the list looks at 5000+ records and will throw back levels 1 to 8. Let me give this a try tomorrow and ill let you know.

Thanks

S

 

[ShilP]

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

might not be the prettiest solution possible, but it works. imagine someone else can clean it up or improve on it
----------------------

Book1
	A	B	C	D	E
1	person number	parent number	level 1 population	level number	Formula
2	123	124	124	2	2
3	159	112	112	2	2
4	165	111	111	2	2
5	124	987		1	1
6	112	987		1	1
7	111	987		1	1
8	235	123		3	3
9	236	159		3	3
10	237	165		3	3
11	546	787		not known	not found
12	545	787		not known	not found
13	544	789		not known	not found
14	458	235		4	4
15	457	236		4	4
16	456	237		4	4
Sheet1

Cell Formulas
Range	Formula
E2:E16	E2	=IFNA(IF(MATCH(A2,$C$2:$C$16,0),1),IFNA(IF(MATCH(B2,$C$2:$C$16,0),2),IFNA(IF(MATCH(A2,$B$2:$B$16,0),3),IFNA(IF(MATCH(B2,$A$2:$A$16,0),4),"not found"))))