reverse matching

[david763]

Good afternoon,
I've gone down rabbit hole to the point of overthinking...

I have a value "R" on sheet1.

I am looking for matching values of "R" in a table on another sheet (there may be more than one occurrence of "R" in that column). For each occurrence there will be an adjacent "O" value.
Now, each of those "O" values will apply to other "R" values other than the original "R" value.

I am after a string (textjoin) where the resultant "O" values in the first match reverse match one of the other associated "R" values which match yet another column value of "enterprise" (there may be more than one occurrence of these also). I want those "R" values in a string...

I do have the ability to add a helper column to the table if needed but prefer not if that's possible...

Hope this makes sense. Thanks for any help...

 

[Cubist]

Your description of your ask isn't clear. It'd be helpful if you could post a mock-up sample of your data and expected result (10-20 rows).

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

reverse matching [SOLVED]

Good afternoon, I've gone down rabbit hole to the point of overthinking... I have a value "R" on sheet1. I am looking for matching values of "R" in a table on another sheet (there may be more than one occurrence of "R" in that column). For each occurrence there will be an adjacent "O"...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[david763]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

reverse matching [SOLVED]

Good afternoon, I've gone down rabbit hole to the point of overthinking... I have a value "R" on sheet1. I am looking for matching values of "R" in a table on another sheet (there may be more than one occurrence of "R" in that column). For each occurrence there will be an adjacent "O"...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

sorry, I missed that... will remember in future...

 

[david763]

Your description of your ask isn't clear. It'd be helpful if you could post a mock-up sample of your data and expected result (10-20 rows).

Sorry, I can't seem to download xl2bb on my work computer. I have pasted data as text below if that works. If not, please don't bother with it.

Based on a 'Ref' value on adjacent sheet, my quest is to find a formula to manage the following:

Scenario 1:
Ref on other sheet = 1511.
This corresponds to Obj values 780 783 789 790 921 923 in my data
For EACH of those Obj values, I want to see if any subsequently LINK BACK to a Ref with type of 'enterprise'.
In this case, 780 links to an enterprise ref 4136, 783 to 4147, 789 to 4143, 790 to 4136 and 4144. Obj 921 and 923 do not have an equivalent 'enterprise' match.
I would like a string returned on original sheet "4136, 4147, 4143, 4136, 4144"

Scenario 2:
Ref on other sheet = 1507.
This corresponds to Obj value 786. There is no matching 'enterprise' value for that Obj.
Final string is blank.

I hope this makes sense..?


Ref Obj type
1498 780 basic
1498 789 basic
1507 786 basic
1511 780 basic
1511 783 basic
1511 789 basic
1511 790 basic
1511 921 basic
1511 923 basic
1523 789 basic
1537 923 basic
1538 786 basic
1571 789 basic
1574 780 basic
1950 780 basic
2008 789 basic
2009 780 basic
2010 780 basic
2012 780 basic
2014 921 basic
2016 780 basic
2017 780 basic
2017 790 basic
2027 780 basic
2029 780 basic
2029 790 basic
2038 780 basic
2039 789 basic
2041 780 basic
2059 786 basic
2060 923 basic
2063 780 basic
2072 780 basic
2073 789 basic
2076 780 basic
2077 783 basic
2081 783 basic
2091 780 basic
2091 789 basic
2091 790 basic
2123 780 basic
2123 789 basic
2124 780 basic
2124 789 basic
2125 789 basic
2126 780 basic
2126 783 basic
2127 780 basic
2127 790 basic
2137 780 basic
2137 789 basic
2137 923 basic
2138 786 basic
2138 789 basic
2139 780 basic
2139 790 basic
2139 923 basic
2140 780 basic
2140 923 basic
2372 780 basic
2373 789 basic
2375 780 basic
2376 780 basic
2377 790 basic
2378 783 basic
2379 783 basic
2381 786 basic
2382 780 basic
2622 780 basic
2623 780 basic
2629 786 basic
2793 780 basic
2793 789 basic
2793 921 basic
2794 790 basic
2870 783 basic
3000 786 basic
3669 923 basic
3760 780 basic
3825 790 basic
3826 790 basic
3827 780 basic
3828 790 basic
3834 786 basic
3835 923 basic
3887 923 basic
4042 786 basic
4136 780 enterprise
4136 790 enterprise
4138 786 enterprise
4143 789 enterprise
4144 790 enterprise
4147 783 enterprise

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F
1	Ref	Obj	Type		Ref	1511
2	1498	780	basic
3	1498	789	basic		Obj	Ref
4	1507	786	basic		780	4136
5	1511	780	basic		783	4147
6	1511	783	basic		789	4143
7	1511	789	basic		790	4136, 4144
8	1511	790	basic		921
9	1511	921	basic		923
10	1511	923	basic
11	1523	789	basic
12	1537	923	basic
13	1538	786	basic
14	1571	789	basic
15	1574	780	basic
16	1950	780	basic
17	2008	789	basic
18	2009	780	basic
19	2010	780	basic
20	2012	780	basic
21	2014	921	basic
22	2016	780	basic
23	2017	780	basic
24	2017	790	basic
25	2027	780	basic
26	2029	780	basic
27	2029	790	basic
28	2038	780	basic
29	2039	789	basic
30	2041	780	basic
31	2059	786	basic
32	2060	923	basic
33	2063	780	basic
34	2072	780	basic
35	2073	789	basic
36	2076	780	basic
37	2077	783	basic
38	2081	783	basic
39	2091	780	basic
40	2091	789	basic
41	2091	790	basic
42	2123	780	basic
43	2123	789	basic
44	2124	780	basic
45	2124	789	basic
46	2125	789	basic
47	2126	780	basic
48	2126	783	basic
49	2127	780	basic
50	2127	790	basic
51	2137	780	basic
52	2137	789	basic
53	2137	923	basic
54	2138	786	basic
55	2138	789	basic
56	2139	780	basic
57	2139	790	basic
58	2139	923	basic
59	2140	780	basic
60	2140	923	basic
61	2372	780	basic
62	2373	789	basic
63	2375	780	basic
64	2376	780	basic
65	2377	790	basic
66	2378	783	basic
67	2379	783	basic
68	2381	786	basic
69	2382	780	basic
70	2622	780	basic
71	2623	780	basic
72	2629	786	basic
73	2793	780	basic
74	2793	789	basic
75	2793	921	basic
76	2794	790	basic
77	2870	783	basic
78	3000	786	basic
79	3669	923	basic
80	3760	780	basic
81	3825	790	basic
82	3826	790	basic
83	3827	780	basic
84	3828	790	basic
85	3834	786	basic
86	3835	923	basic
87	3887	923	basic
88	4042	786	basic
89	4136	780	enterprise
90	4136	790	enterprise
91	4138	786	enterprise
92	4143	789	enterprise
93	4144	790	enterprise
94	4147	783	enterprise
Sheet2

Cell Formulas
Range	Formula
E4:F9	E4	=LET(a,A2:A94,b,B2:B94,c,C2:C94,obj,FILTER(b,(a=F1),""),HSTACK(obj,MAP(obj,LAMBDA(m,TEXTJOIN(", ",,FILTER(a,(b=m)*(c="enterprise"),""))))))
Dynamic array formulas.

 

[Eric W]

Another take:

Book1
	A	B	C	D	E	F
1	Ref	Obj	Type
2	1498	780	basic
3	1498	789	basic		Ref	Enterprise Refs
4	1507	786	basic		1511	4136, 4136, 4143, 4144, 4147
5	1511	780	basic		1507	4138
6	1511	783	basic
7	1511	789	basic
8	1511	790	basic
9	1511	921	basic
10	1511	923	basic
11	1523	789	basic
12	1537	923	basic
13	1538	786	basic
14	1571	789	basic
15	1574	780	basic
16	1950	780	basic
17	2008	789	basic
18	2009	780	basic
19	2010	780	basic
20	2012	780	basic
21	2014	921	basic
22	2016	780	basic
23	2017	780	basic
24	2017	790	basic
25	2027	780	basic
26	2029	780	basic
27	2029	790	basic
28	2038	780	basic
29	2039	789	basic
30	2041	780	basic
31	2059	786	basic
32	2060	923	basic
33	2063	780	basic
34	2072	780	basic
35	2073	789	basic
36	2076	780	basic
37	2077	783	basic
38	2081	783	basic
39	2091	780	basic
40	2091	789	basic
41	2091	790	basic
42	2123	780	basic
43	2123	789	basic
44	2124	780	basic
45	2124	789	basic
46	2125	789	basic
47	2126	780	basic
48	2126	783	basic
49	2127	780	basic
50	2127	790	basic
51	2137	780	basic
52	2137	789	basic
53	2137	923	basic
54	2138	786	basic
55	2138	789	basic
56	2139	780	basic
57	2139	790	basic
58	2139	923	basic
59	2140	780	basic
60	2140	923	basic
61	2372	780	basic
62	2373	789	basic
63	2375	780	basic
64	2376	780	basic
65	2377	790	basic
66	2378	783	basic
67	2379	783	basic
68	2381	786	basic
69	2382	780	basic
70	2622	780	basic
71	2623	780	basic
72	2629	786	basic
73	2793	780	basic
74	2793	789	basic
75	2793	921	basic
76	2794	790	basic
77	2870	783	basic
78	3000	786	basic
79	3669	923	basic
80	3760	780	basic
81	3825	790	basic
82	3826	790	basic
83	3827	780	basic
84	3828	790	basic
85	3834	786	basic
86	3835	923	basic
87	3887	923	basic
88	4042	786	basic
89	4136	780	enterprise
90	4136	790	enterprise
91	4138	786	enterprise
92	4143	789	enterprise
93	4144	790	enterprise
94	4147	783	enterprise
Sheet6

Cell Formulas
Range	Formula
F4:F5	F4	=LET(a,$A$2:$A$94,b,$B$2:$B$94,c,$C$2:$C$94,TEXTJOIN(", ",1,FILTER(a,ISNUMBER(MATCH(b,FILTER(b,a=E4,""),0))*(c="enterprise"),"")))

 

[david763]

Thank you Eric. This works perfectly . I just need to unpick it in my own brain to follow the logic...