Excel - Formula/VBA help

ShilP

New Member
Joined
Oct 8, 2015
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 13, 2021
Messages
151
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ShilP

New Member
Joined
Oct 8, 2015
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1Person NumberParent NumberLevel 1 PopulationLevel Number
21231241242
31591121122
41651111112
51249871
61129871
71119871
82351233
92361593
102371653
11546787Not Known
12545787Not Known
13544789Not Known
144582354
154572364
164562374
175954585
185964575
195974565
203115956
213125966
223135976
232113117
242123127
252133137
266012118
276022128
286032138
297826019
307886029
317816039
Sheet1



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

S
 

ExceLoki

Board Regular
Joined
Dec 13, 2021
Messages
151
Office Version
  1. 365
Platform
  1. Windows
that does help some. can you explain what are the conditions that would result in a 5, 6, 7, 8, 9?
 

ShilP

New Member
Joined
Oct 8, 2015
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2011
Messages
1,765
Office Version
  1. 2016
Below code could count up forever level:
Code:
=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

New Member
Joined
Oct 8, 2015
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Thanks Bebo, i gave this a try and it didn't seem to work I'
Below code could count up forever level:
Code:
=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

Board Regular
Joined
Dec 13, 2021
Messages
151
Office Version
  1. 365
Platform
  1. Windows
this is using @bebo021999 's response. it is working for me.
-----------------------
ifna-if-ifna-if matching in columns.xlsx
ABCD
1person numberparent numberlevel 1 populationshould be
21231241242
31591121122
41651111112
51249871
61129871
71119871
82351233
92361593
102371653
11546787Not Known
12545787Not Known
13544789Not Known
144582354
154572364
164562374
175954585
185964575
195974565
203115956
213125966
223135976
232113117
242123127
252133137
266012118
276022128
286032138
297826019
307886029
317816039
Sheet1 (2)
Cell Formulas
RangeFormula
D2:D31D2=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

New Member
Joined
Oct 8, 2015
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2011
Messages
1,765
Office Version
  1. 2016
Just extend the range C2:C4 to what far ever you need
 

ShilP

New Member
Joined
Oct 8, 2015
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,175,670
Messages
5,898,811
Members
434,731
Latest member
njakfla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top