Extract data using the raw file - VBA help needed.

[Anand307]

Hi Chandoo Team,

Extract data using the raw file - VBA help needed.

1. There are two excel workbooks. a. Raw data (which has all the data) b. Break Report (to which only few data needs to be extracted and pasted from Raw data excel sheet)

2. So I update a unique ID in column “R” Head Unique ID and Column “S” OM unique ID in raw data worksheet. We have created two separate unique ID’s because few columns use Head unique ID and Few use OM unique ID (More details added in the additional detail sheet regarding which unique id needs to be used for which column)

3. Is there an option in VBA where in it can pull out the data from raw data workbook based on unique ID we input? If so I would prefer it. Hence every time I input the unique ID manually and hit on macro it should pull up the data from raw data tab and paste in respective columns in break report in each row.

4. For your convenience I have mentioned the column name and address from where to extract and where to paste the data. Also few excel conditions are mentioned in a separate excel sheet named as Additional detail.

Thanks in Advance,

Regards,
Anand

 

[vletm]

1. This is Okay.
2&4. Do You use always same terms? Uniq ID - Head Unique ID ... 'break type' and so on?
3. You sample 'Break report' Column R&S are empty below 'header'.
'Raw Report's' columns A&B same (not headers). Why two columns?
There could something written rules missing in 'Additional Detail',
like: D - Symbol - F & E - OM/ID - F compare to results...
This (Your idea) is possible to do, after You will give all rules, written rules.

 

[Anand307]

Hi Vletm,

Thanks for your response.

- regarding 2 and 4 they remain the same
- 3. In break report R and S are empty because we manually update it from an external source( web based) . It is similar to unique ID in column A in raw report. Just the difference is, in raw report the data is represented one below the other ( horizontal format) and it has a huge data in it which is not required, however in break report we are trying to get data in vertical format for better comparison purpose.
- reason behind two columns for unique ID in break report is to simply the macro building process. As I already mentioned that few of the columns in break report use head unique ID and few other use OM unique ID.

Let me know if anything else.

Thanks
Amanda

 

[vletm]

I will try to 'think' one more time:
Because, there is nothing in 'Break report!R2:S4', You want all possible 'IDs' from 'Raw report'. No! Cause some rule, You want only rows: 2,4 & 12.
I'll try with Raw!Row 4:
Raw!DP4 to Break!A3 , Raw!BI4 to Break!B3 ,
Raw!DP6 to Break!C3 ( why from row 6 ? ) ,
Raw!F5 & F4 to Break!D3 ( okay, okay )
Raw!'F6:F11 to Break!E3 ( as above, but show in 'random' order? )
Raw!AJ4 to Break!F3 (interesting note in 'Add..!G3')
'Add..!H3 & I3' Suddenly terms 'break type', notional, MTM, IA & Unmatched.
for some reason
sum(Raw!O4:O5) to Break!G3 ( term: pick value )
sum(Raw!O6:O11) to Break!H3 ( term: pick value )
... and so on.

This is still possible to do, but before that
I have to learn Your 'terms' ...
or try to find rules of those three workbooks with Your examples.
Did You get an idea?

 

[Anand307]

I am working on a it, I will get back to you. Appreciate your patience .

Thanks
Anand

 

[Anand307]

Hi vletm –


I have tried to put everything in a much easier way for you to understand, apologies for confusion and the “Terms” that were included earlier. As of now you know that we use two separate workbook’s 1. Break Report 2. Raw Report (Please refer the new files attached)



1. Firstly assume that the Break report is blank (only contains header) and I have received the raw report for the 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 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 Column “R” Head Unique ID and column “S” OM Unique ID in break report based on my investigation on a online web based application. Let me make a point here the raw report contains entire data that is on online web application hence we can use raw report as an data base to pull the required data to break report. So I copy the unique ID from web and paste in Columns “R” and Column “S” assume in cell “R2” and “S2”, after updating this I would like to hit on a macro button and macro pulls the data to Column A to H, Column J, and column N in break report from raw report. Similarly I will update the next unique ID’s in cell “R3” and "S3" and hit on macro button and so on one by one.

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



Hope it makes you easier now, However let me know any thing else required.


Regards,

Anand

 

[vletm]

Something like this (You had missed something ... no matter so far)
for testing ...
1) Both files have to be in same folder.
2) Open ONLY 'Break report.xlsb'
3) Write informations to Columns R [& S] in wanted row
4) Keep previous Cell Activated in Column R and press [DOIT]
5) BTW, It will overwrite everything

 

[Anand307]

I will check and get back. Much appreciated.Thank you

Thanks
Anand