Solved

XML / CSV Data Manipulation

Posted on 2014-01-18
4
172 Views
Last Modified: 2014-11-03
I have 4 separate sets of data, each in 3 formats (.csv, .xml & .xls) and 1 excel spread sheet which contains a complete dump of a supplier's product list (over 35,000 items), the other 4 data sets contain the following information for each specific item contained within the product list dump:-

- Styles
- Icons
- Image filenames
- Descriptive data

The separate data sets can be linked together using a "style ID" which is replicated across each data set, I would like to link all data together and ensure the data is matched to the correct product.

The method I'm currently using (from within MS Excel) is to dump all data in to one "master" spread sheet and use the VLOOKUP function to pull back the relevant values. Although this is useful, it's still extremely laborious to perform for over 20,000 product ID's on 35,000 items.

The process needs to be automated and some products share the same style ID (e.g. 20 T-Shirts have style ID ABC1234, information for Style ID ABC1234 is stored in separate spread sheet and has adjacent cell containing description for product).

--------------------------------------
EXAMPLE
--------------------------------------

I need formulae / functionality to do the following:-

- Lookup style ID
- Pull back descriptive data contained in adjacent cell and dump into specified cell reference
- Move on to next style ID and perform same action in loop

Also, the style ID's have no sequential order, they are a numeric/text mixture and I'm unsure how to automate this as Excel has no way of knowing which style ID to move on to next as they are custom values, as mentioned above there are over 20,000)

What's the easiest / most automated way or achieving this?

Cheers,

Craig
0
Comment
Question by:mparfitt
4 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39791562
Please reduce the data sets to about 5-10 data elements and post the data, in the form of the SSCCE.  We can probably show you the principles that will be applicable to the larger data set.
0
 

Accepted Solution

by:
mparfitt earned 0 total points
ID: 39798226
Please mark this thread as closed as I have now spotted my mistake.

Thanks for your time.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40419008
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now