Possible to append two ranges into one?

[Mightyginger]

I am using an interpolation function which takes as an input the range of the data to be interpolated. So lets says it looks like this:
Column/Row A B
1 1-Jul-10 10
2 1-Jul-11 20
3 1-Jul-12 30
etc

So I would submit the range A1:B3 but lets say that column B was really column C and I had to have data in column B which was unrelated and I didn't want input into the interpolation function. Is there any way of submitting a range to the function which is essentially range A1:A3 and C1:C3 glued together? Obivously I could just great another two columns on the spreadsheet somewhere and then point to columns A and C but wondered if there might be something smarter.

Thanks in adance.


Neil.

 

[GlennUK]

According to your other posting in this forum, the interpolate function takes different ranges for the X and Y values ...

=interpolate(B1:B8,C1:C8,D2) where the B column are my X values and C are my Y values

Why are you now wanting to pass a single range?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Mightyginger]

Ah. Well spotted!! Annoyingly there are two versions of the interpolation function. One does indeed take three inputs which is the X to find, X array, and Y array of values to be interpolated. Somewhat annoyingly that function only returns ONE X value. They then created another interpolation function which takes an array of Xs to find and then an array with Xs and Ys as one input. The reason for that is to allow a 2-D interpolation instead of a 1-D interpolation. Obviously when doing something like a cubic spline it's better to build the spline once and interpolate than have to build a new spline for every X value. Also, now I've encountered this problem I'm also curious to know if this can be done generally.

Thanks Glenn.

 

[xlbo]

This may work - as a test, create a range name with the definition:

=Sheet1!$A$1:$A$3,Sheet1!$C$1:$C$3

call it rng_Test and try passing that to the function rather than a hardcoded range

If that works, we can look at how to build the range dynamically...I'd check it myself but don't seem to have an interpolate fucntion....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Mightyginger]

Thanks Geoff but sadly didn't work. This particular interpolation function comes with Reuters so it's not an easy xll I can upload.

 

[Mightyginger]

For those following this I basically had to end up writing a VBA function which uses "Union" which I had never used before. The code ended up being pretty easy in the end so think I'll use this function to do the trick.

Code:

Function AppendRanges(inputRange1 as range, inputRange2 as range) as range
Set AppendRanges=Union(inputRange1, inputRange2)
End Function

 

[Mightyginger]

Actually. Is this right? It only seems to work if the two inputRanges are next to each other in the first place. So Ranges A1:A5 and B1:B5 work find but A1:A5 and C1:C5 don't when used as the input. Thoughts?

 

[combo]

In case of using the Union function:
- use YourRange.Areas with counter as argument to get each rectangle range in joined ranges,
- if two or more joined ranges form a rectangle area, they return one area.

combo

 

[Mightyginger]

Sorry. I am trying to get this to work by trying .Areas and .Areas.Count

Do you have a sec to give me an example for the code above please?

 

[combo]

Dim PartialRange As Range
For Each PartialRange In InputRange.Areas
    ' proceed with PartialRange
Next PartialRange

However, the question is: do you really need to join ranges? How much freedom do you have to design the function?Have you considered ParamArray declaration?
Can you pass processed variant instead? Now you have two ranges for two different variables. I'd transfer them into two variants and use for calculations. Alternatively I'd use dynamic array filled range by range.


combo

 

[Mightyginger]

The function is fixed. So I kinda have no option there and I am using that addin already for pricing feeds so it makes sense to use their interpolation addin too if possible. So might you be able to give an example of what you think might be the optimal way of handling sticking two ranges together please?

 

[xlbo]

tbh, the issue is less likely to be with sticking 2 ranges together (as that is relatively easy) and more to do with how the interpolation function uses the range(s) it is passed

your best bet might be to create an "InterpolationRange" area which is 2 columns side by side and use formulae to populate that range dynamically then pass that nice neat n tidy range to the function...e.g.


Data in A1:A3 and C1:C3

In Y1: =A1
In Z1: =C1

etc etc - of course these formulae could be dynamic and populated either using a helper cell to detemine the columns of data to pick up ro some code that puts the appropriate formulae in there - then simply pass the Y1:Z3 range to the interpolate function...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

I'd test if the function works with variant array. The size and values can be calculated from input ranges. Try to declare fixed simple array first and use it as input:

Dim v(1 To 2, 1 To 2)
v(1,1)=Dateserial(2010,7,1):v(1,2)=10
v(2,1)=Dateserial(2011,7,1):v(2,2)=20

If this fails and your function accepts only single range, you will have to build the input on the sheet. To simplify and automate the process you could follow Geoff's hint.

combo

 

[byundt]

The Union operator in Excel worksheet user interface is a pair of parentheses. It doesn't work in very many built-in functions, but it is worth trying with your Reuters function assuming that your VBA workaround is also working:
=Interpolate(B1:B5,(D110,G1:G10)) 'Note the parentheses that create the union of D110 with G1:G10

=AREAS(D110,G1:G10) returns an error message too few arguments
=AREAS((D110,G1:G10)) returns 2, the number of areas in the union

Brad

 

[Mightyginger]

Hi Brad. Sadly that didn't work. I am still struggling to union two seperate ranges that aren't immediately next to one another. Combo was kind enough to try and help but I've not manage to take his hints to the finished product so still don't have something that works. Any thoughts?

 

[SkipVought]

Is it possible to reconfigure your table to have the two ranges contiguous? It could even be a separate table, using MS Query to generate and use for source.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mightyginger]

Well I am using the interpolation function multiple times in the spreadsheet. I tihnk the simplest solution would be just to have an append function that I could pass to the function. I was hoping I might be able to use the Union fucntion as a neat solution but still struggling. Not sure why it only works for adjacent ranges but not otherwise. Any thoughts as how to get round this using Union please?

 

[combo]

You can generally consider some different approaches:
- if possible, rearrange the shape of input data from the beginning,
- combine ranges into one, using excel interface, with maximal automation. You have some tips in this thread,
- experiment with intermediate function.

You concentarted only on the last method. You need flexible input but the add-in function accepts only one area. The Union function is not applicable. As I explained, it returns one area, but only when ranges form bigger rectangle. It's not your case, unles you rearrange source data.
Look at the object browser and arguments declaration. Depending on their flexibility (variant, range, other) and number you can think about intermediate function or other solutions. I added some code that added to intermediate function can test what the target function accepts. Have you tested it?

Concluding, with the installed add-in:
- in VBE display the object browser, in the Project/Library drob down find the add-in library, find and select your function (classes/members) and analyse the declaration from the description at the bottom. If you like, copy it and show us. This will be helpful in building imput,
- in the meanime, test variant input. As posted above, the test function could be:
Function TestInput() as Variant
[tt]Dim v(1 To 2, 1 To 2)
v(1,1)=Dateserial(2010,7,1):v(1,2)=10
v(2,1)=Dateserial(2011,7,1):v(2,2)=20
TestInput=v
End Function[/tt]
and pass it as argument. If it works, you will have to concentrate on transfer ranges=>variant array. Again the Union will be useless.


combo