View Full Version : searching for and subtracting values

wakwak1

01-06-2008, 05:34 PM

I've got certain percentage values going downwards in column J, with varying amounts of blank fields. I also have a similar situation in column W. I would like to write a macro that will scan through column J and W and then to output (in say column L) the difference between the first two values, 2nd values, etc.)

So for eg, if the first few values in column J (going downwards) were 10%,blank,blank,5%,1% and in column W 9%, 2%,blank, 3% , then the output in L should be 1%,3%, -2%

Thanks.

mikerickson

01-06-2008, 06:55 PM

This UDF might help you.

pairDiff takes three arguments, two ranges and an index

pairDiff returns the difference between the (index)'th value in firstRange and the (index)'th value found in secondRange. It skips text and blanks, but counts dates and logical as numbers.

If there are fewer than (index) numeric entries in either of the ranges, it returns the #REF error.

Putting this in L1 and filling down should meet your need.

=pairDiff(J:J,W:W,ROW())

Function pairDiff(ByRef firstRange As Range, ByRef secondRange As Range, ByVal index As Long) As Variant

Dim spareIndex As Long, xVal As Variant

Dim firstRRay As Variant, firstNum As Double

Dim secondRRay As Variant, secondNum As Double

Set firstRange = Application.Intersect(firstRange, firstRange.Parent.UsedRange)

If firstRange Is Nothing Then pairDiff = CVErr(xlErrNA): Exit Function

firstRRay = firstRange.Value

secondRRay = secondRange.Resize(firstRange.Rows.Count, firstRange.Columns.Count).Value

spareIndex = index

For Each xVal In firstRRay

If xVal <> vbNullString And IsNumeric(xVal) Then

firstNum = CDbl(xVal)

index = index - 1

If index = 0 Then Exit For

End If

Next xVal

If index <> 0 Then pairDiff = CVErr(xlErrRef): Exit Function

For Each xVal In secondRRay

If xVal <> vbNullString And IsNumeric(xVal) Then

secondNum = CDbl(xVal)

spareIndex = spareIndex - 1

If spareIndex = 0 Then Exit For

End If

Next xVal

If spareIndex <> 0 Then pairDiff = CVErr(xlErrRef): Exit Function

pairDiff = firstNum - secondNum

End Function

I've a feeling there's a spreadsheet solution, but I can't find it.

wakwak1

01-06-2008, 10:38 PM

Thanks heaps for that reply!

I have a different problem now, so i thought i'd keep it in this forum:

I've got two .xls files (call them form.xls and data.xls). In column B of form.xls, I need it to search in column P of data.xls where it will be looking for a certain time value, say 3:00:00 PM. When it finds this time value, it needs to fetch a number from column T of data.xls (let's say it was from T20). That number will then be inputted in cell B3 of form.xls. In addition, cell C3 of form.xls will take the value of the number in T19 of data.xls, D3(form) and T18(data), E3(form) and T17(data) etc, and so on up to K3 and T11. Then L3 will correspond with T6 (i.e. we skipped 5 from T11) and M3 with T(-13) *** [This is just to illustrate the relationship, negative rows don't exist].

The macro will keep searching for further instances of 3:00:00 PM and put successive matches in B4,B5, etc. whilst following the rules described previously (fetching the associated numbers, etc). To top it all off, when the macro finds the 3:00:00 PM value in column P of data.xls, it would be nice if it could also pull the date which is in the same row but in column O (of data.xls). Then the A column in form.xls would be populated with the matching date values.

So it boils down to searching one .xls for a certain date, fetching associated data and then pulling that data back into a different .xls

I'd appreciate any input or provide any further clarification. Thanks!!!

unmarkedhelicopter

01-07-2008, 01:46 AM

I've got certain percentage values going downwards in column J, with varying amounts of blank fields. I also have a similar situation in column W. I would like to write a macro that will scan through column J and W and then to output (in say column L) the difference between the first two values, 2nd values, etc.)

So for eg, if the first few values in column J (going downwards) were 10%,blank,blank,5%,1% and in column W 9%, 2%,blank, 3% , then the output in L should be 1%,3%, -2%

Thanks.This doesn't make sense ...

J W L (yes out of order)

10% 9% 1%

- 2% 3% (where from ?)

- - (0% or should this be blank ?)

5% 3% -2%

1% - (shouldn't -1% go here ?)

Where does the bold 3% come from ?

Rather than a macro you should use a formula and drag it down.

Why do you want to use a macro ?

unmarkedhelicopter

01-07-2008, 01:48 AM

No, you should start a new thread for each new problem.

mikerickson

01-07-2008, 07:04 AM

I interpreted the question as

10 is the first non-blank in J, 1 is the first non-blank in W, 9 is the first result.

5 is the second non-blank in J, 2 is the second non-blank in W, result 3

1 is the third non-blank in J, 3 is the third non-blank in W, result -2.

unmarkedhelicopter

01-07-2008, 09:10 AM

Mike it obviously did what he wanted, so kudos to you, but that ain't what he said ! So, again, kudos to you for working it out. :)

wakwak1

01-07-2008, 01:25 PM

thanks micerickson, that's what i was after. Sorry if it was a bit confusing.

I will post the other question in another thread, thanks everyone!

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.