Изваждане на уникален списък с номера от няколко колони

2 одобрения 0 неодобрения
попитан 2017 септември 13 в Excel от vdz1215 начинаещ (14 точки)
Здравейте,

В колони А,В и С имам хиляди пощенски кодове. Някои се повтарят, други не. Искам в колона D да изкарам всички уникални кодове от трите колони и съответно в следващите колони (E,F,G) по някакъв начин да е обозначено в коя колона го има този пощенски номер (ако го има в повече от една също да го показва).

2 отговори

2 одобрения 0 неодобрения
отговорени 2017 септември 13 от Badan експерт (249 точки)

Здравей,

Задачата щеше да бъде лесна, ако трябваше да намериш уникалните стойности в една колона (data => advanced filter => unique records only). В твоя случай, доколкото разбирам, става въпрос за уникални стойности в няколко колони и трябва да използваш комбинация от формули.

Като начало, бих ти препоръчал да видиш къде има дубликати. Можеш да използваш conditional formatting, за да оцветиш клетките, които се повтарят: Home => Conditional Formatting =>Highlight Cells Rules => Dublicate values.

За да изкараш уникалните стойности в отделна колкона, ако кодовете са в клетки А2:C1000, копираш следната формула в клетка D2 и влачиш надолу:

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$1000, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$1000), 0)), INDEX($B$2:$B$1000, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$1000), 0))), INDEX($C$2:$C$1000, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$1000), 0))), "")

Това е формула за масив (въвежда се не с ENTER, а CTRL + Shift + ENTER)!

Относно втория ти въпрос, мога да ти предложа следната формула, която да поставиш в клетка E2:

=CELL("address",INDEX($A$2:$A$1000,MATCH(D2,$A$2:$A$1000,0)))

Формулата търси къде в колона "А" има съвпадение със стойността в клетка "D2" и връща номера на клетката (например $A$2). Аналогично може да пренапишеш формулата да търси и в колони B и C. Не се сещам за вариант само с една формула. Също така, ако ще ти свърши работа, можеш да напишеш условие, с което да провериш кои от стойностите в колона D се срещат само веднъж в колони A:C и кои са дубликати:

=IF(COUNTIF($A$2:$C$1000,D2)>1, "Here I am! I'm a duplicate!","Original")

Успех!

0 одобрения 0 неодобрения
отговорени 2017 септември 19 от vbafreak експерт (203 точки)

Следният код оцветява всички дубликати:

Sub Highlight unique values()

'Step 1:  Declare your variables
    Dim MyRange As Range
    Dim MyCell As Range
    
'Step 2:  Define the target Range.
    Set MyRange = Selection
    
'Step 3:  Start looping through the range.
    For Each MyCell In MyRange
    
'Step 4:  Ensure the cell has Text formatting.
    If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
    MyCell.Interior.ColorIndex = 36
    'Uncomment this if you want to hide all rows with dublicates
    'MyCell.EntireRow.Hidden = True
    End If
'Step 5:  Get the next cell in the range
    Next MyCell

End Sub

Следният код оставя само редовете с дубликати (скрива редовете с уникални стойности):

Sub KeepDublicates()

'Step 1:  Declare your variables
    Dim MyRange As Range
    Dim MyCell As Range
    Dim i As Long
    Dim LastRow As Long

    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    
'Step 2:  Define the target Range.
    Set MyRange = Selection
    
'Step 3:  Start looping through the range.
    For Each MyCell In MyRange
    
'Step 4:  Ensure the cell has Text formatting.
    If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
    MyCell.Interior.ColorIndex = 36
    Else
    MyCell.Interior.ColorIndex = 2
    End If

'Step 5: Get the next cell in the range
    Next MyCell
    
'Step 6: Hide the rows with unique values
    For i = 2 To LastRow
    If Cells(i, 1).Interior.ColorIndex = 2 And Cells(i, 2).Interior.ColorIndex = 2 And Cells(i, 3).Interior.ColorIndex = 2  Then
    Cells(i, 2).EntireRow.Hidden = True
    End If
    Next

End Sub

Редовете могат да се появят отново с десен бутон => unhide.

Добре дошли в българския форум за Ексел и Майкрософт офис! Тук можете да питате хора със знания и опит, както и да споделите знанията и опита си с другите.
...