+ Reply to Thread
Results 1 to 55 of 55

Merging columns, excluding columns, and calculating columns by division over another

  1. #1
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Merging columns, excluding columns, and calculating columns by division over another

    hi
    I would like macro to merge a group of columns from the other sheets In STTO sheet, the values shown in column F will be the summation of values for the same column (F) from the second and third sheets . As for column (G) , the summing process will just be from the fourth sheet of column( F). As for column (J), the summing process will be done from the second sheet for column( I) and third sheet for columns (H).As for column K, the collection process will take place from the second sheet for column (J) and fourth sheet for column (H). After doing all these steps, the values of the two columns(H,I) in sheet STTO will be extracted by dividing the columns by each other. I have put the formulas into them . so that the extraction method is done, but I do not want any formula in the output STTO sheet .
    when run the macro should clear data from before brings data to update any changing , and just I want brings data without formatting (I will do that manually to avoid code slowness)
    last thing if the macro could deal with 8000 rows will be great .

    for instance AA123 will summing for two columns F for sheets ENTERING & BTY
    so sheets ENTERING the total after summing =1270
    sheets BTY the total after summing =60
    so the column F in STTO =1270+60=1330
    as to column F for sheet STY the total after summing =170
    so the column G for sheet STTO= 170
    sheets ENTERING the total for column I after summing =194000
    sheets BTY the total for column H after summing =7200
    so the column J in STTO =194000+7200=201200
    sheets ENTERING the total for column J after summing =194600
    sheets STY the total for column H after summing =28700
    so the column K in STTO =194600+28700=223300
    finally should extract values for columns H,I for sheet STTO by H=J/F & I=K/G
    not always what show in ENTERING sheet should also be in sheet BTY .

    also posted here
    https://www.mrexcel.com/board/thread...heets.1228454/
    I hope there is no error and very clear .
    Attached Files Attached Files
    Last edited by Alaa-A; 02-01-2023 at 01:38 PM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: Merging columns, excluding columns, and calculating columns by division over another


    Hi,

    issues with your attachment :

    1. Column F has some text values instead of only numeric values : so the VBA procedure may crash or the result should be wrong …

    2. It seems the SSTO column K has wrong results …

    As I won't write any codeline without a correct & exact attachment,
    maybe the reason why you did not receive any help on the other forum …

  3. #3
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Column F has some text values instead of only numeric values
    what do you mean ,pleas?
    do you mean number formatting ?
    It seems the SSTO column K has wrong results …
    yes you're right , sorry!
    I edited the file .
    Last edited by Alaa-A; 02-01-2023 at 10:24 AM.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    Not the number formatting but the data itself ‼
    But with the number formatting you must see so format the worksheet BTY column F without any decimal …

  5. #5
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Greetings to all
    Try the following variant:

    By the way, don't worry about the format: you only have to give it to the table once.
    Attached Files Attached Files
    Last edited by beyond Excel; 02-01-2023 at 07:41 PM.
    You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: Merging columns, excluding columns, and calculating columns by division over another


    Hi beyond !

    According to my post #2 point #1 your code crashes as expected according to my Windows Regional Settings … JaJaJa ‼

  7. #7
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Quote Originally Posted by Marc L View Post

    Hi beyond !

    According to my post #2 point #1 your code crashes as expected according to my Windows Regional Settings … JaJaJa ‼
    Odd... I tried it by changing the locale and it worked fine on both settings.

    It has to be something else...

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    No, exactly the same issue with a text with dot instead of a numeric value when the Regional Settings does not use a dot as decimal separator …
    Your crashing codeline is not inside an On Error Resume Next block.

    Hoping the OP asked it for a training but not for any professional context
    as it's far better for him to learn to see data errors than using On Error VBA procedure which can hide others issues !

    As an easy workaround is to just check the data type rather than any On Error …
    Last edited by Marc L; 02-01-2023 at 12:53 PM.

  9. #9
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    you must see so format the worksheet BTY column F
    this is really strange
    after delete decimal some values keep decimal !
    but I don't see this problem for the others columns contain values .
    so you mean this column for sheet BTY can cause problem?

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    Not strange as I explained those cells are not numeric but text ‼
    Like you can check yourself with the worksheet functions ISNUMBER or ISTEXT …
    Another way is to enlarge the column when cells have the General format : cells aligned to left are text, cells aligned to right are numeric.

    So just correct these cells : just manually enter a numeric value in order to overwrite the text …
    Last edited by Marc L; 02-01-2023 at 01:52 PM.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    According to your last updated attachment a VBA demonstration for starters :

    PHP Code: 
    Sub Demo1()
            
    Dim S&, W(2 To 4), R&, N&, C%, V()
            
    Sheets(5).UsedRange.Offset(1).Clear
        With 
    New Collection
        
    For 2 To 4
            W
    (S) = Sheets(S).[A1].CurrentRegion
        
    For 2 To UBound(W(S))
            For 
    1 To .Count
                C 
    StrComp(.Item(N), W(S)(R2), 1)
             If 
    >= 0 Then
                
    If C Then .Add W(S)(R2), , N
                
    Exit For
             
    End If
            
    Next
                
    If > .Count Then .Add W(S)(R2)
        
    Next RS
            
    For 1 To .Count:  .Add R, .Item(R), R:  .Remove R 1:  Next
            ReDim V
    (1 To .Count1 To 11)
        For 
    2 To 3
        
    For 2 To UBound(W(S))
            
    = .Item(W(S)(N2))
            If 
    IsEmpty(V(R1)) Then V(R1) = R: For 2 To 5V(RC) = W(S)(NC): Next
            V
    (R6) = V(R6) + W(S)(N6)
            
    V(R10) = V(R10) + W(S)(N- (2))
            If 
    2 Then V(R11) = V(R11) + W(S)(N10)
        
    Next NS
        
    For 2 To UBound(W(4))
            
    = .Item(W(4)(N2))
            
    V(R7) = V(R7) + W(4)(N6)
            
    V(R11) = V(R11) + W(4)(N8)
        
    Next
        
    For 1 To .Count
        
    For 0 To 1
            
    If V(RCThen V(RC) = V(R10 C) / V(RC)
        
    Next CR
        End With
        With Sheets
    (5).Range("A1:K" R).Columns
            
    .Borders.Weight 2
            
    .Font.Name = .Cells(1).Font.Name
            
    .Item("A:G").NumberFormat "_W# ###_W;;; @ "
            
    .Item("H:K").NumberFormat "_W# ##0.00_W;;; @ "
             
    With .Rows("2:" R):  .Font.Size 12:  .Value V:  End With
            
    .AutoFit
        End With
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  12. #12
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Sorry Marc: Error 13 due to lack of correction of the Regional Configuration theme (lol The hunter hunted).
    _________________________

    Alaa-A: And how did it go with the proposal in post #5?...
    Last edited by beyond Excel; 02-01-2023 at 03:36 PM.

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    No problemo !

    An easy workaround : If Not IsNumeric …

  14. #14
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Quote Originally Posted by Marc L View Post

    No problemo !

    An easy workaround : If Not IsNumeric …
    My dear Marc (with "c", Jajaja ): The IsNumeric function (just like IsDate with dates) is not foolproof with numbers either. For example, the following two cases cannot both be TRUE for the same configuration:

    PHP Code: 
    Sub Test1()
    MsgBox "a) 1.234,56 : " IsNumeric("1.234,56") & vbLf vbLf _
      
    "b) 1,234.56 : " IsNumeric("1,234.56")
    End Sub 
    But Excel detects the difference.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    As both returns False on my side but yes in some case different option could be necessary like CCur or Evaluate …

  16. #16
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Quote Originally Posted by Marc L View Post
    As both returns False on my side but yes in some case different option could be necessary like CCur or Evaluate …
    I changed my settings to French and in the following example one case gives me TRUE and the other FALSE:

    PHP Code: 
    Sub Test1()
    MsgBox "a) 1 234,56 : " IsNumeric("1 234,56") & vbLf vbLf _
      
    "b) 1 234.56 : " IsNumeric("1 234.56")
    End Sub 

  17. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    Yes, the first returns True and "10.00" returns False on my side.

    An obvious Excel basics workaround is If Application.IsText …

  18. #18
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    I think I know why your proposal doesn't work for me and mine doesn't work reciprocally for you.

    I changed this part:

    PHP Code: 
    For 2 To Q
      
    If InStr(a(i6), ".") > 0 Then a(i6) = CDbl(Replace(a(i6), "."sDec))
    Next 
    and it is working for me in the 3 configurations: Spanish, French and USA.
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    Yes but, according to a professional context the main question is « Why such case ? »
    It's not always the best way to apply directly a workaround without notifying it …
    I met several cases such workaround codes lead to a mess or even a worse situation for the users.

  20. #20
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Yes, but in a professional context it would be preferable to seek an explanation to the following question: How is it possible that the same number (ten) appears in the same column as a number and as a text?...

    It is not a normal or usual situation! and the cause of this must be found.

  21. #21
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    .
    Let's go Alaa-A!...

    You have solutions in posts #5 and #11 and you have hardly commented anything: Do they work for you or do they not work for you?...

    .

  22. #22
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    you have hardly commented anything: Do they work for you or do they not work for you?...
    sorry buddy!
    it's pefect.
    but to back Marc's speech about On Error Resume Next
    I disabled and shows error invalid procedure call or argumen in this line
    Please Login or Register  to view this content.
    just I want to inform you and hope to doesn't affect for project working.

  23. #23
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    @Marc seem to work perfectly.
    ok I will more time to check every thing is ok and I will clean the cells from text to mark solve this thread .
    I will come back to inform you .
    thanks .

  24. #24
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    On Error is needed for this piece of code 'cause of the way beyond used a VBA Collection.
    I was stating about On Error not for the Collection - as you can see within my code my Collection way does not need it neither -
    but to sum the data in case of a text cell instead of a numeric value …

    Thanks for the rep' !

  25. #25
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Quote Originally Posted by Alaa-A View Post
    sorry buddy! it's pefect.
    Are you absolutely sure you are using the variant call CMM_3.xlsb‎ ?

  26. #26
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Are you absolutely sure you are using the variant call CMM_3.xlsb‎ ?
    ofcourse two files I tested .

  27. #27
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    I am going to explain to you the reason why the 'On Error Resume Next' is used temporarily in the following code passage:

    PHP Code: 
      On Error Resume Next
        R 
    0nc(a(i2))
      
    On Error GoTo 
    You will have seen in the Forums that two objects widely used to store information are: Dictionary and Collection, right?...
    These objects are not the same: in some cases it is convenient to use the Dictionary and in other cases (for example with a lot of information to handle) it is convenient to use the Collection.

    One of those differences between them resides in the way in which the programmer finds out if a piece of data is stored in the object or not:

    -> With the Dictionary 'Exists' is used, for example:

    Please Login or Register  to view this content.
    -> Collection doesn't have that method so you can 'call' the data directly:

    Please Login or Register  to view this content.
    If the data exists in the collection, 'R' will take that value.
    But if the data does NOT exist, your code will stop and give you an error.

    Now do you see why it is so important to temporarily use the 'On Error Resume Next' for this check?...
    Last edited by beyond Excel; 02-01-2023 at 10:01 PM.

  28. #28
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Quote Originally Posted by Alaa-A View Post
    ofcourse two files I tested .
    So I take this opportunity to ask you: Did both versions work well for you or only version '3'?...

    From what you will comment, we will know -in the future- whether or not you have Regional Configuration problems on your PC.

  29. #29
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Re: Merging columns, excluding columns, and calculating columns by division over another


    Quote Originally Posted by Alaa-A View Post
    last thing if the macro could deal with 8000 rows will be great .
    According to a speed concern with such bigger data than your attachment
    as I create the Collection to be directly sorted - slower than beyond Excel unsorted Collection direct way -
    so this codeline creating the indexes once the Collection is sorted
    Please Login or Register  to view this content.
    could be simplified as :

    PHP Code: 
    For 1 To .Count:  .Add R, .Item(R):  Next 

  30. #30
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Well, well... If the idea is to have the collection ordered alphabetically then we already saw the best way to do it a short time ago. Here's the adequacy of that:

    PHP Code: 
    Private Sub nc_Ordered()
    Dim cb As MSForms.ComboBoxiWSkaQ&, i&
    Rem ---------------------------\
    Set cb tbl.Range.Parent.ComboBox1cb.List = Array("")
    Rem ---------------------------\
    For 
    Each iWS In Array(Sheets("ENTERING"), Sheets("BTY"), Sheets("STY"))
      
    iWS.Cells(1).CurrentRegion.Columns(2): UBound(a)
      For 
    2 To Q
        k 
    Application.Match(a(i1), cb.List)
        If 
    cb.List(1) < a(i1Then cb.AddItem a(i1), k
      Next
    Next
    Rem 
    ---------------------------\
    For 
    1 To cb.ListCount 1nc.Add iCStr(cb.List(i)): Next
    Rem 
    ---------------------------\
    cb.ClearSet cb Nothing
    End Sub 
    I don't notice any loss of efficiency...
    Attached Files Attached Files

  31. #31
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    thanks for explanation and clarification.

    So I take this opportunity to ask you: Did both versions work well for you or only version '3'?...
    yes, two both works greatly when the error handle procedure is enabled .
    Just a question regarding mapping the array to the numbers 1000,5000
    These numbers refer to the number of rows that data is supposed to contain, if I'm not mistaken
    But the question is why didn't you choose the same number ,why they are different?
    And if I'm wrong, would you please clarify that for me?
    I will check the last version.
    thanks again
    Last edited by Alaa-A; 02-02-2023 at 03:59 AM.

  32. #32
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    @Marc thanks again to make the code seems better and fast

  33. #33
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Merging columns, excluding columns, and calculating columns by division over another


    Quote Originally Posted by beyond Excel View Post
    Well, well... If the idea is to have the collection ordered alphabetically then we already saw the best way to do it a short time ago.
    Well, well, should be tested with more than 30K rows and at least 2000-3000 unique references before such assertion …
    And I have the option to create the indexes in a secondary Collection.
    If the data are already sorted or do not need to be sorted so your original direct way is the more appropriate …


    Quote Originally Posted by Alaa-A View Post
    @Marc thanks again to make the code seems better and fast
    Did you test on your real bigger workbook ?

  34. #34
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    @beyond Excel
    last version seems to be slow for about 10000 - 15000 across sheets gives 14.00 sec
    but in post #5 gives 1.3 sec

  35. #35
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Well: between your verification, @Alaa-A, and @Marc's observation, it seems to me that you have to opt for 1.3 sec, right?

  36. #36
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    it seems to me that you have to opt for 1.3 sec, right?
    that's right buddy
    just may you answer me about array in post#31,please?
    just I want I understand what I think

  37. #37
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    @Marc I think your code too slow for the same size of data which I tested with beyond's code
    but I'm not sure what's wrong . I will come back about it .

  38. #38
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    Yes without sorting the Collection that must be faster so I could revamp my demonstration but should be close to beyond's way …

  39. #39
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Suddenly I remembered that two of the fastest Excel methods ("pure Excel", no more) are: 'RemoveDuplicates' and 'Sort'.

    So here we go with this version:

    PHP Code: 
    Private Sub nc_Ordered()
    Dim iWSQ&, i&
    Rem ---------------------------\
    With tbl.Range.Worksheet
      
    .Range("AA1").CurrentRegion.Clear
      
    .Range("AA1") = "Ref"
      
    For Each iWS In Array(Sheets("ENTERING"), Sheets("BTY"), Sheets("STY"))
        
    iWS.Range("B2"iWS.Range("B1").End(xlDown)).Copy .Cells(Rows.Count"AA").End(xlUp)(2)
      
    Next
      
    .Range("AA1").CurrentRegion.RemoveDuplicates Columns:=1Header:=xlYes
      
    .Range("AA1").CurrentRegion.Sort .Range("AA1"), 1Header:=xlYes
      a 
    = .Range("AA1").CurrentRegionUBound(a)
      .
    Range("AA1").CurrentRegion.Clear
    End With
    Rem 
    ---------------------------\
    For 
    2 To Qnc.Add i 1a(i1): Next
    End Sub 
    Attached Files Attached Files

  40. #40
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Quote Originally Posted by Alaa-A View Post
    just may you answer me about array in post#31,please?
    That passage of VBA code has already "gone to history": it is no longer used!

    Now we are with the version of post #39.

  41. #41
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Try this ...


    Quote Originally Posted by Alaa-A View Post
    I think your code too slow
    According to your initial post updated attachment a new VBA demonstration
    where you can remove the Sort codeline if you do not need to sort the REF column :

    PHP Code: 
    Sub Demo2()
            
    Dim WV(), N&, R&, C%
            
    Sheets(5).UsedRange.Offset(1).Clear
            W 
    Sheets(2).[A1].CurrentRegion
            ReDim V
    (1 To UBound(W), 1 To 11)
        
    With New Collection
            On Error Resume Next
        
    For 2 To UBound(W)
            
    Err.Clear
            R 
    = .Item(W(N2))
            If 
    Err.Number Then R = .Count 1: .Add RW(N2): V(R1) = R: For 2 To 5V(RC) = W(NC): Next
            V
    (R6) = V(R6) + W(N6)
            
    V(R10) = V(R10) + W(N9)
            
    V(R11) = V(R11) + W(N10)
        
    Next
            On Error 
    GoTo 0
            W 
    Sheets(3).[A1].CurrentRegion
        
    For 2 To UBound(W)
            
    = .Item(W(N2))
            
    V(R6) = V(R6) + W(N6)
            
    V(R10) = V(R10) + W(N8)
        
    Next
            W 
    Sheets(4).[A1].CurrentRegion
        
    For 2 To UBound(W)
            
    = .Item(W(N2))
            
    V(R7) = V(R7) + W(N6)
            
    V(R11) = V(R11) + W(N8)
        
    Next
            On Error Resume Next
        
    For 1 To .Count
            V
    (R8) = V(R10) / V(R6)
            
    V(R9) = V(R11) / V(R7)
        
    Next
            On Error 
    GoTo 0
        End With
            Application
    .ScreenUpdating False
        With Sheets
    (5).Range("A1:K" R).Columns
            
    .Borders.Weight 2
            
    .Font.Name = .Cells(1).Font.Name
            
    .Item("A:G").NumberFormat "_w#,###_W;;; @ "
            
    .Item("H:K").NumberFormat "_w#,##0.00_W;;; @ "
             
    With .Rows("2:" R):  .Font.Size 12:  .Value V:  End With
            
    .AutoFit
            
    .Item("B:K").Sort .Item(2), 1Header:=True
        End With
            Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 02-04-2023 at 02:21 AM.

  42. #42
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    That passage of VBA code has already "gone to history": it is no longer used!

    Now we are with the version of post #39.
    OK as you like.


    this version is not better than in post# 5 with huge data .(Its speed decreases when there is a duplicate element of the same class in the same sheet per sheet not like in post#5) .
    thanks for your all theses tries to reach to better version .

  43. #43
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    thanks Marc
    it perfect with sample data but if with random data for huge data shows error invalid procedure call or argument
    Please Login or Register  to view this content.
    is not still clean cells from text?
    thic case seems to be like beyond's code when using On Error Resume Next will avoid the error and works.
    Last edited by Alaa-A; 02-03-2023 at 08:56 AM.

  44. #44
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Hello Alaa-A

    A question that we did not ask you before: in columns B of the sheets you have references.
    I ask: could it be that the 'ENTERING' sheet contains all those references?

    In other words: Could it be that the 'BTY' and 'STY' sheets contain references that are not in 'ENTERING'?

  45. #45
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    .
    And another thing: Could you upload another sample of your data to the Forum but -this time- with about 500 rows of data in each sheet?... Thank you.

  46. #46
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    @beyond Excel
    to I answer your question : the reference are existed in sheets(ENTERING,BTY) must be in sheet STY
    the reference are existed in ENTERING sheet is not always for the same reference could be in ENTERING sheet .
    about file I will try it ,despite of I m not at work .
    Last edited by Alaa-A; 02-03-2023 at 12:33 PM.

  47. #47
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    Quote Originally Posted by Alaa-A View Post
    but if with random data for huge data shows error [B]invalid procedure call or argument
    'Cause your file does not match your initial post attachment, the reason why a 'true' attachment is far better than your poor initial one …
    The reason why I wrote « according to your attachment » so as a reminder : the better attachment, the better code !
    Suspecting a reference in worksheet #2 or 3 does not exist in worksheet #1 …

    Just save a 'true' workbook as binary format .xlsb with many rows as possible with its size just under 1MB in order to be attached here …

  48. #48
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    @beyond Excel
    see if the file help you .
    and if it's possible avoid error when there is ref is existed in sheet but is not in other.
    Attached Files Attached Files

  49. #49
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    In the variant that I attach, the processing time has varied between 0.4 and 0.8 seconds.
    Attached Files Attached Files

  50. #50
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Merging columns, excluding columns, and calculating columns by division over another


    According to post #48 attachment my Demo2 does not arise any error on my side.
    I just edit post #41 to correct the numeric cells formatting …
    And you can remove the Sort codeline.
    Last edited by Marc L; 02-04-2023 at 02:19 AM.

  51. #51
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    Suspecting a reference in worksheet #2 or 3 does not exist in worksheet #1 …
    that's correct when I work at home's laptop
    but strangely when I work at work's laptop doesn't show this error
    According to post #48 attachment my Demo2 does not arise any error on my side.
    yes that's correct . I tested ,there is no error . I was work for another file .
    And you can remove the Sort code line.
    no I don't want . it's ok
    in reality your code is really fast gives 0.7 for file in post#48
    notices:

    1-when delete item from BTY sheet but the item is existed in ENTERING sheet there is no error and copy data with ignore they are both not matched.
    2-when delete item from ENTERING sheet but the item is existed in BTY sheet then will show error and doesn't copy data for new item from BTY but it's not existed in ENTERING sheet(if it's possible fixing it ) .sorry this cases are not clear from the beginning.
    in reality your code is really fast gives 0.7 for file in post#48
    thanks very much for your effort , time.
    Last edited by Alaa-A; 02-04-2023 at 06:28 AM.

  52. #52
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    @beyond Excel
    awesome !!
    really fast and flexible and ignore any error if the item is not existed in sheet and existed in another
    many thanks for this great work

  53. #53
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Merging columns, excluding columns, and calculating columns by division over another

    .
    Ufff... Finally!
    .

  54. #54
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Try this ...


    Quote Originally Posted by Alaa-A View Post
    but strangely when I work at work's laptop doesn't show this error
    Not strange 'cause it's not exactly the same file …
    Now you must have understood why the initial post attachment must be smart enough !


    Anyway according to your post #48 attachment my Demo2 revamped :

    PHP Code: 
    Sub Demo2r()
            
    Dim S%, WV(), N&, R&, C%
            
    Sheets(5).UsedRange.Offset(1).Clear
        With 
    New Collection
            On Error Resume Next
        
    For 2 To 3
            W 
    Sheets(2).[A1].CurrentRegion
            
    If 2 Then ReDim V(1 To UBound(W), 1 To 11)
        For 
    2 To UBound(W)
            
    Err.Clear
            R 
    = .Item(W(N2))
            If 
    Err.Number Then R = .Count 1: .Add RW(N2): V(R1) = R: For 2 To 5V(RC) = W(NC): Next
            V
    (R6) = V(R6) + W(N6)
            
    V(R10) = V(R10) + W(N+ (3))
            If 
    2 Then V(R11) = V(R11) + W(N10)
        
    Next NS
            On Error 
    GoTo 0
            W 
    Sheets(4).[A1].CurrentRegion
        
    For 2 To UBound(W)
            
    = .Item(W(N2))
            
    V(R7) = V(R7) + W(N6)
            
    V(R11) = V(R11) + W(N8)
        
    Next
            On Error Resume Next
        
    For 1 To .Count
            V
    (R8) = V(R10) / V(R6)
            
    V(R9) = V(R11) / V(R7)
        
    Next
            On Error 
    GoTo 0
        End With
            Application
    .ScreenUpdating False
        With Sheets
    (5).Range("A1:K" R).Columns
            
    .Borders.Weight 2
            
    .Font.Name = .Cells(1).Font.Name
            
    .Item("A:G").NumberFormat "_W#,###_W;;; @ "
            
    .Item("H:K").NumberFormat "_r#,##0.00_0;;; @ "
             
    With .Rows("2:" R):  .Font.Size 12:  .Value V:  End With
            
    .AutoFit
            
    .Item("B:K").Sort .Item(2), 1Header:=True
        End With
            Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 02-04-2023 at 06:31 PM. Reason: optimizing cells formatting ...

  55. #55
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: Merging columns, excluding columns, and calculating columns by division over another

    thanks again Marc
    Now you must have understood why the initial post attachment must be smart enough !
    understood , sorry about it .

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 10-06-2017, 03:59 PM
  2. Replies: 1
    Last Post: 02-23-2015, 01:33 PM
  3. Macro to Copy from 2 Columns, Paste Value in the next 2 Columns, Then clear 1st Columns
    By MHALTTUNEN in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2014, 03:34 PM
  4. Division of two columns across multiple rows
    By WiscoEm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2013, 03:44 PM
  5. Combining multiple rows from 2 columns into sorted columns depending on 1st columns value
    By Dexamphetamine in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-11-2013, 10:00 AM
  6. Replies: 1
    Last Post: 04-09-2012, 02:58 PM
  7. Replies: 2
    Last Post: 03-15-2012, 09:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1