VBA - Assign a Code at Level Changes Based on Criteria

[xtiinctt]

Hello all!

Hoping that someone could help me or even tell me if this is possible! First time poster but have been looking at posts in the forum for a long time!

Column B - I have levels that have a code assigned to them based on column E, and column G are my expected results.
If the level changes and has a code in column E then the proceeding level values will be that value, but if the level goes down to 3 in this case, we want the code from when the value was a 3 or if a new code is assigned in like in column E18 use that until the level goes back down.

Columns C, D, and F don't play a part in this scenario.

I was looking at recursive solutions but that is something I have never used, so out of my wheelhouse!

thank you all for your time!

 

[Eric W]

Welcome to the MrExcel forum!

As far as I can tell, no recursion needed. Try:

Sub AssignCode()
Dim dic As Object, Level As Variant, Code As Variant, Expected As Variant
Dim i As Long, LastLevel As Long

    Level = Range("B2", Range("B2").End(xlDown)).Value
    Code = Range("E2").Resize(UBound(Level)).Value
    Expected = Range("G2").Resize(UBound(Level)).Value
    
    Set dic = CreateObject("Scripting.Dictionary")
    LastLevel = -1
    For i = 1 To UBound(Level)
        If Level(i, 1) <> LastLevel Then
            If Code(i, 1) <> "" Then
                dic(Level(i, 1)) = Code(i, 1)
            Else
                If Not dic.exists(Level(i, 1)) Then dic(Level(i, 1)) = Expected(i - 1, 1)
            End If
        End If
        Expected(i, 1) = dic(Level(i, 1))
        LastLevel = Level(i, 1)
    Next i
    
    Range("G2").Resize(UBound(Level)) = Expected
                    
End Sub

This code creates the expected results, except for the last line (index 42). You have the expected value as c, but the level did not change, so the macro did not change the code. Do you want the code to change if the code in column E changes, even if the level doesn't?

 

[xtiinctt]

Welcome to the MrExcel forum!

As far as I can tell, no recursion needed. Try:

Sub AssignCode()
Dim dic As Object, Level As Variant, Code As Variant, Expected As Variant
Dim i As Long, LastLevel As Long

    Level = Range("B2", Range("B2").End(xlDown)).Value
    Code = Range("E2").Resize(UBound(Level)).Value
    Expected = Range("G2").Resize(UBound(Level)).Value
   
    Set dic = CreateObject("Scripting.Dictionary")
    LastLevel = -1
    For i = 1 To UBound(Level)
        If Level(i, 1) <> LastLevel Then
            If Code(i, 1) <> "" Then
                dic(Level(i, 1)) = Code(i, 1)
            Else
                If Not dic.exists(Level(i, 1)) Then dic(Level(i, 1)) = Expected(i - 1, 1)
            End If
        End If
        Expected(i, 1) = dic(Level(i, 1))
        LastLevel = Level(i, 1)
    Next i
   
    Range("G2").Resize(UBound(Level)) = Expected
                   
End Sub

This code creates the expected results, except for the last line (index 42). You have the expected value as c, but the level did not change, so the macro did not change the code. Do you want the code to change if the code in column E changes, even if the level doesn't?

Hey Eric!

Thank you for spending time reviewing my post! that is correct, I want the code to change even if the level doesn't because there could be an instance where the code is "e" for 5 rows on level 4, but then change to "f" on the same level and I would expect "f" to be the expected results. I will test this out tomorrow morning against my data set and let you know how it goes!

 

[xtiinctt]

Hey Eric!

Thank you for spending time reviewing my post! that is correct, I want the code to change even if the level doesn't because there could be an instance where the code is "e" for 5 rows on level 4, but then change to "f" on the same level and I would expect "f" to be the expected results. I will test this out tomorrow morning against my data set and let you know how it goes!

Sorry for the additional response but tested the logic out, and the "expected" column is what I would expect the VBA to return and is not a part of the original dataset. Sorry I did not make that clear in my original post. Please let me know if I can provide any additional data

 

[Eric W]

Try this version:

Sub AssignCode()
Dim dic As Object, Level As Variant, Code As Variant, Expected As Variant
Dim i As Long, LastLevel As Long

    Level = Range("B2", Range("B2").End(xlDown)).Value
    Code = Range("E2").Resize(UBound(Level)).Value
    Expected = Range("G2").Resize(UBound(Level)).Value
    
    Set dic = CreateObject("Scripting.Dictionary")
    LastLevel = -1
    For i = 1 To UBound(Level)
        If Code(i, 1) <> "" Then dic(Level(i, 1)) = Code(i, 1)
        If Level(i, 1) <> LastLevel Then
            If Not dic.exists(Level(i, 1)) Then dic(Level(i, 1)) = Expected(i - 1, 1)
        End If
        Expected(i, 1) = dic(Level(i, 1))
        LastLevel = Level(i, 1)
    Next i
    
    Range("G2").Resize(UBound(Level)) = Expected
                    
End Sub

Make sure to change the cell references to match your sheet.

 

[xtiinctt]

Hey Eric,

I was able to create some more data to test against and added some additional comments for clarity. First thing, I get a error at line "Then dic(Level(i, 1)) = Expected(i - 1, 1)" - Subscript out of range. I did adjust my ranges, but the "expected" column is what I want the macro to return and is not a part of the original data.

Here are some additional notes that might be helpful:

The code gets applied from the top down, which I think you are super close! I think there is another if statement possibly needed? either way, so if level 5 has a code, only the level 6 or lower would get that code.
Meaning..
level 4 - code "E" gets applied down unless it runs into another code on level 5 say "F" then that gets applied down.
if no code on level 5 or additional levels inherit level 4

hopefully this clears up any confusion, and again thank you for your assistance!

Book1.xlsx
	A	B	C
1	Index	Level	Code
2	1	0
3	2	1
4	3	1
5	4	2	a
6	5	2	a
7	6	2	a
8	7	0	b
9	8	1
10	9	1
11	10	1
12	11	1
13	12	1
14	13	1
15	14	1
16	15	1
17	16	1
18	17	1
19	18	1
20	19	1
21	20	1
22	21	1
23	22	1
24	23	1
25	24	1
26	25	1
27	26	0	b
28	27	1
29	28	1
30	29	1
31	30	1	d
32	31	1
33	32	1
34	33	1
35	34	0
36	35	1
37	36	1
38	37	1
39	38	1
40	39	1
41	40	1	e
42	41	2
43	42	3	a
44	43	3	a
45	44	3	a
46	45	3
47	46	3
48	47	3
49	48	3
50	49	3
51	50	3
52	51	3
53	52	3
54	53	3
55	54	3
56	55	3
57	56	3
58	57	3
59	58	3
60	59	3
61	60	3
62	61	3
63	62	3
64	63	3
65	64	3
66	65	3
67	66	3
68	67	3
69	68	3
70	69	3
71	70	3
72	71	3
73	72	3
74	73	3
75	74	3	a
76	75	3	a
77	76	3	a
78	77	3	a
79	78	3	a
80	79	3
81	80	3
82	81	3
83	82	3
84	83	3
85	84	2
86	85	2
87	86	3
88	87	3
89	88	3
90	89	3
91	90	2
92	91	2
93	92	2
94	93	2
95	94	2
96	95	1
97	96	2
98	97	2
99	98	2
100	99	3
101	100	3
102	101	3
103	102	3
104	103	3
105	104	3
106	105	3
107	106	3
108	107	3
109	108	3
110	109	3
111	110	3
112	111	3
113	112	3
114	113	3
115	114	3
116	115	3
117	116	3
118	117	3
119	118	3
120	119	3
121	120	3
122	121	3
123	122	3
124	123	3
125	124	3
126	125	2
127	126	2
128	127	3
129	128	2
130	129	2
131	130	2
132	131	2
133	132	2
134	133	2
135	134	1	f
136	135	2
137	136	2
138	137	2
139	138	2
140	139	2
141	140	2
142	141	2
143	142	2
144	143	2	f
145	144	3
146	145	3
147	146	3
148	147	4	b
149	148	4
150	149	4
151	150	4
152	151	4
153	152	4
154	153	4
155	154	4
156	155	4
157	156	4
158	157	4
159	158	4
160	159	4
161	160	4
162	161	4
163	162	4
164	163	4
165	164	4
166	165	3
167	166	4
168	167	4
169	168	4
170	169	4
171	170	4
172	171	4
173	172	4
174	173	4
175	174	4
176	175	3
177	176	4
178	177	4
179	178	4
180	179	4
181	180	4
182	181	4
183	182	4
184	183	4
185	184	4
186	185	3	f
187	186	4	a
188	187	4
189	188	4
190	189	4
191	190	4
192	191	4
193	192	4
194	193	4
195	194	4
196	195	4
197	196	4
198	197	4
199	198	4
200	199	4	g
201	200	4
202	201	4
203	202	4
204	203	4
205	204	4	h
206	205	5	a
207	206	5	a
208	207	5	a
209	208	5
210	209	5
211	210	5
212	211	6
213	212	5
214	213	5	a
215	214	5
216	215	6
217	216	6
218	217	7
219	218	7
220	219	7
221	220	8
222	221	7
223	222	7
224	223	7
225	224	7
226	225	7
227	226	7
228	227	7
229	228	7
230	229	6
231	230	6
232	231	6
233	232	6
234	233	6
235	234	6
236	235	5
237	236	5
238	237	5
239	238	5	a
240	239	5
241	240	5
242	241	5
243	242	5
244	243	5
245	244	5
246	245	5
247	246	5
248	247	5
249	248	5
250	249	5
251	250	5
252	251	5
253	252	5
254	253	4	i
255	254	4	f
256	255	4
257	256	4
258	257	4
259	258	4
260	259	4
261	260	4
262	261	4
263	262	4
264	263	4
265	264	4
266	265	4
267	266	4
268	267	4
269	268	4
270	269	4
271	270	4
272	271	4
273	272	4
274	273	4
275	274	4
276	275	4
277	276	4
278	277	4
279	278	4
280	279	4
281	280	4
282	281	4
283	282	4
284	283	4
285	284	4
286	285	4
287	286	3	f
288	287	4
289	288	4
290	289	4
291	290	4
292	291	4
293	292	4
294	293	4
295	294	4
296	295	4
297	296	4
298	297	4
299	298	4
300	299	4
301	300	4
302	301	4
303	302	4
304	303	4
305	304	4
306	305	4
307	306	4
308	307	5	i
309	308	4
310	309	4
311	310	4
312	311	4	g
313	312	5
314	313	5
315	314	5
316	315	5
317	316	5
318	317	6
319	318	7
320	319	8
321	320	7
322	321	6
323	322	7
324	323	5
325	324	5	j
326	325	5
327	326	5
328	327	5
329	328	5
330	329	5
331	330	4
332	331	4
333	332	4
334	333	4
335	334	4
336	335	4
337	336	4
338	337	3	f
339	338	4
340	339	4
341	340	4
342	341	4
343	342	4
344	343	4
345	344	4
346	345	4
347	346	4
348	347	4
349	348	4
350	349	4
351	350	4
352	351	3	k
353	352	4
354	353	4
355	354	4
356	355	4
357	356	4
358	357	4
359	358	4
360	359	4
361	360	4
362	361	4
363	362	4
364	363	4	l
365	364	5	a
366	365	5	a
367	366	5	a
368	367	5
369	368	5
370	369	5
371	370	5
372	371	5
373	372	5
374	373	6
375	374	6
376	375	6
377	376	6
378	377	6
379	378	6
380	379	6
381	380	6
382	381	6	a
383	382	6
384	383	6
385	384	6
386	385	6
387	386	6
388	387	6
389	388	7
390	389	7
391	390	7
392	391	8
393	392	8
394	393	8
395	394	8
396	395	8
397	396	8
398	397	8
399	398	7
400	399	7
401	400	7
402	401	7
403	402	7
404	403	7
405	404	7
406	405	7
407	406	7
408	407	7
409	408	7
410	409	7
411	410	7
412	411	7
413	412	7
414	413	7
415	414	7
416	415	7
417	416	7
418	417	7
419	418	7
420	419	7
421	420	7
422	421	7
423	422	7
424	423	7
425	424	7
426	425	7
427	426	6
428	427	6
429	428	6
430	429	6
431	430	6
432	431	6
433	432	6
434	433	6
435	434	6
436	435	6
437	436	6
438	437	6
439	438	6
440	439	6
441	440	6
442	441	6
443	442	6
444	443	6
445	444	6
446	445	6
447	446	6
448	447	6
449	448	6
450	449	6
451	450	6
452	451	6
453	452	6
454	453	6
455	454	6
456	455	6
457	456	6
458	457	6
459	458	6
460	459	6
461	460	6
462	461	6
463	462	6
464	463	6
465	464	6
466	465	6
467	466	6
468	467	6
469	468	6
470	469	6
471	470	6
472	471	6
473	472	6
474	473	6
475	474	6
476	475	6
477	476	6
478	477	6
479	478	6
480	479	6
481	480	6
482	481	6
483	482	6
484	483	5
485	484	5	a
486	485	5
487	486	5
488	487	5
489	488	5
490	489	5
491	490	5
492	491	5
493	492	5
494	493	5
495	494	5
496	495	5
497	496	5
498	497	5
499	498	5
500	499	4	i
501	500	4	f
502	501	4
503	502	4
504	503	4
505	504	4
506	505	4	m
507	506	5
508	507	4
509	508	4
510	509	4
511	510	4	n
512	511	5
513	512	6
514	513	6
515	514	7
516	515	8
517	516	7
518	517	7
519	518	6
520	519	7
521	520	6
522	521	6
523	522	6
524	523	6
525	524	6
526	525	6
527	526	6
528	527	6
529	528	6
530	529	6
531	530	6
532	531	6
533	532	6
534	533	6
535	534	6
536	535	6
537	536	6
538	537	5
539	538	6
540	539	6
541	540	6
542	541	7
543	542	6
544	543	6
545	544	6
546	545	6
547	546	6
548	547	6
549	548	6
550	549	6
551	550	6
552	551	6
553	552	6
554	553	6
555	554	6
556	555	6
557	556	7
558	557	7
559	558	7
560	559	7
561	560	6
562	561	7
563	562	6
564	563	7
565	564	6
566	565	6
567	566	6
568	567	6
569	568	5
570	569	5
571	570	5
572	571	5
573	572	5
574	573	5
575	574	5
576	575	5
577	576	4
578	577	4
579	578	4
580	579	4
581	580	4
582	581	4
583	582	4
584	583	4
585	584	4
586	585	4
587	586	4
588	587	4
589	588	4
590	589	4
591	590	4
592	591	4
593	592	3	g
594	593	4
595	594	4
596	595	4
597	596	4
598	597	4
599	598	4
600	599	4
601	600	4
602	601	4
603	602	4
604	603	4
605	604	4
606	605	4
607	606	4
608	607	4
609	608	4
610	609	4
611	610	4
612	611	4
613	612	4
614	613	4
615	614	4
616	615	4
617	616	4
618	617	5
619	618	5
620	619	5
621	620	5
622	621	5
623	622	4
624	623	4
625	624	4
626	625	4
627	626	5
628	627	5
629	628	5
630	629	5
631	630	5
632	631	5
633	632	5
634	633	5
635	634	5
636	635	5
637	636	4
638	637	5	a
639	638	5	a
640	639	5	a
641	640	4
642	641	5
643	642	5
644	643	5
645	644	5
646	645	5
647	646	5
648	647	6
649	648	4	g
650	649	5
651	650	5
652	651	6
653	652	7
654	653	6
655	654	7
656	655	5
657	656	5
658	657	5
659	658	4
660	659	4
661	660	4
662	661	4
663	662	4
664	663	4
665	664	4
666	665	4
667	666	5	a
668	667	5	a
669	668	5
670	669	6
671	670	6
672	671	6
673	672	6
674	673	7
675	674	7
676	675	7
677	676	6
678	677	6
679	678	6
680	679	6
681	680	7
682	681	6
683	682	7
684	683	5
685	684	6	a
686	685	6	a
687	686	6	a
688	687	6	a
689	688	6	a
690	689	6	a
691	690	6	a
692	691	6	a
693	692	6	a
694	693	6	a
695	694	6	a
696	695	6	a
697	696	6	a
698	697	6	a
699	698	6	a
700	699	6	a
701	700	6	a
702	701	6	a
703	702	6	a
704	703	6	a
705	704	7	a
706	705	6
707	706	6
708	707	6
709	708	7	a
710	709	7	a
711	710	8	a
712	711	6
713	712	7
714	713	8
715	714	9
716	715	10	a
717	716	10	a
718	717	11	a
719	718	10	a
720	719	9
721	720	10	a
722	721	10	a
723	722	8
724	723	9
725	724	10	a
726	725	9
727	726	10	a
728	727	10	a
729	728	11	a
730	729	10	a
731	730	6	a
732	731	7	a
733	732	6	a
734	733	6
735	734	6
736	735	6	a
737	736	6	a
738	737	6	a
739	738	6	a
740	739	6	a
741	740	6	a
Sheet1

 

[Eric W]

OK, some clarity, but I have some questions. The error line you got was because you don't have a code in C2 in your example. My macro tries to look up the code, but if it doesn't have a code for that level, it tries to take it from the previous line. So if there is nothing on the first row, you'll get an error. So what do you want in the Results column if there's nothing on the first row, and there's nothing to inherit?

Also, your explanation of how higher levels inherit the code of lower levels makes me rethink my approach. For example, for Index 6, code a is assigned to level 2. Down to index 40, code e is assigned to level 1. The next row is level 2. Should the result be a because it was assigned to level 2, or should it be e because it inherits the e from level 1?