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

1. ## 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. ## Re: Extract data of a certain criteria from multiple columns without duplication.

Soo.....Nothing?

3. ## 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.

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

#### 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