# how to sort & sum values of two different columns

##### New Member
hi,

i have a worksheet which contains data in 3 columns, viz. col A- date, col B - asset & col C - Sub Asset,
Excel Workbook
ABC
1DateAssetSub Asset
219/03/2010100
320-03-201020
421/03/2010225
522/03/201080
623/03/2010125
724-03-2010150
825-03-2010100
926/03/2010119
1027/03/2010160
1128/03/2010210
1229-03-2010340
1330-03-2010989
1431/03/201010
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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Airfix9

##### Well-known Member
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?

##### New Member
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

Last edited:

hi, any help pls

#### Airfix9

##### Well-known Member
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!

##### New Member
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

Replies
6
Views
464
Replies
5
Views
277
Replies
0
Views
79
Replies
4
Views
45
Replies
5
Views
140

1,191,683
Messages
5,987,991
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back