User not logged in - login - register
Home Calendar Books School Tool Photo Gallery Message Boards Users Statistics Advertise Site Info
go to bottom | |
 Message Boards » » Excel Help Page [1]  
wdprice3
BinaryBuffonary
45908 Posts
user info
edit post

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

BlackJesus
Suspended
13089 Posts
user info
edit post

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

darkone
(\/) (;,,,;) (\/)
11597 Posts
user info
edit post

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

12/14/2012 11:58:33 AM

wdprice3
BinaryBuffonary
45908 Posts
user info
edit post

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

neodata686
All American
11577 Posts
user info
edit post

^^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

Perlith
All American
7620 Posts
user info
edit post

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

HaLo
All American
14050 Posts
user info
edit post

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

dtownral
Suspended
26632 Posts
user info
edit post

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

wdprice3
BinaryBuffonary
45908 Posts
user info
edit post

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

lewisje
All American
9196 Posts
user info
edit post

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

dtownral
Suspended
26632 Posts
user info
edit post

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

Cherokee
All American
8264 Posts
user info
edit post

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

wdprice3
BinaryBuffonary
45908 Posts
user info
edit post

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

neodata686
All American
11577 Posts
user info
edit post

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

JT3bucky
All American
23142 Posts
user info
edit post

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

dzags18
All American
5694 Posts
user info
edit post

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

LastInACC
All American
1843 Posts
user info
edit post

^^, repeat or no repeat?

3/19/2015 2:45:42 PM

TreeTwista10
Forgetful Jones
147484 Posts
user info
edit post

basically what ^^ said

add a new column, put

=RAND()
in the first cell, then copy it down

3/19/2015 5:27:10 PM

LastInACC
All American
1843 Posts
user info
edit post

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

JT3bucky
All American
23142 Posts
user info
edit post

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

AstralEngine
All American
3864 Posts
user info
edit post

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

3/20/2015 1:18:28 PM

JT3bucky
All American
23142 Posts
user info
edit post

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

3/22/2015 6:51:36 PM

neodata686
All American
11577 Posts
user info
edit post

3/22/2015 7:15:05 PM

JT3bucky
All American
23142 Posts
user info
edit post

Where can I find a tutorial?

3/22/2015 7:21:31 PM

AstralEngine
All American
3864 Posts
user info
edit post

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

AstralEngine
All American
3864 Posts
user info
edit post


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





And then you can put information into other tabs by setting Range.Value equal to whatever you want, in the same format you got it back from above (an array of arrays of rows in the range).

Also, with the workbook.Sheets value, you can loop through every sheet, Activate() it, and then find the last row and column and run through the values and do your calculations.

Let me know if you have questions.

3/23/2015 9:34:24 AM

seedless
All American
27142 Posts
user info
edit post

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

wdprice3
BinaryBuffonary
45908 Posts
user info
edit post

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

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

Fareako
Shitter Pilot
10238 Posts
user info
edit post

I don't know

5/3/2015 3:26:49 PM

 Message Boards » Tech Talk » Excel Help Page [1]  
go to top | |
Admin Options : move topic | lock topic

© 2024 by The Wolf Web - All Rights Reserved.
The material located at this site is not endorsed, sponsored or provided by or on behalf of North Carolina State University.
Powered by CrazyWeb v2.38 - our disclaimer.