Data Selection Based on Values

[Anonnymouse]

Hello Excel Wizards,

I am trying to see if its possible to do this in excel.

In column C:G I have the original data set and in Column J:M i have a data set of measured values.

I am wondering if its possible to get in column Q the closest values from K:M that are similar to the original data set C:G.

Example C2=Brick One, B2=Value 1 E2=100, F2=200, G2=300
In columns J:M we have 6 examples of values with the name Brick are very similar. So It should then pull that information and put it on O:Q saying there is 6 instances where it is similar or matches.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Identifier	Sub-Ident	Value 1	Value 2	Value 3			Identifier	Value 1	Value 2	Value 3		Identifier	Sub-Ident	Which Value is closest
2			Brick One	Value 1	100	200	300			Brick One	105	205	305		Brick One	Value 1	6
3			Brick One	Value 2	150	250	350			98331	450	650	750		Brick One	Value 2	7
4			Brick Two	Value 1	100	240	500			Brick One	115	215	313		Brick Two	Value 1	1
5			98331	Value 1	200	300	400			Brick One	161	267	358		98331	Value 1	1
6			98331	Value 2	50	60	70			Brick One	105	205	305		98331	Value 2	1
7			98331	Value 3	400	600	700			Brick two	100	240	500		98331	Value 3	2
8										Brick One	115	215	313
9										98331	205	305	405
10										98331	55	65	75
11										Brick One	155	255	355
12										Brick One	115	215	313
13										Brick One	161	267	358
14										Brick One	161	267	358
15										Brick One	155	255	355
16										Brick One	155	255	355
17										Brick One	161	267	358
18										Brick One	105	205	305
19										98331	400	600	700
Sheet1

 

[Eric W]

This is a case where you have to define what "similar" means. I took the approach that each value must be within 20% of the reference values. This can be changed in the R2 cell. This matches your expected results.

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			Identifier	Sub-Ident	Value 1	Value 2	Value 3			Identifier	Value 1	Value 2	Value 3		Identifier	Sub-Ident	Which Value is closest	Tolerance
2			Brick One	Value 1	100	200	300			Brick One	105	205	305		Brick One	Value 1	6	20%
3			Brick One	Value 2	150	250	350			98331	450	650	750		Brick One	Value 2	7
4			Brick Two	Value 1	100	240	500			Brick One	115	215	313		Brick Two	Value 1	1
5			98331	Value 1	200	300	400			Brick One	161	267	358		98331	Value 1	1
6			98331	Value 2	50	60	70			Brick One	105	205	305		98331	Value 2	1
7			98331	Value 3	400	600	700			Brick two	100	240	500		98331	Value 3	2
8										Brick One	115	215	313
9										98331	205	305	405
10										98331	55	65	75
11										Brick One	155	255	355
12										Brick One	115	215	313
13										Brick One	161	267	358
14										Brick One	161	267	358
15										Brick One	155	255	355
16										Brick One	155	255	355
17										Brick One	161	267	358
18										Brick One	105	205	305
19										98331	400	600	700
Sheet1

Cell Formulas
Range	Formula
Q2:Q7	Q2	=LET(r,FILTER($E$2:$G$7,($C$2:$C$7=O2)*($D$2:$D$7=P2)),v_1,INDEX(r,1),v_2,INDEX(r,2),v_3,INDEX(r,3),ROWS(FILTER($J$2:$J$19,($J$2:$J$19=O2)*($K$2:$K$19/v_1<(1+$R$2))*($L$2:$L$19/v_2<(1+$R$2))*($M$2:$M$19/v_3<(1+$R$2))*(v_1/$K$2:$K$19<(1+$R$2))*(v_2/$L$2:$L$19<(1+$R$2))*(v_3/$M$2:$M$19<(1+$R$2)))))

 

[Anonnymouse]

Hey Eric,

I was running into the issue where it wasn't showing the correct value because I forgot to put the % in cell R2.

Your solution worked. Thank you for the help!

 

[Eric W]

I was playing around with some other approaches, but I'm glad this worked for you!

 

[Anonnymouse]

I was playing around with some other approaches, but I'm glad this worked for you!

Hey Eric

Sorry not sure if I need to make another post but I inserted 5000 rows of data and now it gives a #calc.

Do you know if there is a data restriction on how much it can calculate?

 

[Eric W]

Does the #CALC only appear on some lines? The CALC# will appear if there are no "similar-enough" matches for a particular identifier/sub-identifier. You can try playing around with the tolerance value to see if that will work for you, 25%, 40%, whatever. There shouldn't be a restriction on how much data is allowed. It's possible that some rows in your J:M table won't get counted because they're not "similar-enough" to any of the reference values.

Another approach I considered was to count every row in the J:M table by adding it to the "closest" reference value. Again, this depends on how to define "close". Possibly by using the distance formula: SQRT((x1-x2)^2+(y1-y2)^2+(z1-z2)^2). There are other ways, I don't have enough context to really say. Let me know if you want to see that.

 

[Anonnymouse]

Does the #CALC only appear on some lines? The CALC# will appear if there are no "similar-enough" matches for a particular identifier/sub-identifier. You can try playing around with the tolerance value to see if that will work for you, 25%, 40%, whatever. There shouldn't be a restriction on how much data is allowed. It's possible that some rows in your J:M table won't get counted because they're not "similar-enough" to any of the reference values.

Another approach I considered was to count every row in the J:M table by adding it to the "closest" reference value. Again, this depends on how to define "close". Possibly by using the distance formula: SQRT((x1-x2)^2+(y1-y2)^2+(z1-z2)^2). There are other ways, I don't have enough context to really say. Let me know if you want to see that.

This is still giving me errors, I have attached a bigger data set if this helps.

TestFileV2.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Item Number	Sub-Item	Length	Width	Height			Identifer	Value 1	Value 2	Value 3		Item Number	Sub-Item	Closest	Tolerance
2	8125693	1	375	335	260			8125637	624	345	254		8125693	1	#DIV/0!	90%
3	8125693	2	375	260	335			8125945	386	342	260		8125693	2	#DIV/0!
4	8125924	1	375	335	260			8125945	386	345	257		8125924	1	#DIV/0!
5	8125924	3	375	335	260			8125945	386	337	258		8125924	3	#DIV/0!
6	8125945	1	375	335	260			8125945	384	340	261		8125945	1	#DIV/0!
7	8125945	2	375	260	335			8125945	389	335	260		8125945	2	#DIV/0!
8	9110283	1	400	335	230			8125945	396	337	258		9110283	1	#DIV/0!
9	9110283	2	380	330	230			8125945	386	350	260		9110283	2	#DIV/0!
10	9129711	1	365	185	105			8125945	384	337	259		9129711	1	#DIV/0!
11	9129711	4	370	195	110			8125945	384	337	258		9129711	4	#DIV/0!
12	9137730	1	465	200	145			9103082	518	265	275		9137730	1	#DIV/0!
13	9137730	2	464	200	140			9103082	514	265	275		9137730	2	#DIV/0!
14	9155182	1	345	235	220			9103082	516	267	276		9155182	1	#DIV/0!
15	9155182	2	335	225	204			9103755	511	240	286		9155182	2	#DIV/0!
16	9161868	1	360	185	200			9103755	509	240	296		9161868	1	#DIV/0!
17	9161868	2	365	190	214			9106144	360	182	105		9161868	2	#DIV/0!
18	9166901	1	400	300	245			9108890	367	305	127		9166901	1	#DIV/0!
19	9166901	2	380	240	283			9108981	480	280	222		9166901	2	#DIV/0!
20	9166915	1	380	235	235			9108981	473	282	217		9166915	1	#DIV/0!
21	9166915	2	380	240	252			9110283	410	332	246		9166915	2	#DIV/0!
22	9166915	3	380	240	252			9110283	406	332	251		9166915	3	#DIV/0!
23	9352374	1	395	295	235			9110283	398	332	254		9352374	1	#DIV/0!
24	9352374	2	370	340	213			9110283	403	330	252		9352374	2	#DIV/0!
25	9352381	1	610	408	247			9110283	403	332	253		9352381	1	#DIV/0!
26	9352381	2	520	320	351			9110283	406	330	254		9352381	2	#DIV/0!
27	9356819	1	500	325	245			9110283	408	332	246		9356819	1	#DIV/0!
28	9356819	2	510	340	234			9110283	401	335	249		9356819	2	#DIV/0!
29	9369680	1	500	405	300			9110283	408	332	247		9369680	1	#DIV/0!
30	9369680	2	499	345	306			9110283	401	332	239		9369680	2	#DIV/0!
31	9369694	1	500	405	300			9110283	415	330	247		9369694	1	#DIV/0!
32	9369694	2	505	345	305			9111776	444	330	205		9369694	2	#DIV/0!
33	9377336	1	380	335	214			9111776	439	335	204		9377336	1	#DIV/0!
34	9377336	2	398	285	235			9111776	468	307	204		9377336	2	#DIV/0!
35	9738429	1	415	340	70			9111776	442	330	209		9738429	1	#DIV/0!
36	9738429	2	410	338	69			9111783	437	300	302		9738429	2	#DIV/0!
37	550516738	1	480	195	190			9111783	434	305	293		550516738	1	#DIV/0!
38	550516738	2	480	195	185			9111783	442	300	300		550516738	2	#DIV/0!
39	550599392	1	370	280	135			9111783	432	302	302		550599392	1	#DIV/0!
40	550599392	2	370	280	135			9111783	439	297	308		550599392	2	#DIV/0!
41	550599392	3	382	295	138			9111783	461	305	278		550599392	3	#DIV/0!
42	550615183	1	370	204	275			9111783	463	305	281		550615183	1	#DIV/0!
43	550615183	2	405	195	277			9111783	451	310	279		550615183	2	#DIV/0!
44	550736706	1	385	200	170			9111783	470	315	267		550736706	1	#DIV/0!
45	550736706	2	316	201	194			9111783	454	312	270		550736706	2	#DIV/0!
46	550961278	1	360	185	200			9111783	454	320	273		550961278	1	#DIV/0!
47	550961278	2	365	190	214			9111783	444	315	253		550961278	2	#DIV/0!
48	551212312	1	600	290	175			9111783	454	310	257		551212312	1	#DIV/0!
49	551212312	2	600	290	176			9111783	444	325	251		551212312	2	#DIV/0!
50	551769192	1	370	204	275			9111783	456	307	265		551769192	1	#DIV/0!
51	551769192	2	405	195	277			9111783	468	312	265		551769192	2	#DIV/0!
52	552177984	1	340	244	244			9111783	456	317	258		552177984	1	#DIV/0!
53	552177984	2	340	244	244			9111783	458	307	263		552177984	2	#DIV/0!
54	552541351	1	470	325	170			9111783	478	327	262		552541351	1	#DIV/0!
55	552541351	2	485	340	171			9111793	574	232	202		552541351	2	#DIV/0!
56	552541351	3	465	375	215			9111793	574	235	201		552541351	3	#DIV/0!
57	552541351	4	465	325	183			9111793	576	237	201		552541351	4	#DIV/0!
58	553651654	1	390	315	92			9111793	581	235	200		553651654	1	#DIV/0!
59	553651654	2	390	315	108			9111793	559	252	195		553651654	2	#DIV/0!
60	554293254	1	340	175	200			9112185	343	185	205		554293254	1	#DIV/0!
61	554293254	2	341	177	198			9114630	401	275	221		554293254	2	#DIV/0!
62	554392455	1	360	185	200			9114630	394	275	221		554392455	1	#DIV/0!
63	554392455	3	365	190	220			9114630	391	272	221		554392455	3	#DIV/0!
64	555181438	1	305	203	166			9114630	398	272	221		555181438	1	#DIV/0!
65	555181438	2	304	205	134			9114630	396	275	221		555181438	2	#DIV/0!
66	555647464	1	360	204	175			9114630	394	272	221		555647464	1	#DIV/0!
67	555647464	2	360	170	210			9114630	394	272	221		555647464	2	#DIV/0!
68	555647470	1	360	204	175			9115858	466	287	316		555647470	1	#DIV/0!
69	555647470	2	360	170	210			9115872	504	307	265		555647470	2	#DIV/0!
70	555829397	1	405	265	220			9115872	379	185	151		555829397	1	#DIV/0!
71	555829397	2	405	265	200			9116411	499	255	238		555829397	2	#DIV/0!
72	555829915	1	310	210	220			9116411	504	247	238		555829915	1	#DIV/0!
73	555829915	2	310	210	240			9116411	502	252	238		555829915	2	#DIV/0!
74	555831978	1	410	310	265			9116411	502	255	238		555831978	1	#DIV/0!
75	555831978	2	412	317	246			9116411	499	247	238		555831978	2	#DIV/0!
76	555911808	1	408	270	274			9116411	504	247	239		555911808	1	#DIV/0!
77	555911808	2	408	275	255			9116707	550	245	207		555911808	2	#DIV/0!
78	556000498	1	280	165	220			9117265	346	230	228		556000498	1	#DIV/0!
79	556000498	2	275	175	175			9117265	343	250	212		556000498	2	#DIV/0!
80	556032108	1	455	325	200			9117818	418	292	156		556032108	1	#DIV/0!
81	556032108	3	455	325	200			9117818	437	285	151		556032108	3	#DIV/0!
82	556098622	1	470	320	270			9117818	451	280	150		556098622	1	#DIV/0!
83	556098622	2	455	315	275			9117818	442	280	148		556098622	2	#DIV/0!
84	557221669	1	320	253	225			9117818	444	280	148		557221669	1	#DIV/0!
85	557221669	2	320	253	22			9117818	444	285	151		557221669	2	#DIV/0!
86	563771634	1	285	225	155			9117818	444	285	151		563771634	1	#DIV/0!
87	563771634	2	280	160	220			9117818	422	295	150		563771634	2	#DIV/0!
88	563771638	1	285	225	155			9117818	437	290	148		563771638	1	#DIV/0!
89	563771638	2	280	160	220			9119653	458	312	107		563771638	2	#DIV/0!
90	563771639	1	285	225	155			9122997	509	250	272		563771639	1	#DIV/0!
91	563771639	2	280	160	220			9122997	499	250	271		563771639	2	#DIV/0!
92	564028298	1	405	275	290			9126013	504	310	263		564028298	1	#DIV/0!
93	564028298	2	410	260	320			9126013	533	305	263		564028298	2	#DIV/0!
94								9138278	384	242	164
95								9138839	394	312	127
96								9138839	410	312	124
97								9138839	398	315	126
98								9138839	454	315	109
99								9138839	398	2	110
100								9138897	602	332	235
101								9142132	480	302	279
102								9142132	521	300	276
103								9142132	468	317	264
104								9142132	482	320	251
105								9142132	478	312	246
106								9142132	494	327	258
107								9142132	490	325	258
108								9142219	403	205	236
109								9142219	401	200	237
110								9142219	410	205	241
111								9142394	451	245	363
112								9142394	449	240	364
113								9142394	456	242	362
114								9142394	439	262	350
115								9155182	353	232	233
116								9155182	377	240	220
117								9156093	389	265	172
118								9156450	463	315	96
119								9156450	446	315	96
120								9156848	362	182	105
121								9156848	360	182	105
122								9156848	355	182	105
123								9156848	355	182	105
124								9156848	355	182	105
125								9156848	358	182	105
126								9156848	355	182	105
127								9156848	355	185	105
128								9156848	348	182	105
129								9156848	358	182	105
130								9156848	355	180	105
131								9156848	348	185	105
132								9156848	355	180	105
133								9156848	348	182	105
134								9156848	350	182	105
135								9156848	355	185	105
136								9156848	362	182	105
137								9156848	355	182	105
138								9156848	355	185	105
139								9156848	353	185	105
140								9156848	355	180	105
141								9156848	355	182	105
142								9156848	362	182	105
143								9156848	355	185	105
144								9156848	355	182	105
145								9156848	358	182	105
146								9156848	358	182	105
147								9156848	355	185	105
148								9156848	355	180	105
149								9156848	358	182	105
150								9156848	353	185	105
151								9156848	355	180	105
152								9156848	358	182	105
153								9156848	355	182	105
154								9156848	362	177	105
155								9156848	353	185	105
156								9156848	355	185	105
157								9156848	353	182	105
158								9156848	348	182	105
159								9156848	365	182	110
160								9156848	348	182	105
161								9156848	350	182	105
162								9156848	355	180	105
163								9156848	350	182	105
164								9156848	353	182	105
165								9156848	353	185	105
166								9156848	355	177	105
167								9156848	358	182	105
168								9156848	350	185	105
169								9156848	362	182	105
170								9156848	350	182	105
171								9156848	353	185	105
172								9156848	355	182	105
173								9156848	355	182	105
174								9156848	355	182	105
175								9156848	365	180	111
176								9156848	370	182	105
177								9156848	355	182	105
178								9156933	343	2	204
179								9156933	338	2	205
180								9159205	542	237	293
181								9159205	542	237	306
182								9159205	542	235	303
183								9159205	540	237	296
184								9159205	545	240	301
185								9159205	545	235	302
186								9159205	540	235	299
Sheet8

Cell Formulas
Range	Formula
Q2:Q93	Q2	=LET(r,FILTER($E$2:$G$9000,($C$2:$C$9000=O2)*($D$2:$D$9000=P2)),v_1,INDEX(r,1),v_2,INDEX(r,2),v_3,INDEX(r,3),ROWS(FILTER($J$2:$J$9000,($J$2:$J$9000=O2)*($K$2:$K$9000/v_1<(1+$R$2))*($L$2:$L$9000/v_2<(1+$R$2))*($M$2:$M$9000/v_3<(1+$R$2))*(v_1/$K$2:$K$9000<(1+$R$2))*(v_2/$L$2:$L$9000<(1+$R$2))*(v_3/$M$2:$M$9000<(1+$R$2)))))