Thread: Importing excel sheets from different databases with different column names into one.

Hello Everyone,

My excel skills are pretty amateur and I am looking for some insights into whether this problem is doable or not:

We have to build a database in excel by putting together files which we get from various manufacturers. Now lets say that this file is primarily on a product X which has informations on various characterstic such as: shape, color, size, dimensions, depth, width, product id, discount etc. In total there are over 25 variables on which product X is described. Now each manufacturer sends us out an excel sheet on say over 5000 kind of product X and its attributes. We have over 25 such manufacturers and we need to put all the products in one mother database which we can upload to our website every day, and around 3 times a day.

Our real challenge is in:
(a.) Each manufacturer decides to use a certain set of attributes (say manufacturer A uses only 20 of the 25 attributes) and they use different column arrangements for them. So manufacutrer A may have an excel sheet with the following column arrangements: Shape, color, size, dimensions, depth while manufacturer B may have an excel sheet with column arranged in the order: Size, dimension, color, depth etc. SO basically the order of the columns are all jumbled up across the different manufacturer.

(b.) Each manufacturer can use a different label for the same attribute. So SHAPE can be shape, sha, shap., Sha, etc. While most of the variable will not have more than 5 or 6 possible version to their names, it still is a problem for us.

So my question is: IS there a solution to putting together a database which have these fundamental issues. And if yes, where would be a good place to start. Can excel tackle this on its own? And what kind of excel skills would we need here? Honestly, we are pretty lost here and any suggestions would be very helpful for us.

Many thanks for reading through this post and look forward to hear from you.

I have a question that that I think any other may need answered also to try and help. Obviously data needs some kind of consistency or a reference to say how it is laid out. So my question to you is does each manufacturer at least provide their own data the same eay each time? So let's say you want shape... will they always provide that in say.... G for example? Or do they just randomly throw it in different columns or provide it in different orders? If so, as long as you had a list of where each type of data is kept for each manufacturer... I would think it is deffinetely possible.

Not to sure I would be capable myself of getting it to work, but I think that is the starting information that is needed to clarify the issue.