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.
Bookmarks