Merge columns into single list and replace with count of occurrences

[pratik.jhanb]

Hi

This is sample data set:
A B C
123 112 146
234 123 567
345 134 345
456 156 322
567 456
678
789

I want to end up with:

A B C
123 x
234 x
345 x x
456 x x
678 x
789 x
112 x
134 x
156 x
146 x
567 x x
322 x


Please help. Does anyone know of a tool that will do this?

 

[Gazeta]

Hi

This is sample data set:
A B C
123 112 146
234 123 567
345 134 345
456 156 322
567 456
678
789

I want to end up with:

A B C
123 x
234 x
345 x x
456 x x
678 x
789 x
112 x
134 x
156 x
146 x
567 x x
322 x


Please help. Does anyone know of a tool that will do this?

try
Sub x()

Dim zakres As Range, kom As Range
Dim zakladka As Worksheet
Dim tablica() As Variant
Dim i As Integer, j As Integer, w As Integer

i = 1

Set zakres = Selection
For Each kom In zakres
ReDim Preserve tablica(i)
tablica(i) = kom.Value
i = i + 1
Next kom

w = 1
For j = 1 To UBound(tablica)
If WorksheetFunction.CountIf(Columns(5), tablica(j)) = 0 Then
Cells(w, 5).Value = tablica(j)
Cells(w, 6).Value = WorksheetFunction.Rept("x",
WorksheetFunction.CountIf(Selection, tablica(j)))
w = w + 1
End If
Next j
End Sub

it works on selection and inset the list in column E & F - change it to your
needs
mcg