Some help with my Master's project could get someone an acknowledgment... I need help on this, as copying and transposing 12 cells at a time adds up after a while, and I need to be able to run this data quickly so I can see how the variables interact. Here's the deal. I'm using three DOS programs for running elliptical fourier analysis on fossil clams. They essentially take the coordinates of the shell outline and spit out as many Fourier coefficients as I need. I've been working with 12, but I may try more in the future. For some reason, the programs output this data in a wonderful way to read it by hand, but if I have to run PCA on it (which I do!), it needs to get rearranged. No, I am not attempting to use Excel for Principal Component Analysis. Luckily, I already found a batch file to combine all the output files (one for each shell I digitize, which will increase as well...), and I can import that into Excel as delineated so it's all columnar. Then it looks like this: Fourier coeff | A | B ------------------------------------------ HMATCH: S2841 2 .21148920 .06950466 3 -.01807167 -.00403423 4 .00864765 .06429635 5 .00471425 .01682626 6 -.02873335 .00055260 7 -.01727874 .01729578 8 -.02025489 -.00834811 9 -.00947081 -.00545954 10 -.00671909 -.01138494 11 -.01116356 .00199545 12 -.00644372 -.00343064 HMATCH: S2775J 2 .17108740 .04638181 3 -.02125239 .00420174 4 -.00909468 -.00437767 5 -.02222687 .02087858 6 .00647775 .02050471 7 -.02449726 -.00886601 8 -.00729853 .01883770 9 -.01651866 -.00677889 10 -.01997815 -.00762972 11 -.00147566 -.01738936 12 -.01408296 -.01063410 . . . etc. I need it to be set up something like this: Snumber A2 A3 A4 A5 A6 A7 . . . A12 B2 B3 B4 B5 B6 . . . B12 Snumber A2 A3 A4 A5 A6 A7 . . . A12 B2 B3 B4 B5 B6 . . . B12 . . etc. Does anyone know how to do this so it's fairly scalable? I don't know how to do macros right now and I don't really have time to learn. The syntax would be something like: write the header rows (A2, etc) loop this until the end of the file: write the Snumber transpose the A Fourier coefficents for that Snumber transpose the B Fourier coefficents for that Snumber end loop Can someone PLEASE help?!!
I work with an excel expert, but i wont see her 'till monday I dont know enough about it meself to be able to help
This is pretty ugly, but I threw it together in about 10 minutes. Import your data as space delimited into the mburtonk worksheet, on the sheet1 spreadsheet copy the formula down each column for as many rows of data you've imported (you can simply copy cell A1 and then select the "A" and copy down the entire column if you want - that is probably how I would do it - doing that for each cell). After it is converted on Sheet1, sort the whole sheet (select the sheet, data>sort hit ok) to get rid of all the false values (in other words, delete them or whatever) - there's probably an easier way, but I couldn't figure how to make a false value delete the cell itself. You can then save this off as a txt file, delimited if you want - either reimporting it or not (I don't know a whole lot about excel, but if you export as text formulas become values). Hope this helps - oh yeah rename it to .xls obviously and this was done in Excel 2002.
THANK YOU FASIENT!!!!! Okay, high school girl hysterics over. That's a very elegant and simple solution to my problem. I don't use Excel all that often, so I forget about those silly conditional statements. I'm in the process of making some changes because I gave you the wrong specific output, but it works beautifully!
