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 .
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
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?
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
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 S = 2 To 4 W(S) = Sheets(S).[A1].CurrentRegion For R = 2 To UBound(W(S)) For N = 1 To .Count C = StrComp(.Item(N), W(S)(R, 2), 1) If C >= 0 Then If C Then .Add W(S)(R, 2), , N Exit For End If Next If N > .Count Then .Add W(S)(R, 2) Next R, S For R = 1 To .Count: .Add R, .Item(R), R: .Remove R + 1: Next ReDim V(1 To .Count, 1 To 11) For S = 2 To 3 For N = 2 To UBound(W(S)) R = .Item(W(S)(N, 2)) If IsEmpty(V(R, 1)) Then V(R, 1) = R: For C = 2 To 5: V(R, C) = W(S)(N, C): Next V(R, 6) = V(R, 6) + W(S)(N, 6) V(R, 10) = V(R, 10) + W(S)(N, 8 - (S = 2)) If S = 2 Then V(R, 11) = V(R, 11) + W(S)(N, 10) Next N, S For N = 2 To UBound(W(4)) R = .Item(W(4)(N, 2)) V(R, 7) = V(R, 7) + W(4)(N, 6) V(R, 11) = V(R, 11) + W(4)(N, 8) Next For R = 1 To .Count For C = 0 To 1 If V(R, 6 + C) Then V(R, 8 + C) = V(R, 10 + C) / V(R, 6 + C) Next C, R 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 » ! ◄ ◄
Re: Merging columns, excluding columns, and calculating columns by division over another
Originally Posted by Marc L
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
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.
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.
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
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 .
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
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 = 0: R = nc(a(i, 2)) On Error GoTo 0
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:
Re: Merging columns, excluding columns, and calculating columns by division over another
Originally Posted by Alaa-A
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
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.ComboBox, iWS, k, a, Q&, i& Rem ---------------------------\ Set cb = tbl.Range.Parent.ComboBox1: cb.List = Array("") Rem ---------------------------\ For Each iWS In Array(Sheets("ENTERING"), Sheets("BTY"), Sheets("STY")) a = iWS.Cells(1).CurrentRegion.Columns(2): Q = UBound(a) For i = 2 To Q k = Application.Match(a(i, 1), cb.List) If cb.List(k - 1) < a(i, 1) Then cb.AddItem a(i, 1), k Next Next Rem ---------------------------\ For i = 1 To cb.ListCount - 1: nc.Add i, CStr(cb.List(i)): Next Rem ---------------------------\ cb.Clear: Set cb = Nothing End Sub
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
Re: Merging columns, excluding columns, and calculating columns by division over another
Originally Posted by beyond Excel
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
Originally Posted by Alaa-A
@Marc thanks again to make the code seems better and fast
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 .
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 iWS, Q&, 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:=1, Header:=xlYes .Range("AA1").CurrentRegion.Sort .Range("AA1"), 1, Header:=xlYes a = .Range("AA1").CurrentRegion: Q = UBound(a) .Range("AA1").CurrentRegion.Clear End With Rem ---------------------------\ For i = 2 To Q: nc.Add i - 1, a(i, 1): Next End Sub
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 W, V(), 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 N = 2 To UBound(W) Err.Clear R = .Item(W(N, 2)) If Err.Number Then R = .Count + 1: .Add R, W(N, 2): V(R, 1) = R: For C = 2 To 5: V(R, C) = W(N, C): Next V(R, 6) = V(R, 6) + W(N, 6) V(R, 10) = V(R, 10) + W(N, 9) V(R, 11) = V(R, 11) + W(N, 10) Next On Error GoTo 0 W = Sheets(3).[A1].CurrentRegion For N = 2 To UBound(W) R = .Item(W(N, 2)) V(R, 6) = V(R, 6) + W(N, 6) V(R, 10) = V(R, 10) + W(N, 8) Next W = Sheets(4).[A1].CurrentRegion For N = 2 To UBound(W) R = .Item(W(N, 2)) V(R, 7) = V(R, 7) + W(N, 6) V(R, 11) = V(R, 11) + W(N, 8) Next On Error Resume Next For R = 1 To .Count V(R, 8) = V(R, 10) / V(R, 6) V(R, 9) = V(R, 11) / V(R, 7) 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), 1, Header:=True End With Application.ScreenUpdating = True End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
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 .
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'?
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.
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 .
Re: Merging columns, excluding columns, and calculating columns by division over another
Originally Posted by Alaa-A
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
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.
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.
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
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%, W, V(), N&, R&, C% Sheets(5).UsedRange.Offset(1).Clear With New Collection On Error Resume Next For S = 2 To 3 W = Sheets(2).[A1].CurrentRegion If S = 2 Then ReDim V(1 To UBound(W), 1 To 11) For N = 2 To UBound(W) Err.Clear R = .Item(W(N, 2)) If Err.Number Then R = .Count + 1: .Add R, W(N, 2): V(R, 1) = R: For C = 2 To 5: V(R, C) = W(N, C): Next V(R, 6) = V(R, 6) + W(N, 6) V(R, 10) = V(R, 10) + W(N, 9 + (S = 3)) If S = 2 Then V(R, 11) = V(R, 11) + W(N, 10) Next N, S On Error GoTo 0 W = Sheets(4).[A1].CurrentRegion For N = 2 To UBound(W) R = .Item(W(N, 2)) V(R, 7) = V(R, 7) + W(N, 6) V(R, 11) = V(R, 11) + W(N, 8) Next On Error Resume Next For R = 1 To .Count V(R, 8) = V(R, 10) / V(R, 6) V(R, 9) = V(R, 11) / V(R, 7) 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), 1, Header:=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 ...
Bookmarks