×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

[Excel] Complicated Data Extraction

[Excel] Complicated Data Extraction

[Excel] Complicated Data Extraction

(OP)
Salutations!

Recently I've been given the task of trying to take a list of items from one sheet, and consolidating them into a better format.
Unfortunately for me, this list of items needs to be cross-referenced with another sheet to check if the item must be included or not, a system which is not strictly one-to-one (Sometimes an item is listed twice on the reference sheet, and must be checked under both entries)

So far it's been easy getting the data from the reference sheet, and I can very easily extract one column of data, then find the corresponding row entry that's been checked off, however I'm running into trouble extracting that information across multiple columns.

In a programming sense, I want to do a lookup with multiple outputs for each column entry, then repeat that process for however many duplicate entries there are while appending to the original lookup results, but it looks like there aren't many Excel functions to deal with this?

I've given the following a try:
  • VLOOKUP
  • XLOOKUP
  • FILTER
  • FILTER w/ conditional array attached
  • AGGREGATE
If anyone else has had to do a similarly complicated referencing using Excel, I'd love to hear about it.

RE: [Excel] Complicated Data Extraction

Two options that come to mind are:

1) Use MATCH and INDEX with helper columns so you can do the lookups in stages.
2) Do it in VBA, probably using scripting dictionaries.

For more specific advice, some examples of the sort of data giving problems would be helpful.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: [Excel] Complicated Data Extraction

As with almost any and every complicated situation, the devil is in the details. And until that is revealed, the best anyone can offer has been offered.

Oh and I could offer another possible alternative method of some sort of SQL solution.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: [Excel] Complicated Data Extraction

use the website Fiver. pay someone in India to do it for 5 dollars.

No joke, I use it all the time for data extraction.

Its great as you learn many tricks from their work.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close