Good Afternoon,
Im currently working on a project at work that involves looking at two spreadsheets and adding new information fields like x, y, z to the existing dataset.
One is created by myself and the other one is an imported list from an official source.
The data is currently only in the range of around 2000 cells which is relatively small, however, it remains tedious to import the new information. Also, the main goal of the project is to keep this for future references when the imported list becomes updated later.
My main issue arises is that I familiar with excel at an intermediate level, however, I just started learning to program in VBA / excel with occasional side projects on python.
Therefore, I can relate what I want to do in plain English, but translating that to a macro or program that can execute it flawlessly is a difficult task.
My issue arises is that these two spreadsheets contain similar data, but sometimes it is not exact.
For Example list 1 contains Town of ABC and List 2 contains ABC with an additional column that contains Town
List 1: Column 1 = Town of ABC,
List 2: Column 1 = ABC, Column 2 = Town
Therefore, I would need to perform an approximate match or fuzzy lookup so that the macro recognizes this is the same piece of data.
Then the additional issue would be when the information is matched, I would need to import a new piece of data, lets call this column 3.
So basically, does Column 1 in spreadsheet 1 approx equal to Column 1 and column 2 in spreadsheet 2.
I would make sure this is refined correctly because it will be uploaded to our CRM system and then segmented based on the additional criteria (column 3)
I have no starting point in how to approach this situation, so I am looking for any help/recommendations to help me start this mini project and ultimately accomplish this task.
Additional questions:
· if the name of the spreadsheet 2 changes it when I import it, I want to be able to adjust the code at a future date with it performing the same task as before, Im thinking if I change the name to the new spreadsheet, it will be seamless.
· Would it be easier to import the data into access and automate through there?? Im thinking the goal is to create a relational database.
Im currently working on a project at work that involves looking at two spreadsheets and adding new information fields like x, y, z to the existing dataset.
One is created by myself and the other one is an imported list from an official source.
The data is currently only in the range of around 2000 cells which is relatively small, however, it remains tedious to import the new information. Also, the main goal of the project is to keep this for future references when the imported list becomes updated later.
My main issue arises is that I familiar with excel at an intermediate level, however, I just started learning to program in VBA / excel with occasional side projects on python.
Therefore, I can relate what I want to do in plain English, but translating that to a macro or program that can execute it flawlessly is a difficult task.
My issue arises is that these two spreadsheets contain similar data, but sometimes it is not exact.
For Example list 1 contains Town of ABC and List 2 contains ABC with an additional column that contains Town
List 1: Column 1 = Town of ABC,
List 2: Column 1 = ABC, Column 2 = Town
Therefore, I would need to perform an approximate match or fuzzy lookup so that the macro recognizes this is the same piece of data.
Then the additional issue would be when the information is matched, I would need to import a new piece of data, lets call this column 3.
So basically, does Column 1 in spreadsheet 1 approx equal to Column 1 and column 2 in spreadsheet 2.
I would make sure this is refined correctly because it will be uploaded to our CRM system and then segmented based on the additional criteria (column 3)
I have no starting point in how to approach this situation, so I am looking for any help/recommendations to help me start this mini project and ultimately accomplish this task.
Additional questions:
· if the name of the spreadsheet 2 changes it when I import it, I want to be able to adjust the code at a future date with it performing the same task as before, Im thinking if I change the name to the new spreadsheet, it will be seamless.
· Would it be easier to import the data into access and automate through there?? Im thinking the goal is to create a relational database.
