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:

image_new.JPG


Expected output:
image_new_2.JPG

I have attached a xlsx file


test.xlsx

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/atta...2&d=1645419020

[007_guy]

You can use this file: http://www.vbaexpress.com/forum/atta...2&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.