If blank, find next matching data set and fill in the blanks

[CascadeDiver]

Hi all,
I have a dataset with three sections: NAME, ID, GROUP.
There are multiple occurrences of each name in each group, though not always.
The ID field only has data in the last occurrence of each name per group.
(Note that each name may not have the same ID in the next or any other group)

I am looking to fill in the missing IDs
Any help would be greatly appreciated. Thanks

Name	ID	Group	Missing ID
Bob		A
John		A
Kim		A
Layne		A
Bob		A
John		A
Kim		A
Bob		A
John		A
Kim		A
Layne		A
Bob	1	A
John	2	A
Kim	3	A
Layne	4	A
Bob		B
Bob		B
John		B
Bob		B
John		B
Bob	15	B
John	17	B
Bob		C
Sarah		C
Bob	4	C
John	7	C
Mark		C
Kim	5	C
Mark	8	C
Laura		C
Sarah	6	C
Laura	9	C

 

[Alex Blakenburg]

Will this work for you:

20241227 Fill Up Matching Value CascadeDiver.xlsx
	A	B	C	D
1	Name	ID	Group	Missing ID
2	Bob		A	1
3	John		A	2
4	Kim		A	3
5	Layne		A	4
6	Bob		A	1
7	John		A	2
8	Kim		A	3
9	Bob		A	1
10	John		A	2
11	Kim		A	3
12	Layne		A	4
13	Bob	1	A	1
14	John	2	A	2
15	Kim	3	A	3
16	Layne	4	A	4
17	Bob		B	15
18	Bob		B	15
19	John		B	17
20	Bob		B	15
21	John		B	17
22	Bob	15	B	15
23	John	17	B	17
24	Bob		C	4
25	Sarah		C	6
26	Bob	4	C	4
27	John	7	C	7
28	Mark		C	8
29	Kim	5	C	5
30	Mark	8	C	8
31	Laura		C	9
32	Sarah	6	C	6
33	Laura	9	C	9
Sheet1

Cell Formulas
Range	Formula
D2:D33	D2	=LOOKUP(2,1/(($A$2:$A$33=A2)*($C$2:$C$33=C2)),$B$2:$B$33)

 

[CascadeDiver]

That seems to retrieve some but not all the missing data. I am returning several 0s throughout.

 

[CascadeDiver]

I think I figured out a way that takes some sorting, helper columns, and VLOOKUP
Creating a helper column and making a numerical group to use instead of the letter group
Create a helper column to CONCAT numerical group and name
Sort ID small to large
Sort Numeric group small to large
Missing ID formula
=VLOOKUP([@CONCAT],[CONCAT]:[ID],2,FALSE)

then paste values

Name	CONCAT	ID	Group	Missing ID	Group num
Bob	Group001Bob	1	A	1	Group001
John	Group001John	2	A	2	Group001
Kim	Group001Kim	3	A	3	Group001
Layne	Group001Layne	4	A	4	Group001
Bob	Group001Bob	A	1	Group001
John	Group001John	A	2	Group001
Kim	Group001Kim	A	3	Group001
Layne	Group001Layne	A	4	Group001
Bob	Group001Bob	A	1	Group001
John	Group001John	A	2	Group001
Kim	Group001Kim	A	3	Group001
Bob	Group001Bob	A	1	Group001
John	Group001John	A	2	Group001
Kim	Group001Kim	A	3	Group001
Layne	Group001Layne	A	4	Group001
Bob	Group002Bob	15	B	15	Group002
John	Group002John	17	B	17	Group002
Bob	Group002Bob	B	15	Group002
Bob	Group002Bob	B	15	Group002
John	Group002John	B	17	Group002
Bob	Group002Bob	B	15	Group002
John	Group002John	B	17	Group002
Bob	Group003Bob	4	C	4	Group003
Kim	Group003Kim	5	C	5	Group003
Sarah	Group003Sarah	6	C	6	Group003
John	Group003John	7	C	7	Group003
Mark	Group003Mark	8	C	8	Group003
Laura	Group003Laura	9	C	9	Group003
Bob	Group003Bob	C	4	Group003
Sarah	Group003Sarah	C	6	Group003
Mark	Group003Mark	C	8	Group003
Laura	Group003Laura	C	9	Group003