Formula or VBA Advice to do a VLOOKUP

[VBA learner ITG]

Hi Experts,

Please can i get your help as me and a coleaugue cant fathom a formul or VBA to undertake this.

I have a challenge where i have a text data table which is like below with up to 1000 text values and i am tyrying to look up a text value and return the value 1 or 2 in the adjacent column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]PARTS AS PART OF A SINGLE JOB[/TD]
[TD]FOLDER[/TD]
[/TR]
[TR]
[TD][TABLE="width: 254"]
<tbody>[TR]
[TD="class: xl64, width: 254"]DUN249187 DUN249188 DUN249189 DUN249190 DUN249191 DUN249192 DUN249193 DUN249194 DUN249195 DUN249196 DUN249197 DUN249198 DUN249199 DUN249200 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 254"]
<tbody>[TR]
[TD="class: xl64, width: 254"]DMF133,DMF136,DMF137,DMF140,DMF141,DMF142,DMF143,DMF144,DMF148,DMH250,DMH690,DMH691,DMH692,DMH770,DMH771,DMP663,DMSC405,DMT1039,DMT1040,DMT1041,DMT1042,DMT1043,DMT1044,DMT1045,DMT1046,DMT1052,DMT1053,DMT1054,DMT1055,DMT1056,DMT1057,DMT1058,DMT563,DUN11436,DUN11437,DUN11438,DUN11439,DUN180759,DUN49098,DUN49099,DUN49100,DUN52646,DUN52647,DUN52648,DUN52649,DUN52650,DUN52651,DUN52652,DUN52653,[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I have a column with unique cells and this looks like below:


[TABLE="width: 500"]
<tbody>[TR]
[TD]UNIQUE CELL VALUE[/TD]
[TD]FOLDER[/TD]
[/TR]
[TR]
[TD]DUN249191 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DMF133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DMH691[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 138"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SteveO59L]

Re: Formula or VBA Advice to do a VLOOKUP (Not as easy as it sounds)

"return the value 1 or 2"

what determines whether to return 1 or 2?

 

[VBA learner ITG]

Re: Formula or VBA Advice to do a VLOOKUP (Not as easy as it sounds)

Apologies, When i refer to 1 or 2 this is a folder ID that is in the adjacent column of the cell value that contains the multiple values of the column "parts as part of a single job".

As these parts can appear in multiple folders this causes any formula we write to fail.

example workbook below

https://zackcarter.wetransfer.com/d...f542633d4b1d9705fc5e57b020171019092806/70ce0d

 

[VBA learner ITG]

Re: Formula or VBA Advice to do a VLOOKUP (Not as easy as it sounds)

A formula i have tried =VLOOKUP(INDEX('Job level raw data'!C:C,MATCH("*"&'Line level raw data'!A2&"*",'Job level raw data'!C:C,0)),'Job level raw data'!C:D,2,0)

but works for some and fails for others.

[FONT=&quot]If the part was repeated in several jobs and appeared in multiple folders, because the cell look up value is unique commodity and can appear within several folders.[/FONT]

[FONT=&quot] This wouldnt get captured in the formula as there are 500 odd folders the formula woule be astronomical to build and too complex if something to point too if something went wrong or the data is changed by another user.[/FONT]