Help Designing a Macro to Align Data

[A1MBand]

Hey everyone!

Brand new member here! I am running Excel for Mac OS X 2011 on Mac OS 10.8.

I'm working on a science experiment involving a lot of data with multiple trials. Here is a sample of raw values:

x1	y1		x2	y2		x3	y3
51.4	2000.3334		50.7	2000.3334		51.1	2000.3334
52.7	4000.6667		51	2000.3334		52.1	2000.3334
52.9	2000.3334		52.4	2000.3334		52.7	4000.6667
53	4000.6667		52.6	4000.6667		52.8	8001.3335
53.1	2000.3334		52.8	6001.0001		52.9	6001.0001
53.2	8001.3335		52.9	4000.6667		53	2000.3334
53.3	2000.3334		53	1.40E+04		53.1	2000.3334
54.2	2000.3334		53.1	8001.3335		53.2	6001.0001
54.4	6001.0001		53.2	2000.3334		53.3	8001.3335
54.5	1.20E+04		53.3	1.20E+04		53.9	2000.3334
54.6	2.60E+04		54.3	4000.6667		54.4	8001.3335
54.7	1.60E+04		54.4	6001.0001		54.5	1.80E+04
54.8	2.60E+04		54.5	3.20E+04		54.6	2.80E+04
54.9	2.00E+04		54.6	3.40E+04		54.7	2.80E+04
55	1.80E+04		54.7	3.40E+04		54.8	5.20E+04
55.1	2.00E+04		54.8	6.60E+04		54.9	6.00E+04
55.2	3.80E+04		54.9	7.20E+04		55	4.00E+04
55.3	4.80E+04		55	7.60E+04		55.1	3.60E+04
55.4	1.60E+04		55.1	5.60E+04		55.2	5.20E+04
55.7	2000.3334		55.2	4.80E+04		55.3	4.00E+04
55.8	2000.3334		55.3	8.40E+04		55.4	1.40E+04
55.9	2000.3334		55.4	1.40E+04		55.7	2000.3334
56.2	8001.3335		55.5	2000.3334		55.8	6001.0001
56.3	4000.6667		55.8	8001.3335		55.9	2000.3334
56.4	8.00E+04		56	6001.0001		56	2000.3334
56.5	2.12E+05		56.1	4000.6667		56.1	4000.6667
56.6	3.68E+05		56.2	4000.6667		56.2	1.00E+04
56.7	5.26E+05		56.3	1.40E+04		56.3	1.20E+04
56.8	6.42E+05		56.4	6.00E+04		56.4	8.40E+04
56.9	8.44E+05		56.5	2.44E+05		56.5	2.48E+05
57	8.26E+05		56.6	4.70E+05		56.6	3.44E+05

<tbody>
</tbody>

My x-values should increment by 0.1 but some values are skipped. I was wondering if someone can help me write a macro or formula to fill in the gaps and line up the data; that is, insert missing (x, y) pairs in each column wherever x-values skip by more than .1. For each inserted x-value, a corresponding y-value should be invented that takes the lesser of the established y-value above and below it. The effect is that all x-values would be the same in any given row whereas the y-values (whether native or invented) may vary.

I have taken the data above and done this manually to show an example of the output I am looking for:

x1	y1		x2	y2		x3	y3
50	2000.3334		50	2000.3334		50	2000.3334
50.1	2000.3334		50.1	2000.3334		50.1	2000.3334
50.2	2000.3334		50.2	2000.3334		50.2	2000.3334
50.3	2000.3334		50.3	2000.3334		50.3	2000.3334
50.4	2000.3334		50.4	2000.3334		50.4	2000.3334
50.5	2000.3334		50.5	2000.3334		50.5	2000.3334
50.6	2000.3334		50.6	2000.3334		50.6	2000.3334
50.7	2000.3334		50.7	2000.3334		50.7	2000.3334
50.8	2000.3334		50.8	2000.3334		50.8	2000.3334
50.9	2000.3334		50.9	2000.3334		50.9	2000.3334
51	2000.3334		51	2000.3334		51	2000.3334
51.1	2000.3334		51.1	2000.3334		51.1	2000.3334
51.2	2000.3334		51.2	2000.3334		51.2	2000.3334
51.3	2000.3334		51.3	2000.3334		51.3	2000.3334
51.4	2000.3334		51.4	2000.3334		51.4	2000.3334
51.5	2000.3334		51.5	2000.3334		51.5	2000.3334
51.6	2000.3334		51.6	2000.3334		51.6	2000.3334
51.7	2000.3334		51.7	2000.3334		51.7	2000.3334
51.8	2000.3334		51.8	2000.3334		51.8	2000.3334
51.9	2000.3334		51.9	2000.3334		51.9	2000.3334
52	2000.3334		52	2000.3334		52	2000.3334
52.1	2000.3334		52.1	2000.3334		52.1	2000.3334
52.2	2000.3334		52.2	2000.3334		52.2	2000.3334
52.3	2000.3334		52.3	2000.3334		52.3	2000.3334
52.4	2000.3334		52.4	2000.3334		52.4	2000.3334
52.5	2000.3334		52.5	2000.3334		52.5	2000.3334
52.6	2000.3334		52.6	4000.6667		52.6	2000.3334
52.7	4000.6667		52.7	4000.6667		52.7	4000.6667
52.8	2000.3334		52.8	6001.0001		52.8	8001.3335
52.9	2000.3334		52.9	4000.6667		52.9	6001.0001
53	4000.6667		53	1.40E+04		53	2000.3334
53.1	2000.3334		53.1	8001.3335		53.1	2000.3334
53.2	8001.3335		53.2	2000.3334		53.2	6001.0001
53.3	2000.3334		53.3	1.20E+04		53.3	8001.3335
53.4	2000.3334		53.4	4000.6667		53.4	2000.3334
53.5	2000.3334		53.5	4000.6667		53.5	2000.3334
53.6	2000.3334		53.6	4000.6667		53.6	2000.3334
53.7	2000.3334		53.7	4000.6667		53.7	2000.3334
53.8	2000.3334		53.8	4000.6667		53.8	2000.3334
53.9	2000.3334		53.9	4000.6667		53.9	2000.3334
54	2000.3334		54	4000.6667		54	2000.3334
54.1	2000.3334		54.1	4000.6667		54.1	2000.3334
54.2	2000.3334		54.2	4000.6667		54.2	2000.3334
54.3	2000.3334		54.3	4000.6667		54.3	2000.3334
54.4	6001.0001		54.4	6001.0001		54.4	8001.3335
54.5	1.20E+04		54.5	3.20E+04		54.5	1.80E+04
54.6	2.60E+04		54.6	3.40E+04		54.6	2.80E+04
54.7	1.60E+04		54.7	3.40E+04		54.7	2.80E+04
54.8	2.60E+04		54.8	6.60E+04		54.8	5.20E+04
54.9	2.00E+04		54.9	7.20E+04		54.9	6.00E+04
55	1.80E+04		55	7.60E+04		55	4.00E+04
55.1	2.00E+04		55.1	5.60E+04		55.1	3.60E+04
55.2	3.80E+04		55.2	4.80E+04		55.2	5.20E+04
55.3	4.80E+04		55.3	8.40E+04		55.3	4.00E+04
55.4	1.60E+04		55.4	1.40E+04		55.4	1.40E+04
55.5	2000.3334		55.5	2000.3334		55.5	2000.3334
55.6	2000.3334		55.6	2000.3334		55.6	2000.3334
55.7	2000.3334		55.7	2000.3334		55.7	2000.3334
55.8	2000.3334		55.8	8001.3335		55.8	6001.0001
55.9	2000.3334		55.9	6001.0001		55.9	2000.3334
56	2000.3334		56	6001.0001		56	2000.3334

<tbody>
</tbody>

The (x, y) values highlighted in red represent the invented values that the macro should insert. Notice how all x-values progress steadily by 0.1 increments, and consequently all unique x-values are aligned on the same row (e.g. all pairs where x = 55.9 are on the same row). Inserted y-values should be determined by taking the "low" of the y-value on either side (before/after), (e.g. x2 = 55.9 [inserted] corresponds to y2 = 6001.0001 [inserted] because this was the lesser of the y2-value before it (8001.3335) and the y2-value after it (6001.001). In a large gap, the y-values before and after the gap are compared, and the lesser of which is repeatedly inserted for every single y-value within the gap; meanwhile the corresponding x-values within the gap are inserted at 0.1 increments until the gap is patched. (This is seen on the 'x2' range from 53.4 to 54.2: the y2 values here all take on 4000.6667 because it is the lesser of the y2-value before the gap (12000) and y2-value after it (4000.6667)).

Unfortunately, it's unrealistically tedious to work through my entire dataset manually (hence the need to automate)! I don't know much about VBA or macros, so please bear with me and walk me through the steps if possible! I'm assuming I would need to customize the parameters of the macro to take into account all the rows and columns.

A final bit about those parameters: The entire dataset starts at 50.0. and ends at 650.0, and there are five (x, y) pairs (although only three are shown above). Also, I need to apply this macro to multiple worksheets.

I hope this explanation is clear and you understand what I'm trying to do. If not, please ask, and I will clarify with a follow-up!

Your help is so greatly appreciated. Thank you in advance!

 

[A1MBand]

Does anyone know if a macro like this is possible? Or would my best bet be to accomplish this the long way (manually)?

Please help! Even if you only have partial insight!

 

[A1MBand]

Bump

 

[JoeMo]

This can be done with a macro. I would suggest since the data set runs from 50 to 650 that the macro first creates a new sheet with a single x column that goes from 50 to 650 in 0.1 increments. Then add 5 Y columns using the data from your raw data sheet where it matches the x value, or fabricating the y value if there is no such x value on the raw data sheet. Where there are sequential fabricated y values, only the first one needs to be determined from the minimum of the two y values that straddle the gap, the others will all be equal to this first one.

Perhaps you could provide more information on the exact layout of the raw data sheet .... what columns are the x-y pairs in and starting in what row, etc.? This will improve your chances of getting some help.

 

[A1MBand]

Hey Joe,

Thanks for your reply!!

This can be done with a macro. I would suggest since the data set runs from 50 to 650 that the macro first creates a new sheet with a single x column that goes from 50 to 650 in 0.1 increments. Then add 5 Y columns using the data from your raw data sheet where it matches the x value,

I do actually have a spreadsheet that does exactly that: 1 x column with 5 y columns. The data in this spreadsheet is entered manually, but it would be great if a macro could do this for me (like you said) for my other workbooks.

or fabricating the y value if there is no such x value on the raw data sheet. Where there are sequential fabricated y values, only the first one needs to be determined from the minimum of the two y values that straddle the gap, the others will all be equal to this first one.

Yes, I agree. This is a great idea. Thank you!

Perhaps you could provide more information on the exact layout of the raw data sheet .... what columns are the x-y pairs in and starting in what row, etc.? This will improve your chances of getting some help.

In the raw data, the 5 x-y pairs reside in columns AB, DE, GH, JK, MN respectively. Row 1 is a header, Row 2 has the column names. The data begins on Row 3.

I have an example workbook where the first tab is my raw data (as just described), the second tab is my manual alignments, and the third tab is the manual alignments with the x column consolidated (as you suggested). The ultimate goal is to generate an overlay of these 5 graphs, if the macro could mimic the output of either Tab 2 or Tab 3, then accomplishing this graph would be a piece of cake.

I'd love to post my workbook so you can see what I'm talking about, but this forum doesn't allow attachments. (Am I able to PM the workbook instead?) It would be so much easier to show spreadsheet rather than describing it or trying to format a table in HTML for this message. (HTML Maker isn't compatible with my machine because I'm on a mac, and the copy/paste with borders method is really cumbersome and doesn't always render correctly.)

Again, I am running Excel for Mac 2011. (I'm not sure if macros can run interchangeably between a Windows and a Mac OS X machine, or if they need to be specifically designed for one or the other.)

Please let me know if you need further details or clarification. I am happy to provide!

 

[JoeMo]

I don't use a Mac, but doubt that will cause any problems for what you want to do. If you will PM me, I will send you an email address where you can send a copy of your workbook and I'll try to come up with something for you.

 

[A1MBand]

Ok, thanks, will do!