+ Reply to Thread
Results 1 to 3 of 3

Extract data of a certain criteria from multiple columns without duplication.

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    75

    Extract data of a certain criteria from multiple columns without duplication.

    Hi all. I would like to think i am an intermediate level excel user and I'm trying to hone my excel skills and complete a task with some style (and more effectively) and could use some help (I've completed one very similar to it once before with just brute force). If the way I have presented the situation seems confusing at first just bear with me please.

    The situation is this; we have extracts from our system that show accounting entries with a lot of information, though all I'm trying to extract are all batch numbers within the data set WITHOUT duplication. Without going into too much detail the raw data looks something like this (By column):

    A: USUALLY the data that I want but SOMETIMES irrelevant
    B: Irrelevant
    C: Irrelevant
    D: USUALLY irrelevant but SOMETIMES the data I want
    E: Irrelevant
    F: Irrelevant
    G: Irrelevant
    H: An amount that is essentially Irrelevant

    SO, normally if everything was nice and clean I know I could use the adv. filter function on column A to extract all the values without duplication, the problem is that when our system makes the report if the entry is a credit entry (they usually are not for the data I'm working with) the entry number that is normally in column A is in column D and the information that is in column D is now in column A; in this scenario column H will be a negative number rather than a positive). All the entry numbers are always 6 digits in length.

    Given all of this information, is there a way for me to extract all of the entry numbers to a single column without duplication?

    I gave the extra info because I thought there might be a way to to this by using IF and LEN or something to copy the entries from A and D only if they are exactly 6 characters, or if there was some way to use the fact that the odd entries in column D are always a negative.

    Hope that's clear enough and thank you all for the help.

    PS. If there were a way to do this and extract the data to a DIFFERENT sheet maybe using a CSE formula with INDEX, MATCH, SMALL then that would be even MORE fantastic.

  2. #2
    Registered User
    Join Date
    04-03-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    75

    Re: Extract data of a certain criteria from multiple columns without duplication.

    Soo.....Nothing?

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,806

    Re: Extract data of a certain criteria from multiple columns without duplication.

    These problems are so much easier when you attach a sample workbook and show what you have and what you want returned....

    This looks like an Advanced Filter problem with using TWO rows for the criteria. The filter in a second row means to use OR instead of AND.

    See http://www.contextures.com/xladvfilter01.html#andor for what I mean.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] macro needed to extract specific columns out of multiple columns with their row data
    By genetist in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-07-2014, 12:49 AM
  2. [SOLVED] Extract data based on multiple substring criteria
    By jspharriola in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-10-2013, 01:50 AM
  3. Extract data from Excel Worksheet using multiple criteria
    By Beudean Dorin in forum Excel General
    Replies: 0
    Last Post: 03-26-2013, 01:31 PM
  4. Extract Data Based on Multiple Criteria
    By trevaaaaaaa in forum Excel General
    Replies: 2
    Last Post: 03-14-2012, 11:42 AM
  5. Replies: 2
    Last Post: 03-23-2011, 06:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1