Сортировка Excel с сохранением одинаковых значений в разных столбцах в одной позиции

Question

Представьте, что у меня есть следующий набор данных

A               B
AnthonyMorgan   EvelynThomas
JoePatterson    RussellRobinson
JohnAnderson    ChristopherMiller
CarolynMorris   RyanStewart
DorisKing       SarahPrice
AlanJohnson     MarilynHall
JonathanHall    EricCooper
AndreaPowell    GregorySimmons
PaulaCooper     JackRussell
AndrewPeterson  SeanEvans
JohnWilliams    JudyBell
LillianLewis    JohnWilliams
SteveRoberts    LillianLewis
MatthewGray     SteveRoberts
ChristinaClark  MatthewGray
                PeterAllen
                SharonHill
                MaryTurner
                DorisGonzales
                VictorWhite
                JoanFoster
                ChristinaClark
                RubyBryant
                RogerColeman
                JosephLong
                AndrewPeterson

Я хотел бы отсортировать, представьте по столбцу A чтобы одинаковые значения в разных столбцах были вместе. Окончательный результат будет:

A                   B
AlanJohnson 
AndreaPowell    
AndrewPeterson      AndrewPeterson
AnthonyMorgan   
CarolynMorris   
ChristinaClark      ChristinaClark
                    ChristopherMiller
                    DorisGonzales
DorisKing   
                    EricCooper
                    EvelynThomas
                    GregorySimmons
                    JackRussell
                    JoanFoster
JoePatterson    
JohnAnderson    
JohnWilliams        JohnWilliams
JonathanHall    
                    JosephLong
                    JudyBell
LillianLewis        LillianLewis
                    MarilynHall
                    MaryTurner
MatthewGray         MatthewGray
PaulaCooper 
                    PeterAllen
                    RogerColeman
                    RubyBryant
                    RussellRobinson
                    RyanStewart
                    SarahPrice
                    SeanEvans
                    SharonHill
SteveRoberts        SteveRoberts
                    VictorWhite

Любая идея?

Answer 1

Попробуйте этот макрос:

Sub Interleaver()
    Dim nA As Long, nB As Long
    Dim rc As Long, i As Long, j As Long

    rc = Rows.Count
    nA = Cells(rc, "A").End(xlUp).Row
    nB = Cells(rc, "B").End(xlUp).Row
    Range("A1:A" & nA).Copy Range("C1")
    Range("B1:B" & nB).Copy Range("C" & nA + 1)

    For i = 1 To nA + nB
        If i <= nA Then
            Cells(i, "D") = "A"
        Else
            Cells(i, "D") = "B"
        End If
    Next i

    Range("C1:D" & nA + nB).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal

    Range("A1:A" & nA).Clear
    Range("B1:B" & nB).Clear
    j = 2

    If Range("D1").Value = "A" Then
        Cells(1, "A") = Cells(1, "C")
    Else
        Cells(1, "B") = Cells(1, "C")
    End If

    For i = 2 To nA + nB
        If Cells(i, "C") = Cells(i - 1, "C") Then
            j = j - 1
            Range("A" & j & ":B" & j) = Cells(i, "C")
            j = j + 1
        Else
            If Cells(i, "D").Value = "A" Then
                Cells(j, "A") = Cells(i, "C")
            Else
                Cells(j, "B") = Cells(i, "C")
            End If
            j = j + 1
        End If
    Next i
End Sub

Это произведет:

Answer 2

Я не думаю, что есть простой способ сделать это. Попробуй это:
- объедините ваши данные в одном диапазоне, помня исходное местоположение (например, скопируйте свой первый столбец на новый лист и заполните "1" в следующем столбце, скопируйте второй столбец ниже первого и заполните "10" во втором столбце для этого набора данные).
- вставьте сводную таблицу, метки столбцов должны быть вашим первым столбцом и запросить сумму второго столбца (теперь у вас есть уникальные значения, отсортированные по алфавиту, во втором столбце 1 или 10, если имя встречается только в одном столбце или 11, если оно присутствует в обоих ).
- предположим, что ваши данные в сводной таблице начинаются с A5, введите это в C5: =if(mod(b5,2)=1,a5,"") а эти в d5: =if(b5>9,a5,"")

Answer 3

Другой рабочий процесс будет следующим. Обратите внимание, что это выполняется вручную, так как я не знаю ни одной функции Excel, которая делает то, что вы ищете. Другой вариант - использовать Macro или VBA для автоматизации рабочего процесса, предложенного ниже.

На рисунке 1 показаны исходные данные примера:

Начните с сортировки каждого столбца отдельно. Держите, что сортировка не влияет на другой столбец.Затем добавьте еще один столбец, чтобы помочь процессу, и напишите функцию сопоставления индекса, чтобы найти совпадения для каждой записи столбца A из столбца B:

=INDEX($C$1:$C$10,MATCH(A2,$C$1:$C$10,0),1)

Изображение 2 показывает результаты этого этапа:

Теперь вырежьте и вставьте колонку B, чтобы aviod находился рядом со значениями NA. Это две ячейки внизу в примере. Затем вырежьте и вставьте столбец A и вспомогательный столбец, чтобы сопоставить записи с столбцом B. Смотрите изображение ниже:

Повторяйте этот этап до конца. Не забудьте начать с столбца B, а затем сопоставить столбец A и столбец INDEX (вспомогательный столбец). Наконец удалите столбец INDEX, и все готово.

Answer 4

То, что вы действительно просите, - это полное внешнее объединение, рассматривающее два столбца как отдельные таблицы.

Насколько я знаю, это не возможно в родном Excel, даже с помощью плагина Power Query.

Вы можете достичь результатов, ЕСЛИ более длинная таблица охватывает все записи в более короткой таблице, но ваш пример показывает, что это не так.

Что вы можете сделать, это добавить две колонки, а затем выполнить дедупликацию, это достаточно просто. Вы также можете добавить столбец подсчета, чтобы увидеть, сколько копий с тем же именем существует в исходных данных. Дайте мне знать, если вы хотите это сделать.

ОБНОВЛЕНИЕ: Как я и ожидал, вы можете сделать это с помощью Microsoft Access. Для этого сначала необходимо иметь два столбца в виде отдельных таблиц. Затем вам нужно создать ДВА соединения между таблицами, левое внешнее соединение и правое внешнее соединение. После того как вы создали эти отношения, вы можете сделать простой запрос, чтобы выбрать один столбец из каждой таблицы. Вот и все.

Вы можете оставить свои исходные данные в Excel, если хотите, и просто ссылаться на них, пока вы разделяете два столбца на отдельные таблицы.