Hi all of you here,

I am newcomer. I have one problem i need your help. I have a sheet with the data below

TABLE 1 TABLE 2
INDEX Q'TY PRICE TOTAL INDEX Q'TY TOTAL AVG
1 100 2 200 12 SUM(B3+B4) SUM(D3+D4)
2 200 3 600 13 SUM(B3+B5) SUM(D3+D5)
3 300 4 1,200 14 SUM(B3+B6) SUM(D3+D6)
4 400 5 2,000 15
5 500 6 3,000 23
24
25
34
35
123 SUM(B3+B4+B5) SUM(D3+D4+D6)

Problem is to solve as
The function in column "Q'TY" in table 2 will lookup the number (value) in colume "INDEX" in table 2 to colume "INDEX" in table 1
then calculate the quantity. In my case as number 12 in colum "INDEX" in table 2 is a combination between number 1 and 2, so SUM(B3+B4)

Similarly to the culume "TOTAL" in table 2

Please, anyone can help to solve this function, VBA, or macro

Thanks in advance!

Hi

Hope all is fine. Took a look at that issue. One way to handle this issue would be to use a combination of the INDEX and match functions

The INDEX function returns an item from a specific position in a list
The MATCH function returns the position of a value in a list.

The 2 functions can be used together, as a way for extracting data from a table.

For your specific problem (with the same workbook you uploaded..)

Please paste the following forumula to CELL G3
=INDEX($B$3:$B$7,MATCH(VALUE(LEFT(F3,1)),$A$3:$A$7,0))+INDEX($B$3:$B$7,MATCH(VALUE(MID(F3,2,1)),$A$3:$A$7,0))

Please paste the following forumula to CELL G4
=INDEX($B$3:$B$7,MATCH(VALUE(LEFT(F4,1)),$A$3:$A$7,0))+INDEX($B$3:$B$7,MATCH(VALUE(MID(F4,2,1)),$A$3:$A$7,0))


Hopefully these 2 forumulas can provide a base for you to work with.

The forumula only handles the situation where there are 2 digits in the input index value. So you need to add in handling for the case for cases where there the INDEX is say 100 or greater. Also, if it is less than 10 (INDEX is a single digit) you will have to add in some checking..

But I hope the basic setup will get you going.

Also, hopefully someone else will post up a more efficent solution to this.

Thanks
Al

hello,

tks for your help. It solved them well although it is just for 2 digits only.

but that is base for more than 3 digits.!

Tks alot

Hi,

Here is this macro code :

Code:

Sub calcu() Rem Www.ExcelVBA.Net ( From Turkish Republique ) Rem 02-06-2009 Rem Tarkan VURAL Dim i As Long, s As Worksheet, a As Long, x As Integer Dim F As Long, valF As Long, valT As Long Set s = Sheets("sheet1") a = s.Range("f65536").End(3).Row For i = 3 To a Select Case CInt(Len(s.Cells(i, "f").Value)) Case Is = 1 F = Mid(s.Cells(i, "f").Value, 1, 1) Case Is = 2 For x = 1 To 2 F = CInt(Mid(s.Cells(i, "f").Value, x, 1)) FF = s.Range("a3:a" & a).Find(F, , , 1) Select Case FF Case Is = F valF = CLng(valF + Cells(s.Range("a3:a" & a).Find(F, , , 1).Row, 2).Value) valT = CLng(valT + Cells(s.Range("a3:a" & a).Find(F, , , 1).Row, 4).Value) Case Else End Select Next x Case Is = 3 For x = 1 To 3 F = CInt(Mid(s.Cells(i, "f").Value, x, 1)) FF = s.Range("a3:a" & a).Find(F, , , 1) Select Case FF Case Is = F valF = CLng(valF + Cells(s.Range("a3:a" & a).Find(F, , , 1).Row, 2).Value) valT = CLng(valT + Cells(s.Range("a3:a" & a).Find(F, , , 1).Row, 4).Value) Case Else End Select Next x Case Else End Select s.Cells(i, "g").Value = valF s.Cells(i, "h").Value = valT valF = 0 valT = 0 Next i MsgBox "Calculate complete ... ", vbInformation, "Www.ExcelVBA.Net" i = Empty: a = Empty: x = Empty Set s = Nothing valF = Empty: F = Empty: FF = Empty End Sub

Good luck...

Hi !
Hope all is going fine. Thanks alot for that !

Best of luck and bye for now.
Al

PS: this site was moved over to http://www.sqldrill.com See you, Al