how to sort & sum values of two different columns

[priyadarshini]

hi,

i have a worksheet which contains data in 3 columns, viz. col A- date, col B - asset & col C - Sub Asset,

Excel Workbook
	A	B	C
1	Date	Asset	Sub Asset
2	19/03/2010	100
3	20-03-2010		20
4	21/03/2010	225
5	22/03/2010		80
6	23/03/2010		125
7	24-03-2010		150
8	25-03-2010		100
9	26/03/2010		119
10	27/03/2010		160
11	28/03/2010		210
12	29-03-2010		340
13	30-03-2010	989
14	31/03/2010		10
15
Sheet6

Excel 2007



In this data of hundred of inputs, daily i have to find and match the total of sub asset to asset, manually, for eg. if Asset is 100, then in Sub asset column i have to locate the values which match to 100 in Asset Column. & then i have to sort them as per their matches.

can u help me with some index, match , sumif etc. which a novice like me would understand, if applicable to this particular worksheet please... im using windows xp & excel 2007 verison.

will be grateful for all ur kind help

 

[Airfix9]

Looking at your data, how do you know that the 100 in Asset is related to the 20 on 20/3 and the 80 on 22/3 and not the 100 on 25/3? What is there to differentiate these Sub Asset amounts from each other. What happens if the two columns do not total the same value? What if one Asset doesn't have enough in Sub Asset to match it?

In short, can you please provide more detail of the logic behind the question?

 

[priyadarshini]

Looking at your data, how do you know that the 100 in Asset is related to the 20 on 20/3 and the 80 on 22/3 and not the 100 on 25/3? What is there to differentiate these Sub Asset amounts from each other. What happens if the two columns do not total the same value? What if one Asset doesn't have enough in Sub Asset to match it?

In short, can you please provide more detail of the logic behind the question?

hi, ideally the nearest values in sub asset should be matched with values in Asset, say.. for 100 in Asset it could be 20 on 20/3 & 80 on 22/03 but it will make no difference if its 100 on 25/03, because it will reduce my search.
The values which do not total the same value, will go unmatched and those will be the values in Asset & Sub Asset, im looking for following up.

So basically, i am trying to locate the unmatched entries in both the columns.

Thanks for ur response, airfix9

 

[priyadarshini]

hi, any help pls

 

[Airfix9]

I had a session where I attempted to solve it over the weekend but I am hitting too many variables to get a solid answer for you. I suspect that the data is considerably longer than the example that you showed me and, without more detail on how to assign sub assets to assets, the whole thing becomes more and more complex.

Sorry, have to admit that this one has me stumped!

 

[priyadarshini]

I had a session where I attempted to solve it over the weekend but I am hitting too many variables to get a solid answer for you. I suspect that the data is considerably longer than the example that you showed me and, without more detail on how to assign sub assets to assets, the whole thing becomes more and more complex.

Sorry, have to admit that this one has me stumped!

Tx Airfix9 for all your efforts, it was really kind of you to devote time on my query over the weekend.

Meanwhile, i have tried this code,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim Rng As Range, Num As Integer, Ray, Top As Integer, Bot As Integer<o></o>
Dim SumNum<o></o>
On Error Resume Next<o></o>
Num = Application.InputBox(prompt:="Please Select ", Title:="Insert Brackets", Type:=1)<o></o>
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))<o></o>
Ray = Rng.Value<o></o>
<o></o>
For Top = 1 To UBound(Ray)<o></o>
For Bot = Top + 1 To UBound(Ray)<o></o>
If Ray(Top, 1) + Ray(Bot, 1) = Num Then<o></o>
SumNum = SumNum & Num & " / " & Ray(Top, 1) & " / " & Ray(Bot, 1) & Chr(10)<o></o>
End If<o></o>
Next Bot<o></o>
Next Top<o></o>
If IsEmpty(SumNum) Then<o></o>
MsgBox " No Value Found"<o></o>
Else<o></o>
MsgBox "Number ,Val (1) ,Val (2)" & Chr(10) & SumNum<o></o>

End If
But in this code, there is a catch that,
1. i put in the required asset / value manually, & then it gives only two sub assets matches. Can we modify this code for extending more matches say 4-5 or more if possible.
2. its not giving matches for values which are in 4 figures or decimals.
over to you.....
regards
priyadarshini