• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extraction of data using raw report – VBA help needed

A

Anand307

Member
We have two workbooks - 1. Break Report 20151218 2. Raw Report (Please refer the files attached)

1. I will create two workbooks every day in same format and no changes, with names as (1. “Break report 20151218” & 2. “Raw report”) and place both workbooks in same folder. Assume that the Break report is blank (only contains header) and I have received the raw report for a given day, as raw report has huge data in it all of which is not required for us. Hence I have created the break report in a format easier to understand. All that I need VBA to perform is to pull only the required data from Raw report.

2. Now as I have received the raw report with me, I would start updating only the required unique ID’s . In break report - column “S” & “T” I manually update the details in cell “S2” & “T2” and in cell “R2” I have applied formula Concatenate=(S2,T2), Similarly in column “V” & “W” I manually update the details in cell “V2” & “W2” and in cell “U2” I have applied formula Concatenate=(V2,W2). Hence column “R2” and “U2” will be our unique ID’s to pull data from raw report. So once we update this unique ID data manually in Row 2; we need a VBA in macro button that pulls rest of the data to respective cells. Similarly next I will go to Row 3 update “S3” & “T3” and also update “V3” & “W3” (“R3” & “U3” will get updated as we have Concatenate formula applied in it) and hit on macro button to extract the data one by one.

3. Regarding the rules I have updated in detailed description in “Rules details” workbook for your reference.
 

Attachments

  • Break report.xlsx
    25.1 KB · Views: 8
  • Raw report 20151210.xlsx
    278.6 KB · Views: 10
  • Rules Details.xlsx
    19.5 KB · Views: 6
@Anand307
I did almost same few weeks ago ...
You didn't modify it?
Instructions:
1) write those S, T, V, W -column values to wanted row. (I tested to row 7)
2) activate column R and press [R1]'s button
3) select from F-column wanted 'Break Type'
4) Done
Notes:
1) I add one sheet for dropdowns
2) columns AA, AB, AC & AD will show those 'Break Type' values
3) for some reason row 2's and row 7's columns H, I & J values are different?
I've done calculations same way like with previous time.
Questions?
 

Attachments

  • Break report (1).xlsb
    38.4 KB · Views: 4
1. I see that in module the reference were using the terms that were given in previous example. Hence I have updated the changes in editor and saved. Hope this should not disrupt anything while macro is running. However please take a look I have attached the amended code for your reference.

2. Also there is no need for including the date after the name for Raw report(Apologies it was my mistake to include date in sample file), I have removed the code related for input box for choosing the raw file name, please take a look.

3. 3) for some reason row 2's and row 7's columns H, I & J values are different? – Yes I tested this to find out the reason for difference in H & I, the values are not getting netted as it is happening in cell “G7”( 4779190+7965316 = 12744506), it should similarly sum up the values. In case if the there are multiple values in different cells for same unique ID in raw report then sum up the values based on same unique id and paste in break report. For example: - refer " A2546 to A2551" it has same unique ID hence in the break report we have the sum of these values. (Raw report 10567583+7925687+4248168+3186126+3522831+21298= 29471693 hence we have break report H2 updated with sum value. Same netting of values rule apply to rest of them where there are multiple values for similar Unique ID in raw report.


Also regarding the discrepancy in “H” we need to extract comment only till the sentence has this symbol “.,” whatever appearing after this symbol is not required. If the symbol is not there in the sentence then pick up full sentence.


4. Question a- What should I do to place the same Macro button on this same workbook but different sheet i.e., N101 & N102(Rest all remains same including formation and rules). Currently we had Charmal as active sheet so if I have to place this macro button on remaining N101 & N102 what should I be amending in VBE?

b – Why is file saved in Binary workbook? Can I save it as macro enable workbook?
 

Attachments

  • Break report.xlsb
    36.5 KB · Views: 2
@Anand307
1.) 1st view comments ...
No need formulas in columns R & U and
if V-column values is always same as column-S value.
You should write ONLY values to columns S, T & W. ( so far S, T, V, W! )
Because, You sometimes run ONLY with CP Uniq ID-value!
Break Type headers (8) place could be in 1st row.

2.) Both ok, There will be hidden that InPutBox for future use.

3.) Column-H value, There were one extra letter in code, now this works ...
also Column-I is Okay
Column-J value, My values is right AS2544 (Yours AS2543) [ Row 7] and
it take cares ".," better ... 'Wrap Text'-format missed from Column-J.

4a.) Copy and Paste that button too,
after that fill S, T & V,W -column values and press it!
No matter of 'Break report.xlsb'-file name nor activesheet's name.
'Raw report'-file have to have data in 'Sheet1' ( b_tab = "Sheet1" ).

4b.) .xlsb is Macro-enabled and this keeps file size smaller.

and this is possible to run (needs modification) with more than one row in time.
You could add more 'rows' and run all of those with one press of button.

Ideas ... Questions?
 

Attachments

  • Break report (1).xlsb
    39.8 KB · Views: 5
Iam getting “Missing: Raw report.xlsx” message though both the files are placed in same folder. Unable to test.
 
@Anand307 ... ?
I tested here, it works with out problems!
If there isn't file, only then it will show that Message!
Please, check that file's name and folder one more time!
If if different file-name, You can change it
or
I could make the next step.
 

Attachments

  • Break report (1).xlsb
    40.9 KB · Views: 4
I guess I was getting the error as “Missing: Raw report.xlsx” is because I was trying to run macro with only NT Uniq ID to test, which I had not mentioned earlies to you. Sometimes I run only with NT Uniq ID also, example row 6. And sometimes run only with CP Uniq ID example row 5 this you are already aware.



1. Why do you say “no need formulas in columns R & U”? So you want me to paste special and remove formula before running macro?

Break Type headers (8) place could be in 1st row – this is ok.

2. Hiding is ok

3. Column H,I and J are ok.

4. This is tested and looks good. Can you please make the modification once the initial functions are up and ready to use. I will request for it.


Questions.


5. How do I run the macro for Row 11, I update V, W and activate “R11”and press “R1”macro button, but I get message box saying “Empty Cell!!”, So what is the steps I need to follow while running macro for row 11 And row 12?
 

Attachments

  • Break report (1) (8).xlsb
    43.7 KB · Views: 1
@Anand307
a) You get 'Missing file: ....' only if there IS NOT that file.
b) There HAVE TO be ALWAYS value in R ( = S & T) and
so far, R value HAVE TO be ACTIVE.
I can change this, ... chk 5 >>
1) You wrote that ALWAYS R = S&T and U = V&W
So, as I wrote You need to fill columns S&T or V&W.
The code will take care this, it over writes R and U.
5) (+b) I can modify this,
but ACTIVE CELL have to be (R or U) and NOT EMPTY
This need a little time and quick test,
I'll try to do it within few hours.
You need to run one row in time, not multilines?
 
@Anand307
1) Fill S&T and/or V&W as many rows as You need
2) Press then [Button]
3) Wait for [Done]-message

There is option if no datafile ... You'll know then.

Ideas ... Questions?
 

Attachments

  • Break report (1).xlsb
    42.8 KB · Views: 5
I tested with same break report that you sent me and same raw report. First I filled S&T and W, and activate R and hit on macro button and excel is starts not to respond for long time and after that Iam getting the Run time error 2147417848(80010108) Method ‘Cells’ of object’_worksheet failed. I tested sevaral times but same error, How do I proceed and why is this error appearing?
 
@Anand307
Did You do as I write? No!
1) I wrote fill S&T and/or V&W ... that means,
You have to fill both S and T
and / or
You have to fill both V and W!
If You miss from 1st (S&T) any, there will be unexpected result.
Because, this will do that R = S&T without notice! As You know, it's not same!
Of course, I can modify that this checks before run any row that both (S&T) are filled. If not, it will give a error message.
2) After fill both (S&T), instruction tell 'press [Button]', no matter where is activecell!

Do You want more checking before running or
can You follow instructions with lighter checking?
 
Tried exactly the same way you told, however same events are repeating that is excel not responding for a longer time and then getting error. The macro was working for me when we were working on updating one line at a time. Could you please amend back the macro to work for single lines, which was quick and it fulfills my needs.
 
@Anand307
Are You sure that You used values for S/T and V/W which are possible?
There were 'newer ending loops' (now, there are 100000 limiters with Message)
> There are TWO MODEs ('one row'/'all rows'),
>> if You press [CP Uniq ID], it will run ONLY active row (ActiveCell not BLANK!)
>> if You press [NT Uniq ID], it will run all rows
>>> with [NT Uniq ID], There is also new function, if cell[X1] is NOT BLANK then view will update after every row.
>>>> Questions?
 

Attachments

  • Break report (1).xlsb
    46.7 KB · Views: 1
I still have the issue with running macro on multiple lines, same issue as said above, but no worries. For time being I will use only active row mode. Thanks for your appreciated help. I will test this out and get back if anything required.
 
@Anand307
Send that file here,
I can check what is going on ...
Do You use same 'Raw Report'-file?
If not, I need both ...
... hmmm, You could make same checking Yourself too.
 
Can we have another feature included to this. i.e, Run macro under filter mode for any column. As of now if i run the macro under filter mode some times it picks however sometimes filter automatically gets unfiltered and in row 1 the headers become blank. So it would be good if we can amend to run the macro under filtered mode as well.
 
My apologies for not letting you know that it was Break report that needed amendment, anyways you got it right. Thank you. Wish you a happy new year 2016:)
 
Is it posible to add one more additional function for below. As of now in coloumn I break report - it is picking value as Cell G + Cell H. We need to amend this based on the dropdown selection we do - example below.
1. If Notional is selected from dropdown in coloumn F then Coloumn I should be {Cell G - Cell H}
2. If MTM is selected from dropdown in coloumn F then Coloumn I should be {Cell G + Cell H}
3. If IA is selected from dropdown in coloumn F then Coloumn I should be {Cell G - Cell H}
 
Hi.
Can you please incorporate the above above requested function regarding MTM in below code please, becasue it has the enableevent taken care in it.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If ActiveCell.Column = 6 Then
    V = Application.Match(ActiveCell.Value, Split("Notional MTM IA Unmatched"), 0)
             Application.EnableEvents = False
    With Rows(ActiveCell.Row)
      If IsError(V) Then
          .Cells(7).Resize(, 3).ClearContents
      Else
          .Cells(7).Resize(, 2).Value = Array(.Cells(26 + V).Value, .Cells(30 + V).Value)
                      .Cells(9).Value = .Cells(7).Value + .Cells(8).Value
      End If
    End With
             Application.EnableEvents = True
  End If
End Sub
 
Last edited by a moderator:
@Anand307
There is already that kind of event!
If You're going to add one more 'same' ... maybe conflict or
at least some features that You have now don't work anymore.
 
You must log in or register to reply here.
Back
Top