coppy 2 collums into one ignoring blanks

See if this helps:

https://www.excelforum.com/showthread.php?t=1013785

hi
what i need is a bit diferent
please see below

column a column b column c
luis luis luis
luis luis luis
luis luis luis
luis luis luis
luis luis luis
luis luis
luis luis
luis
luis
luis
luis
luis
column c will have everything from column a nad b ignoring the blanks
any help would be much apreciated

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.

Please Login or Register  to view this content.

and copy down until you get blank cells.

Maybe like this? It's an array formula, which must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Some of your "blank" cells seemed to contain "something" which resulted in a couiple of gaps in thee data. Once whatever was present, was deleted, this worked perfectly.

OK, it looks like you want to extract the unique items from the separate lists.

In the sample file the data is "contaminated" with some kind of whitespace character in some of the cells. Were there formulas in the cells that returned blanks and you converted them to values? Also, some of the cells contain trailing space characters.

On "clean" data this will do what you want.

Data Range

	B	C	D	E	F	G	H	I
3	Table 1			Table 2			Table 3
4	------	------	------	------	------	------	------
5	purple			purple			purple
6	Hibberts Bill			Gallagher Velita			Hibberts Bill
7							Ross Colin
8	Ross Colin						Roper Jo
9							Scrimshire Heather
10	Roper Jo						Smith Craig
11				Henry Sharon			Gallagher Velita
12				Shore Susan			Henry Sharon
13							Shore Susan
14	Scrimshire Heather						Truslove Jane
15	Smith Craig
16
17				Truslove Jane

This array formula** entered in H5:

=IFERROR(IFERROR(INDEX(B$5:B$17,MATCH(0,IF(B$5:B$17<>"",COUNTIF(H$4:H4,B$5:B$17)),0)),INDEX(E$5:E$17,MATCH(0,IF(E$5:E$17<>"",COUNTIF(H$4:H4,E$5:E$17)),0))),"")

Copy down until you get blanks.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Originally Posted by luis6777

This is great thanks
If i would have a 3rd column ie: H how the formula would look like?

You will need to upload a sample workbook to show your requirement. Also populate the desired output manually.

Hi. I "borrowed" Tony's solution (it works fine and was easier to adapt) and have added on the criteria for a 3rd column.

I should remind you that there was "something" in some of your cells that needed to be cleaned before either his solution or mine could work.

What do you mean by "it isn't working"? That doesn't really tell me very much!! As you can see from the attachment @12, it was working when it left here!!

Did you remembe rthat it's an array formula ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

One more thing (I'm away for an hour or so now), what about duplicates - do you want every instance listed, or just list dupes once?