I converted the text into number. The problem I am having is that, excel could not handle this 1M multiple VLOOKUPs. I am wondering is there a better way to do that.



On Sunday, June 15, 2014 2:33:28 AM UTC-6, GS wrote:
Thanks Gary and Claus. I got it working. Is it possible to convert

the 3 column data into matrix grid. I tried using INDEX and MATCH as

mentioned here.

http://exceltactics.com/vlookup-mult...g-index-match/

but my data set will have more than 250 unique rows and more than

4000 unique columns or vice-verse. Is there an efficient way to

achieve this? Ultimately I would like to plot the data as a surface

plot.



I'm seeing this data as very much resembling the output log from some

sort of a data recorder, and so most I'm familiar with have their own

software for manipulating that data in various ways. Is this an option?



You could create an array of arrays to build the matrix, then transfer

that to a 2D array for output to a worksheet. I'm inclined to go the

4000 rows by 250 columns route, but either way will result a fairly

large load on system resources. Especially if you go the VLOOKUP route

what with that many formulas (4000*250=1M cells)!



Are you wanting a grid that contains 1 row only for each Retention Time

without duplicate values across the row?



Also, note that the parsing results are 'text' and so would need to be

reprocessed as Variant so they're useable as numeric values when they

land on the grid in order to 'plot' efficiently.



Also, how do you want the value pairs to be displayed in the grid?

Current parse returns each value to its own column and so your 1st

example re-parses to...



0.6:30.92:269:30.99:317:32.59:302:33.26:337...



..and so on.



Is this for '#NPOINTS= 10' only, or for all data? (An example of the

expected result would be nice to work with!)



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion