Mass data transfer

I am aware of that, I was just stating the total lines of data is 2.5 M lines, not in one spreadsheet, but spread accross many. The original data as you correctly stated is in Access and is a huge 800 MB big

Hi

Using your 2 files, put the macro below into a general module in oxno.xls. Have both spreadsheets open and run. Check the output and see how it goes.

Please Login or Register  to view this content.

rylo

Wow rylo thanks a lot for that, you are a real legend!

Field "N" is being generated perfectly. Field "O" is copied over correctly more than not, but no data at all is being copied over for "T"

Nowshad

Hi

Using the example file that we have been using, can you give instances of
1) Field O that is incorrect, what it should be and why.
2) Examples of Column T where it should be bringing back a result.

rylo

Duh, stupid me, please find attached the outcome.

I will focus on the first set of results (line 4-17 on spreadsheet "oxno" & lne 2-79 on spreadsheet "ox") and fill the fields in light yellow where nothing was copied over to field "O" and pale blue for field "T".
Also there was one instance where the field "N" was not copied over, I have put that in rose.

Thanks again for all your help it is really appreciated!

Hi

Looking quickly at some of the results in oxno.xls!O, you have entries that have 2 non numeric characters, yet your requirement was

its of numerical value with one non numerical field in it (usually a "/").

. Can you please clarify.

Also, I applied the same logic to the data for column T. Does this not apply?

For the same block in rows 4-17, can you please explain for each and every one WHY the item was selected, where it came from (row number) and the logic that selected that item. Update the example files and put the reasons etc into the next columns (P and U).

rylo

Sorry you are correct I did state originally I only needed data to be transfered that was numerical with max of 1 non numerical character, can I correct myself and request 5 non-numerical charcters?
Again I did not clearly state the requirments for Field "T" so appologies for that. This field will have similar data as field "N" so the requirements for field "T" should be the same as field "N".

Thanks

Hi

When you say 5, do you mean that it must have 5 non numeric, or up to and including 5 non numeric (excluding spaces).

rylo

Hello

I mean up to 5 (excluding spaces), so if the data found by macro has more than 5 non numerical numbers it can ignore it and not copy it over.

Thanks

Hi

the entry in row 10 of oxno.xls (012292041, NLG) has 3 matches in ox.xls (rows 30, 72, 78). How is the correct result determined? And are there likely to be many that have multiple possible answers? If so, is there a unique combination that can be used?

rylo

If it is possible to specifiy that the first match should be copied over for field "N" from "I". If that is do-able that would be the best solution, because the data will always be same just in different lines.

Hi

For the column T output, if it is the same selection as column N, then how do you determine which entry to take from columns J and M? J if it is non blank otherwise M regardless of what is in it?

As I requested earlier, can you please complete each of the items in rows 4 - 17, explain where the data came from and WHY that result was selected.



rylo

Hi

Originally Posted by rylo

Hi

For the column T output, if it is the same selection as column N, then how do you determine which entry to take from columns J and M? J if it is non blank otherwise M regardless of what is in it?

rylo

Copying value for "T" from "J" if non blank otherwise "M" as long as "M" is no more than 2 numerical characters, would be the perfect solution.

Also I am attaching "oxno" with comments in terms of where the data has come from and what criteria is attached to that.

Thanks

Hi

OK, here goes again.

One thing I've noticed with oxno.xls, is that you have an ID in the heading row for most of the entries except row 2. You will have to correct columns A and B to make sure the headings are in those columns.

If this brings back some incorrect entries, note which ones, and explain where it should come from and why....

rylo

Please Login or Register  to view this content.

Oh my god Rylo, you are a legend! Do you guys collect donations or something, because you helping me with this deserves one

Hi

I gather that this has solved your request (Gees I sure hope so). If this is right, can you please go back to your original post and mark it as solved.

And don't forget to recommend the site and tell everyone just how helpful the site has been......


rylo

haha indeed it has, thanks again, and dont worry about me telling people about this site, you guys (especially you Rylo) are amazing!!

I shall mark this thread as solved.