VLOOKUP for partial match and average spectras in another sheet

[ExcellDummie]

Hi

The task wish bring me to this forum is the following one.

I have a huge (70000...) collection of spectras (30 per sample) (i have more than 2000 samples, so I can't do it manually in reasonable time).

All these spectras names have this structure : 2190202-14.sam. 2190202 is the sample number, 14 the number of the spectra for this sample (si, it was the 14th spectra recorded for that sample), and .sam the file format (not important).

I use often vloopup formulas to merge some data(for example, chemistry and spectra data, based on sample name matching). So, I tried to design a vlookup which make a partial match and average the result (and place it in another sheet than the one whoch contains all the data), but it not worked...

My problem can be solved ?

Thanks in advance.

Edit : I solved the problem by myself.
I used the LEFT and/or research and replace functions to rename all my samples with the same numbers of characters (so, i eliminate the .sam and the spectra number information : only the sample name information is keeped). After, I made a pivot table, with samples as rows, and the rest as values, and I replace "sum of" by "average of".

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.