• 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.

Large data file using Vlookup to investigate create VBA Array if Work Order number is true

Grant A

New Member
Our Excel function has the following request;
We have a worksheet that lists all past, current and upcoming work projects (work sheet “Report”). There is columns with dates that we can filter for the work status.
Within the report a key Work Order number is generated for all scheduled tasks

We generate another raw data table of 17 columns and anything up to 50,000 rows that display completed tasks (work sheet “Data”). All data is completed work orders
If the work order number in column 1 of the “data” sheet matches a corresponding work order number on designated column on the ”Report” sheet then we wish to have 5 cells from the "Data" sheet put the selected values within their row, updated to the corresponding row / column on the "report" sheet that align with the same work order number. This provides our information as check that the work orders have been completed.

Our coding request is to run the key work order check within an array and if true the row data from the “data” sheet would update cell values from five columns into the corresponding “report” sheet with matched work order number, this provides completed work data.

The remaining Blank rows on the “report” page would indicate open work orders.

This is being done with Vlookup formula but would request some assistance to have this generated with VBA array.
The columns for the "data" sheet and "report" sheet remain fixed. The work order number is unique for each job.

Our Vlookup formula for the key number check is as follows;
=IF(ISERROR(VLOOKUP(K2,Data!$1:$50000,1,0)),"",VLOOKUP(K2,Data!$1:$50000,1,0))
this transposes the data from "Data" sheet to "report" sheet when true
The other four columns have variation of this formula with the change in column lookup from the "data" sheet to the "report" sheet.

I watch the Chandoo videos, read the books and online help but arrays is just one function within VBA i just currently can't fully grasp and understand. i can see the concepts but only become lost in the detail.

would very much appreciate any assistance
regards
Array Tragic
 
@Grant A
Just wondering ...
=if(something;true;false) ... how do Your formula work?
=if(if something gives error;"";something which gave error) ...
Screen Shot 2016-01-14 at 22.15.31.png
=iferror(something;false)
Could You test that blue formula?
 
vletm.....
the Vlookup is quick fix we have only just started in the past few weeks. previously the job function our role would just monitor paper in/out from a tray... not efficient at all
Plus we have no real idea if all projects allocated for work have been closed....
The database base to inform of closed projects is very large so we are looking ways to filter this down from such huge volume of lines...

effectively I would like not to fix the Vlookup which as you have pointed out is combersome in what we are trying to achieve... The goal is have completed tasks clearly identified but more importantly identify those tasks which remain open. with the Date out we will then filter these as projects that should be closed and why they have not been completed.

More effectively I would like investigate a way to use an array to complete this task... this is where I would like some help

in the attached file further explain my request

On the report sheet columns in blue we current can filter to provide header items Row 2... this is the projects in work or projected plan

the green column is the work order number with the the WO removed (this due our complete data for the work order is just numerical

If the work order number on report sheet in green column match the same number on data sheet (column A) then transpose the row data sheet items (data sheet yellow columns to the same header items yellow columns on report sheet)

Am looking at best and most efficient way to check and transpose this data from data sheet to the report sheet

regards
 

Attachments

  • Book1.xlsx
    12.4 KB · Views: 7
@Grant A
'Data'-sheet has now only 1 row.
Could You add about 20 rows or more, including all kind of different cases?
'Report'-sheet;
What do it look before and after Your investigate with sample data?
>> Ideas ...
Do You need all row for ever in those sheets?
'Completes' could move to 'Completed'-sheet,
'Closed' could move to 'Closed'-sheet.
Then only 'Active' would be in 'Data'.
This could mean 'huge' to 'less', but all data would be possible to see.
>> Questions?
 
@vletm
have added 20 rows on the dummy file "Data" sheet this raw download and the data would have information to each column. Have only added test data to relevant columns. With the search the column "project status" will have field "completed". Our search is looking from main database for completed work.

After each download the data sheet would be dumped and no information kept on this sheet.

On the "report" sheet the data that has transposed to yellow columns from "data" sheet to the "report" sheet if the work order number is true when checked with green column number. If the number is not match from column A on data sheet the row on "report" sheet remains blank

We did little more investigation today and have worked a better search for the raw "data" download and think this would be reduced to 10,000 or 20,000 rows max. If we run the check several times a day including through our night shift this would further reduce the search (historical data) so we would estimate it would only be 5000 to 7000 rows.

this would be compared on the report fields in the blue columns approximately 150 rows each day / each download

To answer the Ideas
No, the rows on the "data" sheet dumped after each run
The blue columns on the "report" sheet will also be updated each run
All data on the "data" will be completed, so again any blank columns once check can be filtered and this will report open projects

As stated we now think to run the check at optimum times through the day would have smaller search data.
 

Attachments

  • Book1-1.xlsx
    13 KB · Views: 8
@Grant A - 2nd version
So far ... I have understood
You have 'data' on [Data]-sheet and You want to copy 'Data' to [Report]-sheet with specific layout.
All of Your sample data is 'Complete' ... but You need only those.
Test:
1) Copy Your normal data to [Data]-sheet
2) if ONLY 'Complete' then add any mark in cell[K1]
3) press [Do it] ( cell[A1] )
This will copy from [Data] with 10'000 sets all 'Complete' row to [Report]
with Layout You want.
>> I didn't check how long time it will take, so maybe test with 1000 rows
No need clear [Report]-sheet before press [Do it].
>>> Ideas ... Questions?
 

Attachments

  • Book1 (1).xlsb
    21.5 KB · Views: 11
@vletm

thank you very much as this gives me huge start on where to further progress

i understand your coding enough to see what your suggestions to solve my question.

Ideas.... as you previously suggested this could modify to have data and sheets for work orders not closed. thinking more on this, we can investigate and filter from our main database to have smaller search, less lines and run this more often to provide quicker update of work status. This would give us greater efficiency and timely updates on open work (which was our main objective)

I am away from the office this weekend but will run it with data and see how performs with search time. Also your new approach to take direct from data sheet may also reduce our download and have less rows to search.

will complete more tests next week and update results to the forum.

greatly appreciate your assistance and reply, thank you
 
@Grant A
If that is enough quick and
if Your 'real data' connection is fast then no need to copy to 'Data'-sheet.
Of course, it's possible to read straight from 'real data'.
That 'K1' any mark could use with minor modification like a filter too.
Also ... 'result of filter' could save to own sheets at once - needs only 'one run'.
Test that and make it work for You.
 
@vletm

ran the program and had some results with large file
10000 rows - time run approx 30 sec
20000 rows - time run approx 1 min 15 sec
30000 rows - time run approx 2 mins

also found the "report" result would not provide me sufficent data to produce our desired final requirements
for info the real data download is very slow from mainframe search. also it is not possible to directly read from the DB. We have believe we can further filter our search from main DB which is slower but would reduce number of rows in final file.

thus started fresh but with your previous assistance have drafted the following;

would place two DB dumps onto sheets, filter the very large file from 40 columns into 5. The DB download fixed to columns provided so will run code to filter and delete to only essential column data, UM Dump Raw to Data sheets

The second file would be from 7 Day Raw to Plan copy to report (this may not be the most effective coding but is something am happy with. This also filters down 1500 - 2000 rows into approx 100 - 150 which is the rows we need focus on

the attached workbook has dummy data for these sheets

On the module HF_comparison is my coding to perform the "Do it" functions

with the full files including UM Dump of over 25000 rows the "do it" macro ran in aprrox 1 sec (no issues)

On the module Primary_Xfer_Code have taken your previous code and placed triple comment symbols for the information and lines I need help with.

The filter now has "Report" column K values which I need match from "Data" sheet column A and then paste the row values A B C D E from Data sheet to the columns F G H I J on "report" sheet.

if column K value is not exact match from "Data" column A then report rows will remain blank
 

Attachments

  • HF Check Dummy - Jan 2016 - Copy.xlsb
    422.7 KB · Views: 10
@Grant A
I checked Your 'HF_comparison'-module and made 'some' modifications.
and
I noticed Your 'need' too.
That my previous Macro need to change.
Test how do it work now.

There are some 'challenges' with Report!F2:J2-texts and F:J-background colors.
I will check those after Your tests, Okay
AND ... AND
There are double buttons on 'Result'-sheet',
There have to change Macros still ... feeling so (clearing filters)
 

Attachments

  • HF Check Dummy - Jan 2016 - Copy (1).xlsb
    411.2 KB · Views: 4
Last edited:
@vletm

am unable see the 'ThisWorkBook'
have i missed something??

The macro button to clear dump (2nd button on 'report' sheet) can delete, not important with current question

the background colours can go in F:J this just coloured to show the different column groups. dumped a lot items out of modules to have just the current items that would assist and show more details where i'm going with the program

i uploaded as binary workbook as the macro enabled book was also too large
 
@Grant A
I just updated new file.
I will try to continue later 'today ... 6hr later' after Your feedback.
Sometimes Excel's file size gets too big, it saves 'oldies in it'.
And
One more thing, automatic backups (different versions) would be useful for You too.
 
@vletm

thanks very much for the file
i don't have my main files at home to run this new update but quick look you have cleared up my cumbersome coding

I see the info that was marked in red back ground in columns M and N on report page was just notes to have valid WO numbers from the column A on 'Data' sheet. with so many rows I needed to make sure I have valid WO's in the list to transfer. this I have removed as was just confusing - my apologies

So what I need if check that if WO number on 'report' sheet column K matches exactly the number in 'data' column A then the 5 cells in that row would then copy to 'report' sheet cells in column F G H I J. The 'data' sheet columns had been set to same columns set on 'report' F G H I J - if this makes sense

revised workbook has valid 'data' sheet row 23898 with Work order match and this copied the row to 'report' row 34 as the example -

will run again tomorrow and update follow to forum
 

Attachments

  • HF Check Dummy - Jan 2016 - Copy (2).xlsb
    288.7 KB · Views: 7
@Grant A
Okay, I can only wait You checking.
I only noticed that there are many times same WO-numbers in data, maybe OK and WO-numbers are like string - that's Okay too.
 
@vletm

your right for duplicate WO numbers in 'Data' column A, this is from main database download and is known and expected. These duplicate numbers will not affect the filtered data when compare to WO on the 'report' sheet
yes, this is OK, that the WO numbers are like string is also OK too


WO number on 'report' sheet assigned to single task each time. i have another check to ensure that these WO are unique within 'report' column K with no duplicates

will check later today and reply progress & function
 
@vletm

ran program with live data

UM Raw - little under 6300 rows
7 day raw - little under 1200 rows

ran through line by line in HF_Comparison and checked sheets as went, all function with no errors or breaks

reset all, added call of Primary_Xfer and ran again from 'Play" less than 2 seconds all done. seems to have worked but now running manual check of the results to confirm our report is accurate. filtered all out to search final report of just 36 rows. very happy as believe with several runs and internal improvements this list will further reduce.

it did stop on the Data_Report_Rev_1 subroutine with
'run time error 13
type mismatch

stopped at line - If chk_K = datas (y_y, 1) Then

checked a few corrections on the report page ;
will put header row of 'Data' columns to 'report' in rows F:J
will put header row of 'report' column K

thank you again
 
@Grant A
Quick comments:
Double or more WOs: all of those should be like 'Complete' or any of same kind or You could get 'a wrong answer'!
I haven't checked 'Data_Report_Rev_1', I'll do later.
Is there any more Macros which do You use?
 
@vletm

you have helped more than I can ask or expect
so this helps me further understand I will run the next task coding

this will only give me more knowledge, plus from the code you wrote this a is different version from my very basic run, thus giving me more to learn. I want see if I can follow your lead and continue

I will try and get the next lot of functions to run, practice the code writing and post back to the thread once I have some solution.
If you have time and will to offer me advice it would be appreciated. I have some more questions and learning to read up from how your writing is set out, knowledge is only helping me.

If you could look why we had the run time error as listed few posts agao that would be appreciated. The sheet seems to have run from what we have found so far on doing manual check. But the code didn't complete so not 100% sure on this item.

The double WO is looking at the number in 'report' column K, and with the data table filtered these can only be single numbers if the work list is correct not the dups from the sample data previously provided. This gives us an error check in the plan.

again very much appreciate your kind and patient assistance.
Grant A
 
@Grant A
Case 'Data_Report_Rev_1' ... You run unmodified version ...
There were few kind of version of some Macros.
I tried to left only two (three).
[Do it] runs that longer run (It'll show time too).
[Report] runs that shorter run (It'll show time too).
Worry 'Doubles or more': [Report] shows the 1st WO's row's information.
if case like (not from Your Data!) :
'123454 | Completed'
'123454 | Cancelled'
=> You'll get '123454 | Completed
>>> Better if could take those 'double or more' away ... ?
... and keep on testing .. doing code and so on
<<< Outside is -27C, but few days later maybe 'only' -5C >>>
 

Attachments

  • HF Check Dummy - Jan 2016 - Copy (2).xlsb
    282.9 KB · Views: 15
@vletm

have solved the debug error on run. it was an incomplete WO issue in 7 day plan column data. Have worked a fix for this and do prelim check of the column prior to run - manual check but will fix that...
testing going very well now, actually super.

full program run taking less than 1 second -

working of the basic filters of final result sheets to provide 3 reports for our work check. (still manual operation for now as check best sort / filter process order)

your help has given me plenty to think and learn from, can't thank you enough. It has also provided more efficient process in my coding

Question... if I want copy 'report' sheet to another sheet coding;

Sheets("Report").UsedRange.Copy Destination:=Sheets("Duplicates").Range("A3")

the 'report' will copy even the macro button and macro's from this sheet to destination sheet

with the UsedRange.Copy
wish change the command to be row A3:K3 and down to last row.Copy etc
 
@Grant A
One second is much shorter than Your first comments of time (~over night).
UsedRange really means UsedRange from beginning to end.
1st I would check D_LastRow of 'Duplicates'
2nd I would check 1st R_LastRow of 'Report'
if R_LastRow > 2 then next copy from 3 to R_LastRow with
Sheets("Report").range("A3:K" & R_LastRow).Copy _
Destination:= Sheets("Duplicates").Range("A" & D_LastRow+1)

as You wrote.
Everyday is a new day to learn something ...
 
@vletm

once again thank you for the coding solution you provided.

have refined the program to put three data sheets from our raw data base. These sheets approximately 1200 rows, 1000 rows and 10,000 rows.

coding now sorts,filters and arranges these into UM Raw dump which we now use to update report sheet.

This is then programmed into 4 report sheets (three reports fully active) one sheet once errors filtered has macro run to email direct to customer to update and correct the database errors (duplicate WO numbers)
the two other reports provide the team quick review of open orders. these are filtering down to 1 report only 3 or 4 lines and the second report about 1 landscape page format. we believe further updates to ensure the work orders are closed will further reduce this page list.

These macros now running a total of less than 3 seconds, each macro assigned to its own button with our testing and validation phase. Final program will combine all these functions to single button press. Testing of best times and data validation proving promising

Not only has your assistance provided quick platform for coding solution it has given myself more knowledge and depth to solve other issues within the program. with further online help and digging through other forum threads have solved various other issues that has arisen.

am working on refinement of the filter between variable dates that will again reduce final report issues to zone in only only required dates.

greatly appreciate your work and hope someday I can possibly offer some assistance to others.

regards
Grant A
 
@Grant A
It's nice to hear that You have got something, 'Kiitos'.
If You got something, someday You will give help for someone.
If You give something then You can get something back
I also learnt with this case; thank You.
One more idea,
don't give too much point for 'how long time something takes'.
More important is that the result is right!
And You'll know how that code works (not only the result).
It's much much easier to make code which have one mistake
than 'the prefect quick code'!
 
@vletm,

just some final wrap of this thread
we have run the function and data for the past several weeks and this has shown to be accurate and timely. Where my response that the time is extremely fast this is not my direct focus of absolute time but a much more effective and productive function of our data.
the assistance from yourself has been very valuable, thank you

the program has developed further with another function that i am working on as an offshoot of this data which i need count in CF (this from all the reading online is not accurate or possible with my current testing has only endorsed this) if this needs further assistance i will post new thread online

thus will close this thread and huge high fives for not just a solution to my question but giving me just new outlook and knowledge to further my coding experience and ability.
cheers
Grant
 
Back
Top