Extracting data from multiple columns to one column without duplicates

[blue_clouds_mountain]

HI EVERYBODY
Here is the problem:
I have MULTIPLE and SEPARATEd columns each contains hundreds of data some thing like:

column A
egg
bread
apple
milk
butter

column c
bread
milk
jam

column e
apple
orange
banana

and i need to create column that represents ALL that data WITHOUT duplicates

i.e like this:

column g
egg
bread
apple
milk
butter
jam
orange
banana
is that possible using an excel formula? honestly, i know very very little about vba...
is it possible to make the last column (g) dynamic? i mean to refersh automatically when adding data to the source columns (a, c and e)?
thanks a lot!

[FDibbins]

try this in G...
=IFERROR(INDEX($AC$1:$A$31,MATCH(0,COUNTIF($G$1:G1,$A$1:$A$31),0)),IFERROR(INDEX($B$1:$B$31,MATCH(0,COUNTIF($G$1:G1,$B$1:$B$31),0)),IFERROR(INDEX($C$1:$C$31,MATCH(0,COUNTIF($G$1:G1,$C$1:$C$31),0)),"")))

if you adjust the ranges to cater for additional data, it should update automatically

[blue_clouds_mountain]

thank you very much for reply
that formula is too complex for me to understand! i cann't even dare to dream about writing formula like this!
yet i tried but excel is telling me "circular refrences" and always returns "0" in column g...
so, please, is there any suggestion?

[FDibbins]

oops I just noticed your ranges atr not A, B, C....try this instead...
=IFERROR(INDEX($AC$1:$A$31,MATCH(0,COUNTIF($G$1:G1,$A$1:$A$31),0)),IFERROR(INDEX($C$1:$C$31,MATCH(0,COUNTIF($G$1:G1,$C$1:$C$31),0)),IFERROR(INDEX($E$1:$E$31,MATCH(0,COUNTIF($G$1:G1,$E$1:$E$31),0)),""))

if that still doesnt work for you, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[djapigo]

FD,

Your formula is brilliant... (little correction: =IFERROR(INDEX($A$1:$A$31,MATCH... )

However, I only get 0's... for some reason, when I break down your formula (highlight and F9), I get the correct info, but if entering the formula, I also only see 0's...

Any ideas? Is there an option setting that we should be using? add-in? sorry, I'm grasping at straws here...

Dennis

[FDibbins]

the formula is set up for it to start in G2, so make sure that is where it starts

[djapigo]

FD,

Sorry, now I'm simply getting "blank" results...

This is how I have it set up, A1=egg, A2=bread, A3=apple, etc. C1=bread, C2=milk, C3=jam, E1=apple, E2=orange, E3=banana, G2=your formula, which I'm getting "blank" results...

If I simplify your formula and remove the 2 other IFERRORs, it's still giving blanks, but if I highlight and press F9, I get "eggs"... really strange... =IFERROR(INDEX($A$1:$A$31,MATCH(0,COUNTIF($G$1:G1,$A$1:$A$31),0)),"")

I have a feeling I doing something silly, but it's really confusing me...

[FDibbins]

ummm i think i forgot 1 tint lil matter....that is an array formula and has to be entered using CRTL shift enter, not just enter, sorry (

[djapigo]

Gosh, I thought I tried that... but that was when I had the formula in G1... oops...

It works now... great formula!

[FDibbins]

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

[djapigo]

Hey FD,

Sorry I didn't start the thread... I figured if I couldn't get it to work, I had a feeling blue_clouds was gonna a harder time figuring it out... I'll let them mark it "solved" if they can get it to work... sorry, I just got curious about your formula...

[FDibbins]

lol sorry, I guess i lost track of who the OP was, thought it was you... thanks for the assist tho

[blue_clouds_mountain]

I'm blue_clouds_mountain and I'm the one who started the thread! that great formula worked with me like magic!! and let me admit it, I'd never figure it out my self !!
I've to thank you all specially FDibbins !!
Merry Christmas and Happy New Year