Automate compare excel sheet and text file

[Ebes1099]

I'm looking for a way to compare a list of Group Numbers that I have on a tab in an Excel spreadsheet to a list of group numbers from a tab delimited text file.

In my Excel workbook, I have a list of groups that I need to process for the month, I run this program once a month to process all the groups then I send them over to another department. That department publishes a list of the groups they are expecting to receive from me. They do this in the form of a text file. I pull my data in from elsewhere so sometimes there are some discrepancies.

Both sources of data have the Group Number that I would be able to use to do the compare. So is there a way I can compare the list of groups I have in my Excel document to the list of groups in the text file and show which ones I am missing in my Excel workbook? I'm trying to automate it with a macro. I have a general idea as to how I can do this but could use a few tips to help me out. How do I do the actual comparing and creating the output list?

 

[SkipVought]

Hi,

Where is your code? Did you not try your macro recorder to record the Data Import?

Better to do it on the sheet to understand how it happens.

Then record.

Then customize.

'Show me the money!'

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

I used the macro recorder to import the data into a range on a sheet and I have a formula in a column in that sheet that checks to see if the group number exists in the matching sheet already in the workbook. This might just be where I end it. This provides an easy enough method for going through and checking to see if I have all the groups.

The only other thing I could think of doing is to only show the groups that don't have matches. Either somewhere on an existing sheet or opening up a new sheet and listing those groups. If I feel it's worth it I might try and accomplish that, but we'll see how this works for this month.

 

[SkipVought]

You will need to modify the recorded code, because each time you execute the code you recorded, it will ADD a new QueryTable, which will eventually become a problem.

I assume that the file you import each time is in the same Path with the same file name. YES?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

Yes the file is always the same path.

I did a clear contents before I import the data from the text file. Is that the editing you were thinking?

 

[SkipVought]

No, it is the macro you recorded that will need midification.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

I know that, I meant to say "in the macro" while it was recording I did a clear contents. So when I run the macro, it will clear the contents of the cells I import to before it imports the new data.

 

[SkipVought]

So when I run the macro, it will clear the contents of the cells I import to before it imports the new data.

There is no need to do that with a QueryTable that you refresh with new data. It automatically replaces ALL the data with new data.

Please post your recorded code, so we can make suggestions regarding modification.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}