Tricky situation in excel (

[007_guy]

Hey guys, how are you?
I am trying to solve this tricky situation. I have two different TABS: OPERATIONS and DETAILS

OPERATION TAB has the following fields:



NUMBER
TYPE
DESCRIPTION
SUM_OF_MONEY




B0001100005429



FAC


SADADECO 19278294999








A0001100001230



REC


ORDONEZC9920 19299490733








B0001100005445



N/C


IGN_GONTAN 19266048459 19299490755








DETAILS TAB:


OPERATION_ID
AMOUNT
NUMBER




19278294999





4739








19299490733





9999








19266048459





34








19299490755





234









inside DESCRIPTION field there exist a 11-digit number which is the transaction number (i.e: 19278294999). DO NOTE that the same cell may contain more than one transaction (i.e: 19266048459 19299490755).

I want to achive this:

1) VBA SHOULD FIND EVERY TRANSACTION INSIDE "DESCRIPTION" CELL FROM TAB "OPERATIONS". IN THIS CASE THE FIRST ROW CONTAINS ONE TRANSACTION, ROW 2 CONTAINS ONE TRANSACTION AND ROW 3 CONTAINS 2 TRANSACTIONS


2) IT SHOULD COPY THE NUMBER FROM TAB "OPERATIONS" AND PASTE IT INSIDE COLUMN "NUMBER" FROM TAB "DESCRIPTION" (only do this if it is FAC or N/C) AND THEN, IT HAS TO DO THE SUMATORY IN TAB "OPERATIONS". TAKE A LOOK:

29431


Expected output:
29433

I have attached a xlsx file


29432

Can help me?

Thanks!

[snb]

Create a proper database:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion
sp = Sheet2.Cells(1).CurrentRegion
ReDim sq(100, 4)

sq(0, 0) = sn(1, 1)
sq(0, 1) = sn(1, 2)
sq(0, 2) = sn(1, 3)
sq(0, 3) = "Operation_ID"
sq(0, 4) = "Amount"
n = 1

For j = 2 To UBound(sn)
st = Split(sn(j, 3))
For jj = 1 To UBound(st)
sq(n, 0) = sn(j, 1)
sq(n, 1) = sn(j, 2)
sq(n, 2) = st(0)
sq(n, 3) = st(jj)

For jjj = 2 To UBound(sp)
If CStr(sp(jjj, 1)) = st(jj) Then Exit For
Next
sq(n, 4) = sp(jjj, 2)
n = n + 1
Next
Next

Sheet1.Cells(1, 8).Resize(UBound(sq), 5) = sq
End Sub

[Paul_Hossler]

Seems like MS Access would be better for something like this

[007_guy]

Hey pal, thanks for replying.
I am having problem: i am having this error message "VBA OBJECT REQUIERED".

[007_guy]

Hey pal, thanks for replying.
I am having problem: i am having this error message "VBA OBJECT REQUIERED".

Hey Pal
Could you please help me to solve this in VBA?

[Bob Phillips]

You can do this in VBA, but as snb says you should set it up as proper tables, one id per row, not multiples.

[007_guy]

You can do this in VBA, but as snb says you should set it up as proper tables, one id per row, not multiples.

Hey pal, thank you for replying.
How? i am not getting the idea. What should i do?

[snb]

You didn't use the file you posted.

Alternative code (functionally equivalent):


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion.Resize(, 5)
sp = Sheet2.Cells(1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sp)
.Item(CStr(sp(j, 1))) = sp(j, 2)
Next
.Item(.Count) = Array(sn(1, 1), sn(1, 2), sn(1, 3), "Operation_ID", "Amount")

For j = 2 To UBound(sn)
st = Application.Index(sn, j)
sq = Split(sn(j, 3))
For jj = 1 To UBound(sq)
st(3) = sq(0)
st(4) = sq(jj)
st(5) = .Item(sq(jj))
.Item(.Count) = st
Next
Next
For j = 1 To UBound(sp)
.Remove CStr(sp(j, 1))
Next

Sheet1.Cells(1, 8).Resize(.Count, 5) = Application.Index(.items, 0, 0)
End With
End Sub

[007_guy]

You didn't use the file you posted.

Alternative code (functionally equivalent):


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion.Resize(, 5)
sp = Sheet2.Cells(1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sp)
.Item(CStr(sp(j, 1))) = sp(j, 2)
Next
.Item(.Count) = Array(sn(1, 1), sn(1, 2), sn(1, 3), "Operation_ID", "Amount")

For j = 2 To UBound(sn)
st = Application.Index(sn, j)
sq = Split(sn(j, 3))
For jj = 1 To UBound(sq)
st(3) = sq(0)
st(4) = sq(jj)
st(5) = .Item(sq(jj))
.Item(.Count) = st
Next
Next
For j = 1 To UBound(sp)
.Remove CStr(sp(j, 1))
Next

Sheet1.Cells(1, 8).Resize(.Count, 5) = Application.Index(.items, 0, 0)
End With
End Sub


Hey pal, thanks for replying.
I am using your code but it keeps throwing the same message: "an object is required"

Could you please upload the file you tried this code?

[Bob Phillips]

AT the least, yoiu should have two rows for B0001100005445 not just one.

[007_guy]

AT the least, yoiu should have two rows for B0001100005445 not just one.
You mean 2 different ROWS for ids: 19266048459 19299490755 ?
Is there any way to make it work bu just using 1 single row?

[snb]

You can use this file: http://www.vbaexpress.com/forum/attachment.php?attachmentid=29432&d=1645419020

[007_guy]

You can use this file: http://www.vbaexpress.com/forum/attachment.php?attachmentid=29432&d=1645419020

I downloaded this file but when i put your code it seems not be working because it says: "ERROR: OBJECT IS REQUIRED"

[snb]

Do you know what VBA stands for ?
Do not use any Apple.

[007_guy]

Do you know what VBA stands for ?
Do not use any Apple.

Yes but it seems not to be working in my sheet :/

[snb]

Please post the file in which you copied the macro I posted.