I have 42 sets of x,y data; each set has a different number of points (x,y pair), ranging from say 35 points up to 200 points. Each data set is in it's own sheet; I have 3 workbooks (7 data sets in a sheet, 7 data sets in a sheet, and 28 data sets in a sheet).

I need to filter out points based on distance between them to achieve 50 points per data set. Thus, the filtering distance is unique to each data set. I also need the points to stay in the exact order they are currently in (minus the filtered points). I need to end up with 42 sets of 50 points each (or less for those without 50) without blank cells between values.

The sets are set up with column of y and a column of x values.

I imagine the most efficient way is VB, of which I know nothing.

Help?

12/14/2012 9:58:56 AM

http://www.walmart.com/ip/14096531?adid=222222222270000417025&wmlspartner=wlpa&wl0=&wl1=g&wl2=&wl3=21486607510&wl4=&wl5=pla&veh=sem

12/14/2012 11:08:49 AM

I don't care what people say, Excel is not analysis software.

12/14/2012 11:58:33 AM

You are correct. I'm not using Excel for analysis, just for data formatting and transition.

Though this request may be moot if I can get AutoCAD to play nicely and export my shit correctly.

[Edited on December 14, 2012 at 12:14 PM. Reason : .]

12/14/2012 12:14:21 PM

^^In some industries it's the best tool for the job. You obviously supplement it with other tools but for high level analysis and presentation it's great.

12/14/2012 12:22:16 PM

Excel is the second best tool to do just about anything. There is always a better tool but Excel's tradeoff is its ubiquity. Still amazes me how much basic linear programming you can do with Solver.

Overall, to your question, this could be done in VB. VB within Excel could do the job, but if you ever needed to change this in the future, (m datasets, n pairs, o workbooks, etc.) ... good luck. If AutoCAD can export to another format other than Excel, I'd prefer the raw data to work with rather than Excel's proprietary file format. You'd be amazed how often you need to reference the past data, and/or, combine past data with present data ... much easier to do so within raw data than with Excel. I'd prefer a standalone program in language of choice that could read raw input from a directory and spit out whatever output you need.

Question I have: How much you willing to pay to have this done? There's a fair bit of folks on TWW who probably wouldn't mind some extra holiday cash at this point. Or are you strictly looking to do this yourself?

12/14/2012 2:04:58 PM

Is "distance between them" the absolute value of y-x? Or are they Cartesian coordinates? Can you post a version of it to google spreadsheets?

[Edited on December 14, 2012 at 2:59 PM. Reason : Z]

12/14/2012 2:57:31 PM

Matrix functions should help make this easier to scale and cut and paste

... I think, let me think about this

12/14/2012 5:47:51 PM

Finally got AutoCAD to play nicely so I get to avoid this data set fun. Thanks though.

^haha, ah matrices... been too long... don't want to go back


^^cartesian, so a^2 + b^2 = c^2

^^^I was very close to putting money into this. I need to learn VB, just never have the time. I started on a VB for dummies book (FWIW) but never got far.

[Edited on December 14, 2012 at 5:55 PM. Reason : .]

12/14/2012 5:54:55 PM

You need to specifically learn VBA (Visual Basic for Applications), not the related oldskool Visual Basic, VB.NET, or VBScript

12/14/2012 6:21:26 PM

Matrix functions in excel just help you work with rows and columns, no need to get too complicated. You got to know a couple weird tricks though for them to work.

12/14/2012 6:22:42 PM

would have needed to know what the criteria is regarding removing points (ie: remove points that have a difference of 5, 6, 30, etc)

12/16/2012 1:31:49 AM

No, that would have been different for each set based on reducing the number of points to 50 in each set

12/16/2012 1:38:15 PM

So Powerpivot is my new favorite tool. I can pull in data directly from a SQL or flat file and leverage all the pivot table/chart/slicing features of Excel. Pretty neato.

12/20/2012 9:55:21 AM

BTTT


I have a bunch of songs(200) I want to randomly select some of those songs (24 to be exact) and place them in a table.

Then I want to be able to take that table and copy it to word.

Copying to word isn't the hard hard, I don't know how to randomize that list into a block of 24...anyone?

3/19/2015 1:23:01 PM

You could add a random number next to each song title, pull the top 24 into a table.

3/19/2015 2:26:42 PM

^^, repeat or no repeat?

3/19/2015 2:45:42 PM

basically what ^^ said

add a new column, put

=RAND()

3/19/2015 5:27:10 PM

also can't you just randomize the list of 200. and then select copy and paste the 1st 24 the next 24 so on and so forth.

3/19/2015 5:40:00 PM

I get that, and have done the random sort and that worked on a small scale...

The only thing is it wouldnt allow me to include over 200 songs, only the exact amount I need.

So I would need to make a list of all 200 songs and then make a table in the same sheet...then somehow make it take a random number and put it into the table...

is that possible? that what you're saying?

3/19/2015 7:41:47 PM

how about doing it in python? win32com is the python implementation of the excel vb APIs.

3/20/2015 1:18:28 PM

^come again? python? that's new to me.

3/22/2015 6:51:36 PM

3/22/2015 7:15:05 PM

Where can I find a tutorial?

3/22/2015 7:21:31 PM

the documentation for it is, unfortunately, god awful. I've needed it for some work stuff recently, I'll post some code snippets tomorrow that'll help you do shit.

then you can ask me if you have questions.

3/22/2015 10:57:39 PM

from win32com.client import Dispatch
import win32com.client as client

excel = Dispatch('Excel.Application')
excel.DisplayAlerts = False                #stops the 'are you sure' dialogs from popping up
excel.Visible=True                            #opens the excel spreadsheet so you can see what happens when                  you do things in real time

workbook = excel.Workbooks.Open(filename)   #opens the workbook
workBook = excel.ActiveWorkbook
sheets = workBook.Sheets                             

sheetOfInterest = sheets('sheetname')
sheetOfInterest.Activate()

LastRow = sheetOfInterest .UsedRange.Rows.Count
LastColumn = sheetOfInterest .UsedRange.Columns.Count

range = sheetOfInterest.Range(<>   #you can define a range of cells or one cell here, with numbers (1,1) is A1 or in Excel notation ('A1')  or ranges ('A1:B45')

arrayOfArrays = range.Value  # I think this works, should return an array of arrays, each internal array is a line in the range


for array in arrayOfArrays:
    #do whatever

3/23/2015 9:34:24 AM

Excel works just fine where I work. Who the fuck wants to pay an arm and a leg to calculate %CV, SD, and other low level low shit like that about 95% of the time?

3/23/2015 3:28:09 PM

*********
*********

What's the excel syntax for a relative-cell based linear interpolation function (e.g., function figures out where the x value in question lies within a data set and then interpolates with the known x's and y's)?

No VBA. I've done this before, but I can't brain today.

*********
*********

[Edited on April 14, 2015 at 3:07 PM. Reason : I'm no good with index/match/offset sometimes ]

4/14/2015 3:07:02 PM

I don't know

5/3/2015 3:26:49 PM