Forum Discussion
Help please. Is a simple Excel macro the way to go?
I need to explain the situation. The is a dog application. I have two files. One is 57,000 records and the other is 500 records. The larger file has been reduced to a record ID and the name of the dog. The smaller file has a blank for the record ID, the name of the dog, and a number of health related pieces of data. The goal is to capture the record ID form the larger file and paste it into the corresponding record of the small file. Once done, the small file will be shipped elsewhere to go through a migration process to upload the health data into the complete larger file which is available online.
I was working with the two files and manually copying the name o a dog in the small file, going to the larger file to find the record and then copy the record ID and then move back to the smaller file and paste the record ID into the appropriate record. I was doing this manually and got about 50 records done. Then I had the idea that maybe a macro could work. Am I on the right path?
Why not do the following:
- Create a VLOOKUP or XLOOKUP formula to retrieve the record IDs.
- Select the range with the formulas, copy it, then paste as values.
=INDEX($A$3:$A$24,MATCH(F3,$B$3:$B$24,0))An alternative could be INDEX and MATCH. VBA isn't required if i correctly understand you want to do. Of course you can adapt the formula according to the actual size of the data e.g.
=INDEX($A$3:$A$57000,MATCH(F3,$B$3:$B$57000,0))
Why not do the following:
- Create a VLOOKUP or XLOOKUP formula to retrieve the record IDs.
- Select the range with the formulas, copy it, then paste as values.
I'll need to study both responses. I have no docs except the Excel help file (MS Office 365). I'll see if I understand the proposals and then get back to you both. Thanks for your assistance.
Eric
- Well, I was working my way through the various LOOKUP functions and discovered that my large data file, the source for the looked up data, is corrupted. Many of the dog names have invalid or incorrect IDs. So, rather than allow the errors to continue, I'll just wait until the migration to the new system is complete and figure out how to proceed at that time. Meanwhile I've found documentation of the XLOOKUP function so I'll study this and be prepared to start at a later date. Thanks for setting me on the correct path even if we didn't accomplish the task at hand.
