Transpose/Merge Two Column Data In Single Column

try in C2:

Formula:

Please Login or Register  to view this content.

with CSE. It will show one column with no duplicates

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

The original request specified "without losing data...". This MAY mean that any duplicate values should be KEPT and not discarded. If you want to keep duplicate values, you can use this array formula instead (in my sheet it's in E2):

=IFERROR(INDIRECT(TEXT(SMALL(IF($A$2:$B$8<>"",(COLUMN($A$2:$B$8)+10*(ROW($A$2:$B$8)))),ROWS($E$2:E2)),"R00C0"),),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

OK. What was needed here was a way to "convert" all the non-blank values in your array into a series of ascending unique numbers. The relative ranking of those numbers would then determine the order in which SMALL would return them on the results column.

Looking at your data the desired order within the data array was (R=Row, C=Column)
R1C1
R1C2
R2C2
R3C1
R3C2, etc,

corrresponding to
Apple
Mexico
America
Banana, etc

So the COLUMN($A$2:$B$8)+10*(ROW($A$2:$B$8) bit multiplied the relative column number of each non-blank value by 1 (Column A) or 2 (column B); and multiplied the relative row number by 10 (row 1), 20 (row 2), etc and added them together.

that yielded
21
22
31
41
42, etc, with FALSEs interspersed wherever blank cells were found.

So now you have the numbers in the correct order for SMALL to work on. All that remains is to convert the numbers back to cell values before delivering the result. thats what TEXT(xxx r00c0) does. It converts 21 into row 2, column 1 into "Apple"; 22 into row 2 column 2 into "Mexico" and so on...

Clear??

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

You're welcome.

One way:
=IFERROR(OFFSET($A$2,IF(MOD((ROWS($1:1)-1),7)=0,INT((ROWS($1:1)/7)),""),,,),OFFSET($B$1,MOD((ROWS($1:1)-1),7),,))

similar...
=IFERROR(OFFSET($A$2,IF(MOD((ROWS($1:1)-1),COUNTA(B:B))=0,INT((ROWS($1:1)/(COUNTA(B:B)))),""),,,),OFFSET($B$1,MOD((ROWS($1:1)-1),COUNTA(B:B)),,))

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.