Append row values to other rows based on certain Record ID

[ck2024]

Hi

This is filtered data with company names on the rows that proceed preceed the record ID, so Record ID 1 has Co 1, Record ID has Co 2, and both reside in Argentina, Co 3 through to Co 14, has Record ID 4 through to 14...etc etc

How can I populate the country name against all of these company names based on the record ID, so Record ID 1-2 would have Argentina in a new Country column,  Record ID 4 to 14 would have Australia in the Country column etc.  Struggling to get it right but it'll help automate something quite complex if I can figure this little bit out!! Thanks in advance   

RecordID	Tax Jurisdiction
3	ARGENTINA
15	AUSTRALIA
21	AUSTRIA
27	BRAZIL
31	BULGARIA
34	CANADA
40	CHILE
46	CHINA
50	COLOMBIA
53	COSTA RICA
55	DENMARK
61	FRANCE
72	GERMANY
76	HONG KONG
82	INDIA
84	INDONESIA
86	PHILIPPINES
93	IRELAND
96	ITALY
98	JAPAN
100	KOREA
102	MAYANMAR
108	MALAYSIA
110	MEXICO
118	MONACO
128	NETHERLANDS
130	NEW ZEALAND
135	NORWAY
139	PERU
141	POLAND
148	SINGAPORE
150	NAMIBIA
152	LESOTHO
155	UGANDA
157	BOTSWANA
159	MOZAMBIQUE
174	SOUTH AFRICA
176	SWITZERLAND
186	SPAIN
188	THAILAND
191	TURKEY
195	RUSSIA
285	UNITED KINGDOM
329	UNITED STATES
330	REPORT TOTALS

[Qiu]

@ck2024 
How does your unfiltered data look like?

[ck2024]

Hi @Qiu 

Thanks for opening my request, it looks like this, so I need the capitlised country to appear in a new column for the rows above until it hits already populated countries so Australia wouldn't overwrite the Argentina countries etc.

RecordID	Tax Jurisdiction
1	Company 1
2	Company 2
3	ARGENTINA
4	Company 3
5	Company 4
6	Company 5
7	Company 6
8	Company 7
9	Company 8
10	Company 9
11	Company 10
12	Company 11
13	Company 12
14	Company 13
15	AUSTRALIA
16	Company 14
17	Company 15
18	Company 16
19	Company 17
20	Company 18
21	AUSTRIA
22	Company 19
23	Company 20
24	Company 21
25	Company 22
26	Company 23
27	BRAZIL
28	Company 24
29	Company 25
30	Company 26
31	BULGARIA
32	Company 27
33	Company 28
34	CANADA
35	Company 29
36	Company 30
37	Company 31
38	Company 32
39	Company 33
40	CHILE
41	Company 34
42	Company 35
43	Company 36
44	Company 37
45	Company 38
46	CHINA
47	Company 1
48	Company 2
49	Company 3
50	COLOMBIA
51	Company 1
52	Company 2
53	Company 3
54	Company 4
55	Company 5
56	Company 6
57	Company 7
58	Company 8
59	Company 9
60	Company 10
61	Company 11
62	Company 12
63	Company 13
64	Company 14
65	Company 15
66	Company 16
67	Company 17
68	Company 18
69	Company 19
70	Company 20
71	Company 21
72	Company 22
73	Company 23
74	Company 24
75	Company 25
76	Company 26
77	Company 27
78	Company 28
79	Company 29
80	Company 30
81	Company 31
82	Company 32
83	Company 33
84	Company 34
85	Company 35
86	Company 36
87	Company 37
88	Company 38
89	Company 39
90	Company 40
91	Company 41
92	Company 42
93	Company 43
94	Company 44
95	Company 45
96	Company 46
97	Company 47
98	Company 48
99	Company 49
100	Company 50
101	Company 51
102	Company 52
103	Company 53
104	Company 54
105	Company 55
106	Company 56
107	Company 57
108	Company 58
109	Company 59
110	Company 60
111	Company 61
112	Company 62
113	Company 63
114	Company 64
115	Company 65
116	Company 66
117	Company 67
118	Company 68
119	Company 69
120	Company 70
121	Company 71
122	Company 72
123	Company 73
124	Company 74
125	Company 75
126	Company 76
127	Company 77
128	Company 78
129	Company 79
130	Company 80
131	Company 81
132	Company 82
133	Company 83
134	Company 84
135	Company 85
136	Company 86
137	Company 87
138	Company 88
139	Company 89
140	Company 90
141	Company 91
142	Company 92
143	Company 93
144	Company 94
145	Company 95
146	Company 96
147	Company 97
148	Company 98
149	Company 99
150	Company 100
151	Company 101
152	Company 102
153	Company 103
154	Company 104
155	Company 105
156	Company 106
157	BOTSWANA

[KarolinaRoza · Accepted solution]

Hi @ck2024,

If I understand your problem correctly you want to append to company names country which is displayed one row behind.

My suggestion is to :

- generate rows: for RecordID 1-3 : ARGENTINA, Record ID 4-14: AUSTRALIA,

- then you can join it with list of companies which have assigned RecordID.

I am not sure if you need records with COUNTRies I mean rows with records ID 4, 15 , 21 etc.

But this can be excluded easily (let me know if you need help).

Karolina

[ck2024]

Hi @KarolinaRoza 

You certainly did understand it correctly, thank you so much! This is what I was looking to achieve and had it in my mind the generate row maybe what i needed but just couldn't figure out the config!

So thanks again 🙂