☰

Сортировка Excel, сохраняющая одинаковые значения в разных столбцах в одной и той же позиции

tag-icon • tag-icon microsoft-excel
Сортировка Excel, сохраняющая одинаковые значения в разных столбцах в одной и той же позиции

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

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

Есть идеи?

решение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

Будет произведено:

введите описание изображения здесь

решение2

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

решение3

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

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

Исходный

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

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

На рисунке 2 показаны результаты этого этапа:

Индексация

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

Итерации

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

Почти готово

СДЕЛАННЫЙ!

решение4

На самом деле вы просите полное внешнее соединение, рассматривающее два столбца как отдельные таблицы.

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

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

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

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

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

Связанный контент