Hi all,
I've got two spreadsheets from which I have to compare and then copy data. But I'm talking about 25.000+ rows, so most of the task should be done automatically. The problem is that it's text data that doesn't match 100% and that there are duplicates involved.
On sheet 1 I have, a.o., a column of file names from an external database. These consist of a prefix and the actual file name, eg. "01|filename", "01|differentfilemane", "458|anotherfilename" etc.
Sheet 2 has four columns: server-path, filename, file size and extention. Here the filenames are given without any prefix. And there are duplicates: a few % of the filenames on sheet 2 appear in multiple rows because of different values in the "extention" column.
I want to compare the names on sheet 1 with those on sheet 2, neglecting the prefixes. For those that match I need to copy (or cut) all 4 cells of that row to 4 empty cells on sheet 1 (in the same row as the corresponding file name, obviously).
I thought Vlookup could help me here, but I'm stuck on the fuzzyness of the text data and how to deal with the duplicates. I wouldn't mind having an error message on sheet 1 whenever there's a duplicate, I could clean those up manually if necessary.
Any help would be great!
I've got two spreadsheets from which I have to compare and then copy data. But I'm talking about 25.000+ rows, so most of the task should be done automatically. The problem is that it's text data that doesn't match 100% and that there are duplicates involved.
On sheet 1 I have, a.o., a column of file names from an external database. These consist of a prefix and the actual file name, eg. "01|filename", "01|differentfilemane", "458|anotherfilename" etc.
Sheet 2 has four columns: server-path, filename, file size and extention. Here the filenames are given without any prefix. And there are duplicates: a few % of the filenames on sheet 2 appear in multiple rows because of different values in the "extention" column.
I want to compare the names on sheet 1 with those on sheet 2, neglecting the prefixes. For those that match I need to copy (or cut) all 4 cells of that row to 4 empty cells on sheet 1 (in the same row as the corresponding file name, obviously).
I thought Vlookup could help me here, but I'm stuck on the fuzzyness of the text data and how to deal with the duplicates. I wouldn't mind having an error message on sheet 1 whenever there's a duplicate, I could clean those up manually if necessary.
Any help would be great!
