Combine two columns in a third collumn, maintaining order

[connor2290]

I have two columns

A B
1
2
3
5 4
6
7
8 9

How can I combine these two columns while keeping the order? I need it to be done with a formula or macro so it can be done seamlessly, and keep working with new data sets.

[WideBoyDixon]

Could you define "combine these two columns while keeping the order"? Can you give an example of what you want as a result? Are the entries all unique or could there be duplicates?

WBD

[connor2290]

the ideal result looks like this. Consolidate the two columns into one, if a value is in the second column, it needs to go directly above or below its adjacent entry. There some duplicates, but I want them to stay. I am consolidating two lists of employees that are grouped by branch.
1
2
3
4
5
6
7

[JeteMc]

Based on the file attached to post #1, try pasting the following formula into cell K2 and dragging the fill handle down:

=IFERROR(VALUE(TEXT(INDIRECT("R"&TEXT(RIGHT(LARGE(INDEX(0+(COUNTIF($E$2:$H$98,">="&$E$2:$H$98)&TEXT(ROW($E$2:$H$98),"0000")&TEXT(COLUMN($E$2:$H$98),"000")),0),ROWS(K$1:K1)),7),"0000\C000"),0),"#")),"")

Let us know if you have any questions.

[connor2290]

Your formula works with numbers, but I am using text. I'm using this for a list of names

[connor2290]

Thank you for responding. The formula you gave works for numbers, but not a list of names....probably should have stated that

[Bosco]

Or,

In G2, copied down :

=IF(ROWS($1:1)<=COUNT(E$2:F$100),SMALL(E$2:F$1100,COUNTIF(E$2:F$100,"<="&G1)+1),"")

Regards
Bosco

[JeteMc]

It may help if you upload another file that is more closely representative of your actual needs. Remember to manually include the expected results so that contributors will have something against which to compare their formulas/code.
Let us know if you have any questions.

[connor2290]

Okay, I have updated the attachment to be more clear

[JeteMc]

Using the following array entered formula* produces the same list as that manually placed in A3:A17 on the desired result sheet, with the exception that Kate is placed before Bob, however since Robin had been placed before Steve and Max before Chadd, I don't feel that is an issue.

=IFERROR(INDIRECT(TEXT(SMALL(IF((B$3:C$14<>""),ROW(A$3:A$14)*100+COLUMN(B$1:C$1),10^10),ROWS(A$1:A1)),"R0C00"),)&"","")

*Array entered formulas are 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.
Let us know if you have any questions.

[connor2290]

We're so close. Is there a way this can work indefinitely? I need it working for a list with hundreds of entries

[JeteMc]

Quickest way would be to change the 14's to 1000's as in: =IFERROR(INDIRECT(TEXT(SMALL(IF((B$3:C$1000<>""),ROW(A$3:A$1000)*100+COLUMN(B$1:C$1),10^10),ROWS(A$1:A1)),"R0C00"),)&"","")
Remember to only have the first cell in the output range selected when you make the modification, simultaneously press the Ctrl, Shift and Enter key to activate, then copy down the column.
Let us know if you have any questions.

[connor2290]

Works beautifully, thanks so much for your help

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.