Extract data from table based on symbol and return the value from column.

[nredman]

Hi Experts,

We are trying to extract data based on the "symbol" for example find all "CNU" and return the "C" value. i.e. for CNU 3.44 into separate columns (figure 2) for all the symbols.

Is this possible?

Thank you

Figure 1

[TABLE="width: 970"]
<colgroup><col span="10"></colgroup><tbody>[TR]
[TD]Symbol[/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]T[/TD]
[TD]O[/TD]
[TD]H[/TD]
[TD]L[/TD]
[TD]C[/TD]
[TD]V[/TD]
[TD]OI[/TD]
[/TR]
[TR]
[TD]CNU[/TD]
[TD] [/TD]
[TD]29-Jan-16[/TD]
[TD]0[/TD]
[TD]3.41[/TD]
[TD]3.45[/TD]
[TD]3.4[/TD]
[TD]3.44[/TD]
[TD]112474[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MTU[/TD]
[TD] [/TD]
[TD]29-Jan-16[/TD]
[TD]0[/TD]
[TD]12.45[/TD]
[TD]12.8[/TD]
[TD]12.41[/TD]
[TD]12.58[/TD]
[TD]3879420[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]SPK[/TD]
[TD] [/TD]
[TD]29-Jan-16[/TD]
[TD]0[/TD]
[TD]3.08[/TD]
[TD]3.1[/TD]
[TD]2.99[/TD]
[TD]3.1[/TD]
[TD]2373568[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TPM[/TD]
[TD] [/TD]
[TD]29-Jan-16[/TD]
[TD]0[/TD]
[TD]9.9[/TD]
[TD]10.13[/TD]
[TD]9.895[/TD]
[TD]10.05[/TD]
[TD]1309932[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TLS[/TD]
[TD] [/TD]
[TD]29-Jan-16[/TD]
[TD]0[/TD]
[TD]5.57[/TD]
[TD]5.63[/TD]
[TD]5.57[/TD]
[TD]5.63[/TD]
[TD]27231040[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]VOC[/TD]
[TD] [/TD]
[TD]29-Jan-16[/TD]
[TD]0[/TD]
[TD]7.8[/TD]
[TD]8[/TD]
[TD]7.75[/TD]
[TD]7.86[/TD]
[TD]5497848[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]SPK[/TD]
[TD] [/TD]
[TD]28-Jan-16[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]3.08[/TD]
[TD]2.99[/TD]
[TD]3.06[/TD]
[TD]1141232[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNU[/TD]
[TD] [/TD]
[TD]28-Jan-16[/TD]
[TD]0[/TD]
[TD]3.42[/TD]
[TD]3.425[/TD]
[TD]3.36[/TD]
[TD]3.41[/TD]
[TD]148818[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MTU[/TD]
[TD] [/TD]
[TD]28-Jan-16[/TD]
[TD]0[/TD]
[TD]11.77[/TD]
[TD]12.2[/TD]
[TD]11.69[/TD]
[TD]12.1[/TD]
[TD]1437747[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TPM[/TD]
[TD] [/TD]
[TD]28-Jan-16[/TD]
[TD]0[/TD]
[TD]9.77[/TD]
[TD]9.95[/TD]
[TD]9.7[/TD]
[TD]9.88[/TD]
[TD]1226355[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TLS[/TD]
[TD] [/TD]
[TD]28-Jan-16[/TD]
[TD]0[/TD]
[TD]5.51[/TD]
[TD]5.62[/TD]
[TD]5.49[/TD]
[TD]5.59[/TD]
[TD]28126660[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]VOC[/TD]
[TD] [/TD]
[TD]28-Jan-16[/TD]
[TD]0[/TD]
[TD]7.3[/TD]
[TD]7.58[/TD]
[TD]7.25[/TD]
[TD]7.5[/TD]
[TD]2731219[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNU[/TD]
[TD] [/TD]
[TD]27-Jan-16[/TD]
[TD]0[/TD]
[TD]3.43[/TD]
[TD]3.55[/TD]
[TD]3.42[/TD]
[TD]3.44[/TD]
[TD]171871[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]SPK[/TD]
[TD] [/TD]
[TD]27-Jan-16[/TD]
[TD]0[/TD]
[TD]3.05[/TD]
[TD]3.08[/TD]
[TD]3[/TD]
[TD]3.01[/TD]
[TD]819206[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MTU[/TD]
[TD] [/TD]
[TD]27-Jan-16[/TD]
[TD]0[/TD]
[TD]11.55[/TD]
[TD]11.8[/TD]
[TD]11.47[/TD]
[TD]11.78[/TD]
[TD]1394994[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TLS[/TD]
[TD] [/TD]
[TD]27-Jan-16[/TD]
[TD]0[/TD]
[TD]5.53[/TD]
[TD]5.54[/TD]
[TD]5.5[/TD]
[TD]5.53[/TD]
[TD]19720440[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]VOC[/TD]
[TD] [/TD]
[TD]27-Jan-16[/TD]
[TD]0[/TD]
[TD]7.2[/TD]
[TD]7.38[/TD]
[TD]7.19[/TD]
[TD]7.33[/TD]
[TD]1421436[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TPM[/TD]
[TD] [/TD]
[TD]27-Jan-16[/TD]
[TD]0[/TD]
[TD]9.99[/TD]
[TD]10.025[/TD]
[TD]9.75[/TD]
[TD]9.78[/TD]
[TD]1462942[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MTU[/TD]
[TD] [/TD]
[TD]25-Jan-16[/TD]
[TD]0[/TD]
[TD]11.43[/TD]
[TD]11.61[/TD]
[TD]11.43[/TD]
[TD]11.59[/TD]
[TD]858948[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CNU[/TD]
[TD] [/TD]
[TD]25-Jan-16[/TD]
[TD]0[/TD]
[TD]3.43[/TD]
[TD]3.43[/TD]
[TD]3.39[/TD]
[TD]3.39[/TD]
[TD]85512[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Figure 2
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]cnu[/TD]
[TD="class: xl65, width: 64"]mtu[/TD]
[/TR]
[TR]
[TD="class: xl66"]3.44[/TD]
[TD="class: xl68"]12.58[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]3.41[/TD]
[TD="class: xl68"]12.1[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]3.44[/TD]
[TD="class: xl68"]11.78[/TD]
[/TR]
</tbody>[/TABLE]
3.39

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this formula. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across & down.

Excel Workbook
	A	H	K	L	M	N	O	P	Q	R
1	Symbol	C			CNU	MTU	SPK	TPM	TLS	VOC
2	CNU	3.44			3.44	12.58	3.1	10.05	5.63	7.86
3	MTU	12.58			3.41	12.1	3.06	9.88	5.59	7.5
4	SPK	3.1			3.44	11.78	3.01	9.78	5.53	7.33
5	TPM	10.05			3.39	11.59
6	TLS	5.63
7	VOC	7.86
8	SPK	3.06
9	CNU	3.41
10	MTU	12.1
11	TPM	9.88
12	TLS	5.59
13	VOC	7.5
14	CNU	3.44
15	SPK	3.01
16	MTU	11.78
17	TLS	5.53
18	VOC	7.33
19	TPM	9.78
20	MTU	11.59
21	CNU	3.39
List Values

 

[nredman]

Thank you Peter for your assistance and time.

 

[nredman]

Hi Peter,

I have no idea why the formula works ok with all symbols except "SPK"?

Thanks

 

[nredman]

Hi Peter,

Its all good, thanks again.

 

[Peter_SSs]

Cheers. Glad you got it sorted.