¿Cómo puedo crear una fórmula que cuente el número de duplicados, devuelva el valor en una celda de mi elección y luego elimine los duplicados?

Question 1

Mi propuesta se refiere sólo a la primera parte (lista ordenada de productos):

Puede colocar su lista en una tabla de Excel, por ejemplo, Table1y hacer referencia a esta lista como Table1[List].
En columnas Cy Dobtienes una lista de unicas con sus recuentos.
Fórmula en C2: =UNIQUE(Table1[List])
Fórmula en D2: =COUNTIF(Table1[List],C2#)
Como siguiente paso, puede ordenar estas dos columnas como se muestra en columnas Fy G.
Fórmula en F2:=SORT(HSTACK(C2#,D2#),2,-1)

Answer

Mi propuesta se refiere sólo a la primera parte (lista ordenada de productos):

Puede colocar su lista en una tabla de Excel, por ejemplo, Table1y hacer referencia a esta lista como Table1[List].
En columnas Cy Dobtienes una lista de unicas con sus recuentos.
Fórmula en C2: =UNIQUE(Table1[List])
Fórmula en D2: =COUNTIF(Table1[List],C2#)
Como siguiente paso, puede ordenar estas dos columnas como se muestra en columnas Fy G.
Fórmula en F2:=SORT(HSTACK(C2#,D2#),2,-1)

Question 2

Si tiene los siguientes datos y los utiliza MS365, puede utilizar los siguientes métodos:

Fibra	Calcio	Hierro	Magnesio	Zinc	Cobre	Manganeso	Yodo	Selenio	Fluoruro
frijoles blancos	leche	cereales fortificados	semillas de calabaza	carne de res	cangrejo	germen de trigo	Papa horneada	nueces de Brasil	té negro
lentejas	yogur	hojuelas de salvado de trigo	Almendras	semillas de calabaza	langosta	patatas dulces	leche	semillas de girasol	camarón
frijoles pintos	parmesano	sémola	espinaca	semillas de girasol	shiitakes	col rizada	camarón	atún	Pasas
garbanzos	suizo	Granola	frijoles negros	lentejas	botones blancos	guisantes	pavo	salmón	frambuesas
habas	requesón	trigo triturado	salmón	pavo	portabellas	piñones	frijoles blancos	langosta	Avena cocinada
palta	espinaca	crema de arroz	yogur	huevos	creminis	avellanas	huevos	camarón	cerveza
semillas de calabaza	col rizada	carne de res	leche	shiitakes	patatas dulces	nueces pecanas		lomo de cerdo	vino tinto
semillas de sésamo	hojas de nabo	Carne molida	palta		hojas de nabo	avena		carne de res	sidra dura
Almendras	brócoli	solomillo de ternera	plátanos		Papa horneada	habas		cordero	sémola
pistachos	Black Eyed Peas	solomillo de búfala			espinaca	garbanzos		pollo	Sopa de frijol negro
nueces pecanas	frijoles blancos	bisonte terrestre			semillas de girasol	frijoles blancos		pavo	Papa horneada
semillas de girasol	salmón	mesón			semillas de calabaza	lentejas		frijoles blancos	zanahorias
calabaza bellota	calabaza bellota	carne de estofado de ternera			nueces de Brasil	espinaca		frijoles pintos	espinaca
calabaza hubbard	langosta	lomo de cerdo			nueces	piña		habas	Cantalupo

• Utilizar GROUPBY()la función aplicable a MS365--> Office Insidersúnicamente.

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     GROUPBY(_SingleCol,_SingleCol,ROWS,,0,-2))

O, usando BYROW()oMMULT()

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, MMULT(N(_Uniq=TOROW(_SingleCol)),SEQUENCE(ROWS(_SingleCol))^0),
     HSTACK(_Uniq,_Counts))

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, BYROW(_Uniq,LAMBDA(x,SUM(--(x=_SingleCol)))),
     HSTACK(_Uniq,_Counts))

Todas las fórmulas anteriores que se muestran proporcionan el número de recuentos para cada registro único.
Uso de TOCOL()la función para convertir los datos de múltiples rangos en una matriz de una sola columna excluyendo los duplicados.
Método 1--> En el primer método, utilizamos GROUPBY()la función que está diseñada para y groupsegún los campos que especificamos. Sin embargo, dicha función está disponible hasta que, a menos que haya habilitado la opción, esto no funcionará y se mostrará como un error.aggregatesortOffice-Insiders#NAME!
Método 2--> También comienza con la LET()función, que facilita la lectura y reduce el uso de rangos/fórmulas redundantes.
Al igual que el primer método, primero aplica la TOCOL()función, luego usa UNIQUE()la función para devolver valores únicos y, por último, usa MMULT()la función para devolver los recuentos, que es el producto matricial de las dos matrices dadas.
Finalmente, use HSTACK()para combinar ambas matrices devueltas usando UNIQUE()y MMULT()función para devolver el resultado deseado.
Método 3--> Comienza con los mismos pasos anteriores excepto que usa BYROW()la función para obtener los recuentos coincidentes de cada elemento.

Sin embargo, si estos métodos le parecen complicados, también puede utilizar el más simple que se muestra a continuación:

En primer lugar, use TOCOL()& UNIQUE()function para devolver los múltiples rangos en una matriz excluyendo los vacíos y duplicados. Y colocarlo en una celda, digamos enL2

=UNIQUE(TOCOL(A3:J16,1))

Finalmente, ingrese la siguiente función para obtener los recuentos. Recuerde que he tomado el rango de datos que A3:A16es posible que deba cambiar según su traje. No es necesario completar lo siguiente, ya que se derramará. ¿Dónde L2está la fórmula devuelta desde arriba usando TOCOL()yUNIQUE()

=COUNTIFS(A3:J16,L2#)

Si aún no está dispuesto a seguir lo anterior, puede utilizar POWER QUERYel que está disponible en Excel 2010+adelante para, disponible en Windows Excel 2010+yExcel 365 (Windows or Mac)

Para utilizar Power Query siga los pasos:

Primero convierta los rangos de origen en una tabla y asígnele el nombre correspondiente; para este ejemplo lo he nombrado comoTable1

A continuación, abra una consulta en blanco desde DataTab --> Get & Transform Data--> Get Data--> From Other Sources-->Blank Query

Lo anterior permite que Power Queryse abra la ventana, ahora desde HomeTab --> Advanced Editor--> Y pega lo siguiente M-Codeeliminando lo que veas, y presionaDone

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Por último, para volver a importarlo a Excel--> Haga clic en Close & Loado Close & Load To--> El primero que hizo clic creará un archivo New Sheetcon el resultado requerido, mientras que el segundo mostrará una ventana que le preguntará dónde colocar el resultado.

^{Nota: La fórmula no puede eliminar duplicados, sino que extrae valores cuando se colocan en una celda siguiendo condiciones específicas. Como he mostrado arriba. Básicamente necesitas la función & UNIQUE()o para obtener el resultado deseado.TOCOL()COUNTIF()COUNTIFS()}

Simplemente Rough Listcoloque esta fórmula en cualquier celda en blanco, donde Listestá el nombre de Table:

=LET(
     _Uniq, UNIQUE(List),
     _Output, SORT(HSTACK(_Uniq, COUNTIF(List,_Uniq)),2,-1),
     VSTACK({"Nutrients","Counts"},_Output))

Es más fácil con GROUPBY()la función si corresponde:

=GROUPBY(List,List,ROWS,,0,-2)

Answer