Solved

XML / CSV Data Manipulation

Posted on 2014-01-18
4
185 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 109

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Lookup - True if return at least one match 9 42
Excel Calculation 4 44
issue with DB import 1 17
JQuery Search Filter 2 27
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

791 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