Okay, so I have been trying to figure out how to do this for about 6 hours straight now - my headache is unimaginable, and I keep just about giving up before thinking, "this is ridiculous, there has to be a way... I'm just not doing it right!" So, hopefully you guys can help me out. I've read multiple threads and articles on other websites, but everything seems to be specific to each situation so it just isn't working right. I've also tried to use code from some of the threads here, but either it isn't set up quite the way I need it or I'm doing it wrong because that hasn't worked either (I have NEVER used code before, total newbie... but I can follow directions and I did watch some tutorials and etc. today, so I'm praying I'm not too hopeless). So... here goes...
Part of my job is to match MAC IDs (serial numbers) from modems that have been returned to my company from clients with the cable order they were originally assigned to. There are three possible cable companies the modems could belong to, and I export the information that I need from their databases into Excel in order to combine their data into one workbook with a separate sheet for each company. I do this so that I can search by MAC ID in one step, rather than searching through each company's database separately. A coworker of mine physically handles the returned modems and imports the scanned MAC IDs to FileMaker, where I can go through them and then search for each (USC will not scan them when they pick them up, so we have no information to match them with except the MAC ID). We were working on finding a more efficient way to accomplish this, but our Logistics manager recently left and so now I am in limbo with this process. My coworker sometimes sends out spreadsheets of modems that he is shipping to a cable company, though we don't have them matched with a specific order yet. I would like to be able to search between his spreadsheet and my spreadsheets for duplicate MAC IDs so that it is quicker to match them up - that way I can figure out which MAC IDs are listed in the cable company's information (sometimes they do not have them at all and we're just SOL) without having to go through and search by every single MAC ID that my coworker has shipped out.
I cannot for the life of me figure out how, or if it is even possible, to cross-reference worksheets and/or workbooks to search for these duplicates. I finally gave up on that endeavor and decided to try pasting just the MAC IDs from each company and my coworker's spreadsheet into a new spreadsheet and searching for duplicates through conditional formatting. On a very small scale, this seemed to work (though it was still time-consuming to go through and search the original sheet for the matching MAC IDs to actually get the order information from them and then compile all of that in yet another worksheet... but, I digress...).
However, I noticed once I tried this on a larger scale that it was not going to work properly because one of the cable companies that I pull information from exports their data to Excel with apostrophes before the MAC ID numbers (the numbers often contain both numbers and letters). With the smaller sample, when I copied and pasted the MAC IDs into the new spreadsheet I did a find for the apostrophes and replaced them with blanks and then also did a find for spaces and replaced them with blanks as well. I thought this had taken care of the issue because they appeared to be gone, but once all 5000 and some odd MAC IDs were pasted, I noticed that some of them - not all - had converted to scientific notation, and even when I changed the formatting back to text it was all funky and not the correct serial number any more. After I had tried various ways to fix it I ended up frustrated and closed that worksheet and tried to start again to see when exactly they converted and etc. In the new worksheet when I searched for the apostrophes it wouldn't let me replace them with blanks (it said it replaced them but they were still there) and when I tried to replace them with spaces it would but then some of the numbers would convert to scientific notation part of the time, and when I got lucky and they didn't it wouldn't let me get rid of the spaces. I tried using the trim function but couldn't figure out how to get it to apply to the entire column. When I entered =trim(A:A) for the entire column it just did nothing... another time it replaced everything with 0s. I tried running code I found on this forum to get rid of apostrophes (right-click sheet, view code, pasted, clicked on sheet and/or exited) and nothing happened at all. I also tried code for trimming spaces and it didn't do anything. I tried to clean and had the same issue. I am positive that it is something I am doing or not doing rather than Excel but I have no idea what it is or how to get to the outcome I want. There has to be a way, though, because the other two cable companies do not have apostrophes when I export them and I can search for dupes just fine.
Ideally, I would like to get to a point where I could export the MAC IDs in FileMaker to an excel spreadsheet and search for dupes between those and my collaborative workbook with the cable company information in it - but there are over 1200 MAC IDs in FM that would need to be looked for (each cable company also has a decent amount of MAC IDs to cross-reference with; one is relatively small with only 500 or so, the company that I am having this issue with has over 5000, and the final company has over 15000). Annnnd assuming that I can at some point get to where I can search for duplicates within them, I would like to find a way to have all of the information in one workbook and have each individual match distinguished in some way so I don't have to do a physical search for it (for example, if I find that E86D5279C371 has a duplicate under cable company A, I would like E86D5279C371 to be highlighted in both places a distinct color, or something, from all the others so that I can easily spot it).
I would really, REALLY, appreciate any help/advice, because I am at a total loss at this point and I really don't want to keep yelling at Google for not helping me find the answer :tongue:. Sorry this is such a book, just trying to be as complete as possible. I was going to attach samples but for some reason it won't work :-(. THANK YOU in advance!!!!!!!