Tengo dos columnas en una hoja de cálculo y necesito escribir una fórmula para determinar cuántas veces aparece cada combinación. Ejemplo:
A 1
A 1
A 2
A 2
A 2
B 1
B 1
B 2
Debería resultar en:
A 1 2
A 2 3
B 1 2
B 2 1
Lo hice muy fácilmente ayer importando a una tabla SQL, pero necesito hacerlo para que cualquiera pueda hacerlo, simplemente colocando esas dos columnas en una hoja de cálculo. Luego debería completar una segunda hoja con la tabla de cantidades.
Respuesta1
Editar: Solución simple (el crédito es para SaintWacko)
Concatena las dos columnas:
=$A2&$B2
Cree una tabla dinámica, utilizando la columna concatenada como una 'Etiqueta de fila' y un 'Valor'. Luego asegúrese de que el 'Valor' esté calculando los recuentos (debe ser el predeterminado).
Solución complicada
Puede concatenar las dos columnas en la columna C:
=$A2&$B2
En la columna D, necesitará una función que determine si el elemento es un duplicado de otro elemento para que el valor sea verdadero solo para uno de cada elemento (esto solo funciona si los elementos están ordenados por columnas de datos, pero la macro luego lo hace).
=$C1<>$C2
Luego calcule el recuento si coincide en la columna E:
=COUNTIF($C:$C,$C2)
Una macro encontrará todas las combinaciones por usted, las colocará en la hoja 2 y copiará los recuentos. El código siguiente hace algunas suposiciones, pero probablemente puedas modificarlo para hacer lo que necesites.
Option Explicit
Sub GetCombinations()
Dim sheet1, sheet2 As Worksheet
Set sheet1 = Worksheets(1)
Set sheet2 = Worksheets(2)
Dim sStartColumn As String
Dim iTopRow As Long
Dim sEndColumn As String
Dim iBottomRow As Long
sStartColumn = "A"
iTopRow = 1
sEndColumn = "E"
iBottomRow = sheet1.UsedRange.Rows.Count
Dim Rng As Range
Dim sRange1 As String
sRange1 = sStartColumn & CStr(iTopRow) & ":" & sEndColumn & CStr(iBottomRow)
Set Rng = sheet1.Range(sRange1)
Rng.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _
Orientation:=xlSortColumns, Header:=xlYes
Dim i, j As Integer
j = 2
For i = 2 To iBottomRow
If sheet1.Cells(i, 4) Then
sheet2.Cells(j, 1) = sheet1.Cells(i, 1)
sheet2.Cells(j, 2) = sheet1.Cells(i, 2)
sheet2.Cells(j, 3) = sheet1.Cells(i, 5)
j = j + 1
End If
Next i
End Sub
Deberias hacer eso.