PDA

View Full Version : Complex-ish macro including averageing and choosing-data-by-identifier



KptnKoma
04-18-2013, 04:43 AM
Hi.
Started on some simple macros only recently. Came therefore across a problem quickly that seems to be above my current command of vba.

1. Background
- using Excel 2010
- have a dataset of pretty varying values, it looks something like this

plot value
1 31
1 25
1 32
2 21
2 19
3 46
3 55
3 41
3 52

... and so forth. It is not very good data to start with, i am aware of that. But still, i have to work with it. Thus,

2. What do i want vba to do?
2.1. compare values within one plot to each other
2.2. choose two that are within 10% of each other
2.3. visualize the ones chosen/delete the others or something of that kind, give an average of the chosen two in a new cell
2.4. move to the next plot and continue.

3. What do i need help with?
2.1 and 2.2 seem to be the most difficult ones for me. My code does not stretch further than finding the "plot" column and moving to value one yet.

You will probably and rightfully tell me that this is no easy task for a newbie. Rightfully so. Would you still be able to point me in the right direction? Would be very grateful indeed!

Cheers
H

mdmackillop
04-18-2013, 02:05 PM
Welcome to VBAX
Give this a try. It doesn't look for 10% but keeps the two closest values (although that may need refining if multiple cases occur)
Step through the code using F8 to see how the data is processed.

KptnKoma
04-18-2013, 11:13 PM
Thanks alot! I think that solves most of my problem and is some very nice code to learn from for me. Highly appreciated!

- Wait, now i ran it on a copy of my data and it is giving far too many (and wrong) results. I will try to track down the problem, at first glance it looks like there is an issue with identifying which values belong to a plot when there are two alike values in a plot. If you want to look at it: In the attachement, sheet1 has some of data proccessed with your code, on sheet 2 i did some of it by hand.

Cheers
H